代码收藏家技术教程 2025-02-12

Python 中如何使用 sqlite3 操作 SQLite 数据库？

在 Python 开发中，SQLite 是一种轻量级的关系型数据库，它无需单独安装数据库服务器，所有数据存储在一个单独的文件中，适用于小型应用、移动开发、数据分析等场景。Python 提供了内置的 sqlite3 模块，方便我们操作 SQLite 数据库。本文将详细介绍 sqlite3 模块的使用，包括数据库连接、表操作、数据增删改查（CRUD），以及常见的事务管理和参数化查询，帮助你快速掌握 Python 操作 SQLite 数据库的方法。

1. 为什么选择 SQLite？

✅ 零配置：Python 内置支持，无需安装额外数据库服务。

✅ 文件存储：数据库仅是一个 .db 文件，便于管理。

✅ 轻量高效：适用于小型应用、测试环境、移动端存储。

✅ SQL 支持：支持 SQL 语法，易于与其他数据库迁移。

📌 SQLite 适用场景：

桌面应用（如浏览器、记事本应用存储用户数据）。

移动应用（Android、iOS 本地数据库）。

数据分析（存储小型数据集，替代 CSV 文件）。

2. 连接 SQLite 数据库

2.1 连接或创建数据库

使用 sqlite3.connect() 连接数据库：

import sqlite3

# 连接数据库（如果不存在则创建）
conn = sqlite3.connect("example.db")

print("数据库连接成功")

📌 特点：

如果 example.db 存在，则连接它，否则自动创建该文件。

2.2 获取游标（Cursor）

数据库操作需要使用 游标对象（cursor）：

cursor = conn.cursor()

📌 作用：

cursor.execute(sql) 用于执行 SQL 语句。

cursor.fetchall() 获取查询结果。

3. 创建表（`CREATE TABLE`）

使用 CREATE TABLE 语句创建数据库表：

cursor.execute('''
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        age INTEGER,
        city TEXT
    )
''')
conn.commit()  # 提交更改
print("表创建成功")

📌 解析：

id INTEGER PRIMARY KEY AUTOINCREMENT：主键自增。

TEXT NOT NULL：name 必须有值。

IF NOT EXISTS：避免重复创建表。

4. 插入数据（`INSERT INTO`）

4.1 插入单条数据

cursor.execute("INSERT INTO users (name, age, city) VALUES (?, ?, ?)",
               ("Alice", 25, "New York"))
conn.commit()
print("插入数据成功")

📌 注意：

? 占位符 避免 SQL 注入。

conn.commit() 提交事务，否则数据不会保存。

4.2 插入多条数据（`executemany()`）

users = [
    ("Bob", 30, "San Francisco"),
    ("Charlie", 28, "Los Angeles"),
    ("David", 35, "Seattle")
]

cursor.executemany("INSERT INTO users (name, age, city) VALUES (?, ?, ?)", users)
conn.commit()
print("批量插入成功")

📌 适用于：

批量写入数据，避免多次 execute() 提高效率。

5. 查询数据（`SELECT`）

5.1 查询所有数据

cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()

for row in rows:
    print(row)

📌 fetchall() 获取所有数据，返回列表：

(1, 'Alice', 25, 'New York')
(2, 'Bob', 30, 'San Francisco')
(3, 'Charlie', 28, 'Los Angeles')

5.2 查询单条数据（`fetchone()`）

cursor.execute("SELECT * FROM users WHERE name = ?", ("Alice",))
user = cursor.fetchone()
print(user)

📌 fetchone() 获取单条数据，适用于查询唯一记录。

6. 更新数据（`UPDATE`）

cursor.execute("UPDATE users SET age = ? WHERE name = ?", (26, "Alice"))
conn.commit()
print("数据更新成功")

📌 确保：

SET age = ? 只更新 age 列，避免误修改其他数据。

7. 删除数据（`DELETE`）

cursor.execute("DELETE FROM users WHERE name = ?", ("David",))
conn.commit()
print("数据删除成功")

📌 务必小心：

没有 WHERE 条件会删除所有数据！

先 SELECT 确认数据是否存在。

8. 事务管理（`commit()` 和 `rollback()`）

8.1 `commit()` 提交事务

conn.commit()  # 确保数据写入数据库

📌 每次 INSERT、UPDATE、DELETE 后，都要 commit() 确保数据持久化。

8.2 `rollback()` 回滚事务

try:
    cursor.execute("UPDATE users SET age = ? WHERE name = ?", (100, "Charlie"))
    raise Exception("模拟异常")  # 模拟错误
    conn.commit()
except Exception as e:
    conn.rollback()  # 发生错误时回滚
    print("事务回滚:", e)

📌 作用：

如果操作失败，回滚到上一次 commit()，防止错误影响数据库。

9. 关闭数据库连接

cursor.close()
conn.close()
print("数据库连接已关闭")

📌 最佳实践：

完成所有数据库操作后，应关闭连接，释放资源。

10. 结合 `with` 语句自动管理连接

Python 提供 with 语法，自动关闭数据库连接：

with sqlite3.connect("example.db") as conn:
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM users")
    print(cursor.fetchall())

📌 优势：

自动 commit()

异常发生时自动 rollback()

退出时自动关闭 conn

11. 使用 `Row` 以字典方式访问数据

默认 fetchall() 返回元组：

cursor.execute("SELECT * FROM users")
print(cursor.fetchone())  # (1, 'Alice', 25, 'New York')

可以改用 sqlite3.Row 使数据可用字典访问：

conn.row_factory = sqlite3.Row  # 设置行工厂
cursor = conn.cursor()

cursor.execute("SELECT * FROM users")
row = cursor.fetchone()
print(row["name"], row["age"])  # Alice 25

📌 适用于：

提高可读性，避免 row[0] 这样的索引访问方式。

12. 结论

操作	代码示例
连接数据库	`conn = sqlite3.connect("example.db")`
创建表	`CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)`
插入数据	`INSERT INTO users (name, age) VALUES (?, ?)`
查询数据	`SELECT * FROM users`
更新数据	`UPDATE users SET age = ? WHERE name = ?`
删除数据	`DELETE FROM users WHERE name = ?`
事务管理	`commit()` / `rollback()`
关闭连接	`conn.close()`