3. Python与数据库交互：使用Python连接SQLite，创建材料数据表，执行基本的CRUD操作

好，咱们进入实战环节了。前面两章我们把数据库的基本概念和SQLite的安装环境都捋了一遍，现在该让Python上场了。说实话，我最早做材料数据库的时候，用的是Excel加VBA，那叫一个痛苦。后来转到Python+SQLite，感觉整个世界都清爽了。

这一章的核心就三件事：连上数据库、建表、增删改查。你想想看，材料信息学里最基础的操作不就是这些吗？把实验数据存进去，随时能查出来，能更新，能删除。搞定了这些，后面做数据分析、机器学习才有数据可用。

本章核心知识点

  • Python内置的sqlite3模块怎么用
  • 创建材料数据表的完整流程
  • CRUD操作的代码实现与避坑
Python + SQLite 交互流程 ① 连接数据库 ② 创建数据表 ③ CRUD操作 Create 插入 Read 查询 Update 更新 Delete 删除 最终目标：材料数据持久化存储与高效管理

3.1 连接数据库——第一步就这么简单

Python自带的sqlite3模块，不需要额外安装。我个人习惯在项目根目录下放一个database文件夹，专门存数据库文件。

import sqlite3

# 连接数据库（如果文件不存在会自动创建）
conn = sqlite3.connect('database/materials.db')
print("数据库连接成功！")

# 创建游标对象
cursor = conn.cursor()

# 用完记得关闭
# cursor.close()
# conn.close()

嗯，这里要注意：connect()的参数是数据库文件的路径。如果只写文件名，文件会创建在当前工作目录下。我在项目中吃过这个亏——脚本跑完发现数据库文件不知道跑哪去了。所以建议用绝对路径或者明确指定相对路径。

小技巧：用:memory:作为参数可以创建内存数据库，数据只存在内存中，程序退出就没了。适合做测试用。

conn = sqlite3.connect(':memory:')  # 内存数据库，速度快，但不持久

3.2 创建材料数据表——给数据安个家

表结构设计是数据库的灵魂。我最早做材料数据库时，表结构设计得乱七八糟，后来数据量大了，改起来那个痛苦啊。所以一开始就要想清楚：你要存什么材料？有哪些属性？

咱们以常见的合金材料为例，建一个表：

cursor.execute('''
CREATE TABLE IF NOT EXISTS alloys (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    material_name TEXT NOT NULL,
    composition TEXT NOT NULL,
    density REAL,
    melting_point REAL,
    tensile_strength REAL,
    elongation REAL,
    heat_treatment TEXT,
    create_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
''')
conn.commit()
print("材料数据表创建成功！")

这里有几个关键点：

  • IF NOT EXISTS——防止重复创建报错，我建议每次都加上
  • PRIMARY KEY AUTOINCREMENT——自增主键，每条记录的唯一标识
  • NOT NULL——材料名称和成分不能为空，这是底线
  • DEFAULT CURRENT_TIMESTAMP——自动记录创建时间，省心

注意：SQLite没有专门的日期类型，但TIMESTAMP可以存成文本格式，排序和查询都没问题。我曾经用INTEGER存时间戳，后来发现可读性太差，查个数据还得转换，麻烦。

3.3 插入数据（Create）——把实验数据存进去

数据来了，怎么存？两种方式：直接写SQL语句，或者用参数化查询。我强烈推荐参数化查询，为什么呢？

先看第一种：

# 不推荐的方式——字符串拼接
material_name = "304不锈钢"
composition = "Fe-18Cr-8Ni"
cursor.execute(f"INSERT INTO alloys (material_name, composition) VALUES ('{material_name}', '{composition}')")

这种方式有什么问题？如果material_name里包含单引号，比如O'Brien合金，SQL语句就炸了。更严重的是，如果有人恶意构造输入，可能造成SQL注入攻击。

再看推荐的方式：

# 参数化查询——安全又优雅
alloy_data = [
    ("304不锈钢", "Fe-18Cr-8Ni", 7.93, 1450, 520, 40, "固溶处理"),
    ("316L不锈钢", "Fe-16Cr-10Ni-2Mo", 7.98, 1375, 485, 50, "固溶处理"),
    ("6061铝合金", "Al-1Mg-0.6Si", 2.70, 652, 310, 12, "T6时效"),
    ("Ti-6Al-4V", "Ti-6Al-4V", 4.43, 1660, 950, 14, "退火")
]

cursor.executemany('''
INSERT INTO alloys (material_name, composition, density, melting_point, tensile_strength, elongation, heat_treatment)
VALUES (?, ?, ?, ?, ?, ?, ?)
''', alloy_data)
conn.commit()
print(f"成功插入 {cursor.rowcount} 条材料数据")

看到?了吗？这就是占位符。Python会把后面的元组数据安全地填充进去。我在项目中一直用这种方式，从来没出过问题。

3.4 查询数据（Read）——把数据拿出来用

数据存进去了，怎么查？说白了就三种场景：查全部、按条件查、模糊查。

# 查询所有材料
cursor.execute("SELECT * FROM alloys")
all_materials = cursor.fetchall()
for row in all_materials:
    print(f"ID: {row[0]}, 名称: {row[1]}, 成分: {row[2]}")

