4、SQLite3模块实战：连接数据库、创建表、CRUD操作、参数化查询、批量操作、获取元数据

好，咱们今天来点真家伙。SQLite3 这个模块，说实在的，是我在桌面应用开发里用得最顺手的数据库工具。它轻量、免安装、一个文件就是一个库，特别适合咱们做本地数据管理。

我个人习惯，拿到一个新项目，第一件事就是先把数据库层搭好。为什么呢？因为数据是应用的灵魂，你想想看，界面再花哨，数据存不住、查不出来，那都是白搭。

4.1 连接数据库：从零开始

连接 SQLite3 数据库，其实就一行代码的事。但这里有个坑，我刚开始学的时候踩过——路径问题。

import sqlite3

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

# 别忘了关闭连接
conn.close()

嗯，这里要注意：connect() 里的路径是相对路径。如果你在项目根目录运行，它就在根目录创建。我曾经有个项目，把数据库文件写到了桌面，结果打包发布后，用户根本找不到那个文件……后来我统一用 os.path.join 拼接绝对路径，再也没出过问题。

小技巧： 建议用 conn = sqlite3.connect(':memory:') 创建内存数据库，适合做单元测试，速度快，用完即焚。

4.2 创建表：设计你的数据容器

连接上数据库后，下一步就是建表。说白了，表就是数据的“收纳盒”，你得先想好要放什么，再动手。

import sqlite3

conn = sqlite3.connect('myapp.db')
cursor = conn.cursor()

# 创建用户表
cursor.execute('''
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        age INTEGER,
        email TEXT UNIQUE,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
''')

conn.commit()
conn.close()
print("表创建成功！")

我个人习惯，每个表都加一个 id 自增主键，再加一个 created_at 时间戳。为什么？因为后期排查数据问题时，这两个字段能救命。你想想看，没有时间戳，你怎么知道这条数据是什么时候插入的？

避坑指南： 我曾经在生产环境里忘记加 IF NOT EXISTS，结果重复执行脚本时直接报错。记住，建表语句一定要加这个保护！

4.3 CRUD操作：增删改查，一个都不能少

CRUD 是数据库操作的四大金刚。咱们一个一个来，我结合项目经验给你讲透。

4.3.1 插入数据（Create）

# 插入单条数据
cursor.execute("INSERT INTO users (name, age, email) VALUES (?, ?, ?)", 
               ("张三", 25, "zhangsan@example.com"))

# 插入多条数据
users_data = [
    ("李四", 30, "lisi@example.com"),
    ("王五", 28, "wangwu@example.com"),
    ("赵六", 35, "zhaoliu@example.com")
]
cursor.executemany("INSERT INTO users (name, age, email) VALUES (?, ?, ?)", users_data)

conn.commit()

看到那个 ? 了吗？这就是参数化查询的占位符。我见过太多新手直接拼字符串，结果被 SQL 注入搞崩了数据库。记住：永远不要用 f-string 拼接 SQL，这是铁律。

4.3.2 查询数据（Read）

# 查询所有用户
cursor.execute("SELECT * FROM users")
all_users = cursor.fetchall()
for user in all_users:
    print(user)

# 条件查询
cursor.execute("SELECT * FROM users WHERE age > ?", (25,))
older_users = cursor.fetchall()

# 查询单条
cursor.execute("SELECT * FROM users WHERE id = ?", (1,))
user = cursor.fetchone()

这里有个细节：fetchall() 返回的是列表，fetchone() 返回的是元组。我刚开始用的时候，老是把 fetchone() 当列表用，结果报错。后来我习惯用 cursor.description 获取列名，再转成字典，用起来就顺手多了。

4.3.3 更新数据（Update）

cursor.execute("UPDATE users SET age = ? WHERE name = ?", (26, "张三"))
conn.commit()
print(f"更新了 {cursor.rowcount} 条记录")

cursor.rowcount 这个属性很实用，它能告诉你影响了多少行。我在做批量更新时，经常用它来校验操作是否成功。

4.3.4 删除数据（Delete）

cursor.execute("DELETE FROM users WHERE id = ?", (4,))
conn.commit()
print(f"删除了 {cursor.rowcount} 条记录")
警告： 删除操作一定要加 WHERE 条件！我曾经在测试环境里手滑执行了 DELETE FROM users 没加条件，结果整个表清空了……还好是测试库。生产环境建议先 SELECT 确认再 DELETE。

4.4 参数化查询：安全第一

参数化查询，说白了就是用占位符代替直接拼字符串。为什么重要？我给你讲个真实案例。

我曾经接手过一个遗留系统，里面全是这样的代码：

# 危险写法！千万别学！
name = "张三"
cursor.execute(f"SELECT * FROM users WHERE name = '{name}'")

如果 name 的值是 "'; DROP TABLE users; --"，那后果不堪设想。这就是经典的 SQL 注入攻击。

正确的做法是：

# 安全写法
name = "张三"
cursor.execute("SELECT * FROM users WHERE name = ?", (name,))

SQLite3 会自动处理特殊字符，把输入当作数据而不是 SQL 命令。嗯，这个习惯一定要养成，不管项目大小，安全无小事。

4.5 批量操作：效率翻倍

如果你要插入一万条数据，一条一条 insert 那得等到猴年马月。批量操作就是用来解决这个问题的。

import time

# 模拟大量数据
data = [(f"user_{i}", i % 50, f"user{i}@example.com") for i in range(10000)]

# 批量插入
start = time.time()
cursor.executemany("INSERT INTO users (name, age, email) VALUES (?, ?, ?)", data)
conn.commit()
end = time.time()

print(f"批量插入 10000 条数据耗时：{end - start:.2f} 秒")

我实测过，批量插入比逐条插入快了将近 100 倍。为什么？因为 executemany 底层做了事务优化，减少了磁盘 I/O 次数。

进阶技巧： 如果数据量特别大（比如几十万条），建议分批提交，每批 5000 条左右。这样可以避免事务日志撑爆内存。我有个项目处理 50 万条数据，就是靠分批提交搞定的。

4.6 获取元数据：了解你的数据库

元数据，就是“关于数据的数据”。比如表结构、列名、索引信息等。这些信息在动态生成 SQL 或做数据迁移时特别有用。

# 获取所有表名
cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
tables = cursor.fetchall()
print("数据库中的表：", tables)

# 获取表结构信息
cursor.execute("PRAGMA table_info(users)")
columns = cursor.fetchall()
print("\nusers 表的列信息：")
for col in columns:
    print(f"  列名: {col[1]}, 类型: {col[2]}, 是否非空: {col[3]}, 默认值: {col[4]}")

# 获取索引信息
cursor.execute("PRAGMA index_list(users)")
indexes = cursor.fetchall()
print("\nusers 表的索引：")
for idx in indexes:
    print(f"  索引名: {idx[1]}, 是否唯一: {idx[2]}")

我个人最常用的是 PRAGMA table_info。为什么？因为我在做一个通用数据导出工具时，需要动态获取所有列名和类型，然后生成 CSV 文件头。没有元数据，这个功能根本做不了。

另外，sqlite_master 这个系统表里还存了建表语句，你可以用 SELECT sql FROM sqlite_master WHERE name='users' 把建表 SQL 捞出来，做数据库版本迁移时特别方便。

核心总结：
  • 连接数据库用 sqlite3.connect()，注意路径和内存模式
  • 建表加 IF NOT EXISTS，主键和时间戳是标配
  • CRUD 操作中，参数化查询是安全底线
  • 批量操作用 executemany，效率提升明显
  • 元数据查询用 PRAGMA 和 sqlite_master，灵活又强大

好了，这一章的内容就到这里。SQLite3 虽然轻量，但该有的功能一个不少。你把这些基础打牢了，后面做桌面应用的数据管理，那真是得心应手。下一章咱们聊聊事务和锁，那才是真正考验数据库功底的地方。

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