# 按条件查询——密度大于7的材料
cursor.execute("SELECT material_name, density FROM alloys WHERE density > ?", (7.0,))
heavy_materials = cursor.fetchall()
print("高密度材料：", heavy_materials)

# 模糊查询——查找含"不锈钢"的材料
cursor.execute("SELECT * FROM alloys WHERE material_name LIKE ?", ("%不锈钢%",))
steels = cursor.fetchall()
print(f"找到 {len(steels)} 种不锈钢材料")

你想想看，实际工作中最常用的就是条件查询。比如我要找所有抗拉强度大于500MPa的材料，或者找所有经过固溶处理的材料，用WHERE子句加上参数化查询，几行代码就搞定了。

经验之谈：fetchall()适合数据量小的情况。如果表里有几十万条数据，用fetchmany(size)分批取，或者用fetchone()一条条处理，避免内存爆掉。我曾经在分析材料基因组数据时，一次性取了一百多万条记录，结果内存直接飙到2个G，后来改成分批处理才解决。

3.5 更新数据（Update）——数据变了怎么办

实验数据不是一成不变的。比如某个材料的性能参数需要修正，或者热处理工艺改了，这时候就要用UPDATE。

# 更新304不锈钢的抗拉强度
cursor.execute('''
UPDATE alloys 
SET tensile_strength = ?, elongation = ?
WHERE material_name = ?
''', (540, 42, "304不锈钢"))
conn.commit()

if cursor.rowcount > 0:
    print(f"成功更新 {cursor.rowcount} 条记录")
else:
    print("没有找到匹配的记录，请检查条件")

嗯，这里有个坑：UPDATE语句一定要加WHERE条件，否则会更新所有记录！我刚开始学的时候犯过这个错，把整个表的密度都改成了同一个值，还好数据量不大，重新导入了一遍。所以我的习惯是：先SELECT确认条件，再执行UPDATE。

3.6 删除数据（Delete）——清理不需要的记录

删除操作要格外小心。我建议遵循"三步走"原则：

  1. 先用SELECT确认要删除的数据
  2. 再用DELETE执行删除
  3. 最后检查影响的行数
# 第一步：确认
cursor.execute("SELECT * FROM alloys WHERE material_name = ?", ("Ti-6Al-4V",))
print("即将删除的数据：", cursor.fetchall())

# 第二步：删除
cursor.execute("DELETE FROM alloys WHERE material_name = ?", ("Ti-6Al-4V",))
conn.commit()

# 第三步：确认
print(f"删除了 {cursor.rowcount} 条记录")

重要提醒：生产环境中，我建议不要直接执行DELETE，而是用"软删除"——加一个is_deleted字段，标记为1表示已删除。这样数据还在，只是查询时过滤掉。万一删错了，还能恢复。

3.7 完整示例——一条龙走一遍

最后，我把整个流程串起来，给你一个可以直接跑的完整代码：

import sqlite3

def main():
    # 1. 连接数据库
    conn = sqlite3.connect('materials.db')
    cursor = conn.cursor()
    
    # 2. 创建表
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS alloys (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        material_name TEXT NOT NULL,
        composition TEXT NOT NULL,
        density REAL,
        melting_point REAL,
        tensile_strength REAL,
        elongation REAL,
        heat_treatment TEXT,
        create_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
    ''')
    
    # 3. 插入数据
    samples = [
        ("304不锈钢", "Fe-18Cr-8Ni", 7.93, 1450, 520, 40, "固溶处理"),
        ("316L不锈钢", "Fe-16Cr-10Ni-2Mo", 7.98, 1375, 485, 50, "固溶处理"),
    ]
    cursor.executemany('''
    INSERT INTO alloys VALUES (NULL, ?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP)
    ''', samples)
    conn.commit()
    
    # 4. 查询并展示
    cursor.execute("SELECT * FROM alloys")
    for row in cursor.fetchall():
        print(f"{row[0]}. {row[1]} | 成分: {row[2]} | 密度: {row[3]}")
    
    # 5. 关闭连接
    cursor.close()
    conn.close()

if __name__ == "__main__":
    main()

这个例子涵盖了连接、建表、插入、查询、关闭的完整流程。你把这个跑通了，CRUD的基本功就算掌握了。

3.8 常见问题与避坑指南

问题 原因 解决方案
数据库文件找不到 路径写错了 用绝对路径或os.path.join()
插入数据报错 数据类型不匹配 检查字段类型，用参数化查询
查询结果为空 条件写错了 先SELECT * 看看数据是否存在
数据没保存 忘记commit() 每次修改后记得conn.commit()
表已存在报错 重复创建 加上IF NOT EXISTS

我曾经在项目里遇到一个最诡异的问题：数据明明插入了，但程序重启后数据就没了。查了半天，发现是用了:memory:内存数据库。嗯，这个坑踩过一次就记住了。

好了，这一章的内容就到这里。Python操作SQLite其实不复杂，核心就是记住那四个操作：增删改查。多写几遍，形成肌肉记忆，后面做材料数据分析的时候就能信手拈来了。

公众号:蓝海资料掘金营，微信deep3321

← 上一章 📖 返回目录 下一章 →