sqlite3 是 Python 的标准库模块，它提供了一个接口，用于访问和操作 SQLite 数据库，SQLite 是一个轻量级的、基于文件的、自包含的、无服务器的数据库引擎，非常适合中小型应用、移动应用、桌面应用以及作为大型应用的本地缓存。

下面我将从核心概念、基本操作、高级特性到最佳实践，为你详细讲解 sqlite3 框架。

核心概念

在使用 sqlite3 之前，需要理解几个核心对象：

1. sqlite3.connect(database): 连接对象。

   • 这是与数据库交互的入口点。
   • 参数 database 是数据库文件的路径，如果文件不存在，SQLite 会自动创建它，如果传入 ":memory:"，则会创建一个临时的内存数据库。
   • 返回一个 Connection 对象。

2. Connection.cursor(): 游标对象。

   
   （图片来源网络，侵删）
   • 游标就像一个指针,它允许你遍历数据库查询的结果集。
   • 所有的 SQL 语句（SELECT, INSERT, UPDATE, DELETE 等）都是通过游标对象执行的。
   • 返回一个 Cursor 对象。

3. Cursor.execute(sql, parameters): 执行 SQL 语句。

   • 这是执行单个 SQL 命令的核心方法。
   • sql: 要执行的 SQL 语句字符串。
   • parameters: 可选的参数元组或字典，用于参数化查询，以防止 SQL 注入。

4. Cursor.fetchall() / fetchone() / fetchmany(size): 获取查询结果。

   • fetchall(): 获取查询结果集中的所有行，返回一个列表，列表中的每个元素是一个元组。
   • fetchone(): 获取结果集的下一行，返回一个元组，如果所有行都已获取完毕，则返回 None。
   • fetchmany(size): 获取指定数量的行。

5. Connection.commit(): 提交事务。

   • 在执行 INSERT, UPDATE, DELETE 等修改数据的操作后，必须调用此方法才能将更改永久保存到数据库文件中。
   • SQLite 默认在 DML 语句执行后立即进入一个事务，直到 commit() 或 rollback() 被调用。

6. Connection.rollback(): 回滚事务。

   
   （图片来源网络，侵删）
   • 回滚自上次 commit() 以来对数据库的所有更改。
   • 通常在发生错误时使用。

7. Connection.close(): 关闭连接。

   • 完成所有操作后,必须关闭数据库连接以释放资源。
   • 一个好的实践是使用 with 语句来自动管理连接。

基本操作流程

下面是一个完整的、包含创建表、插入、查询、更新和删除操作的示例。

import sqlite3
import os
# 数据库文件名
DB_FILE = "my_database.db"
# 如果数据库文件已存在，先删除它以便于演示
if os.path.exists(DB_FILE):
    os.remove(DB_FILE)
# --- 1. 连接到数据库并创建表 ---
# 使用 'with' 语句可以自动管理连接的打开和关闭
with sqlite3.connect(DB_FILE) as conn:
    # 获取游标对象
    cursor = conn.cursor()
    # 创建一个表
    # 使用 """...""" 可以编写多行字符串
    create_table_sql = """
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        email TEXT UNIQUE NOT NULL,
        age INTEGER
    )
    """
    cursor.execute(create_table_sql)
    print("表 'users' 创建成功或已存在。")
    # --- 2. 插入数据 ---
    # 使用参数化查询防止 SQL 注入
    insert_users_sql = "INSERT INTO users (name, email, age) VALUES (?, ?, ?)"
    users_to_insert = [
        ('Alice', 'alice@example.com', 30),
        ('Bob', 'bob@example.com', 25),
        ('Charlie', 'charlie@example.com', 35)
    ]
    # executemany 用于批量插入
    cursor.executemany(insert_users_sql, users_to_insert)
    # 提交事务
    conn.commit()
    print(f"成功插入 {cursor.rowcount} 条数据。")
    # --- 3. 查询数据 ---
    query_sql = "SELECT id, name, email, age FROM users WHERE age > ?"
    age_limit = 28
    cursor.execute(query_sql, (age_limit,)) # 注意参数必须是元组，即使只有一个元素也要加逗号
    print(f"\n查询年龄大于 {age_limit} 的用户:")
    # fetchall 获取所有结果
    results = cursor.fetchall()
    for row in results:
        print(f"ID: {row[0]}, 姓名: {row[1]}, 邮箱: {row[2]}, 年龄: {row[3]}")
    # --- 4. 更新数据 ---
    update_sql = "UPDATE users SET age = ? WHERE name = ?"
    new_age = 31
    user_name = 'Alice'
    cursor.execute(update_sql, (new_age, user_name))
    conn.commit()
    print(f"\n已将 {user_name} 的年龄更新为 {new_age}。")
    # --- 5. 删除数据 ---
    delete_sql = "DELETE FROM users WHERE name = ?"
    user_to_delete = 'Bob'
    cursor.execute(delete_sql, (user_to_delete,))
    conn.commit()
    print(f"\n已删除用户 {user_to_delete}。")
    # 再次查询以验证更改
    print("\n更新后的所有用户:")
    cursor.execute("SELECT id, name, email, age FROM users")
    for row in cursor.fetchall():
        print(f"ID: {row[0]}, 姓名: {row[1]}, 邮箱: {row[2]}, 年龄: {row[3]}")
print("\n数据库操作完成，连接已自动关闭。")

高级特性

1 使用 with 语句管理连接

with 语句是 Python 的上下文管理器，它能确保 Connection 对象在代码块执行完毕后被正确关闭，即使在代码块中发生了异常。强烈推荐使用此方式。

# 推荐
with sqlite3.connect("my_database.db") as conn:
    cursor = conn.cursor()
    # ... 执行操作 ...
    # with 语句结束时，conn.commit() 或 conn.rollback() 会被自动调用（取决于是否有异常）
    # conn.close() 也会被自动调用
# 不推荐（容易忘记关闭连接）
# conn = sqlite3.connect("my_database.db")
# try:
#     # ... 执行操作 ...
#     conn.commit()
# finally:
#     conn.close()

2 参数化查询与 SQL 注入防护

永远不要使用 Python 字符串拼接来构建 SQL 查询，这极易导致 SQL 注入攻击。

# 危险！不要这样做！
user_name = "Alice'; DROP TABLE users; --"
# sql = f"SELECT * FROM users WHERE name = '{user_name}'" # 这会破坏数据库
# 安全的方式：使用问号作为占位符
sql = "SELECT * FROM users WHERE name = ?"
cursor.execute(sql, (user_name,)) # 参数作为元组传递

3 使用字典游标

默认情况下,fetchall() 返回的是元组，访问数据需要通过索引（如 row[0]），可读性较差，可以创建一个字典游标，让查询结果以字典的形式返回，键为列名。

with sqlite3.connect("my_database.db") as conn:
    # 创建字典游标
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    cursor.execute("SELECT id, name, email FROM users")
    # 现在每一行都是一个 Row 对象，可以像字典一样访问
    for row in cursor.fetchall():
        print(f"ID: {row['id']}, 姓名: {row['name']}, 邮箱: {row['email']}")
        # 也可以通过索引访问: row[0], row[1]

4 事务管理

SQLite 默认是事务自动提交模式，但更常见的做法是显式地管理事务，以保证数据的一致性。

• 开始事务：当执行第一个 DML 语句（INSERT, UPDATE, DELETE）时，事务自动开始。
• 提交事务：conn.commit()，将更改写入磁盘。
• 回滚事务：conn.rollback()，撤销自事务开始以来的所有更改。

try:
    with sqlite3.connect("my_database.db") as conn:
        cursor = conn.cursor()
        # 开始执行 DML 操作，事务隐式开始
        cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ('David', 'david@example.com'))
        cursor.execute("UPDATE users SET age = 40 WHERE name = ?", ('Charlie',))
        # 如果一切顺利，提交事务
        conn.commit()
        print("事务成功提交。")
except sqlite3.Error as e:
    # 如果发生任何错误，回滚事务
    print(f"数据库发生错误: {e}")
    # 注意：在 'with' 语句中，如果发生异常，回滚是自动的，但显式处理是好的实践
    # conn.rollback() 

最佳实践总结

1. 始终使用 with 语句：确保连接被正确关闭。
2. 始终使用参数化查询：用 作为占位符，并将参数作为元组传递给 execute()，以防止 SQL 注入。
3. 为查询结果使用字典游标：提高代码可读性和可维护性。
4. 合理使用事务：将一组相关的操作放在一个事务中，要么全部成功，要么全部失败，保证数据完整性。
5. 处理异常：用 try...except 块捕获 sqlite3.Error 及其子类（如 sqlite3.IntegrityError, sqlite3.OperationalError），以便优雅地处理数据库错误。
6. 使用 if __name__ == "__main__":：将你的数据库操作代码放在主程序块中，这样当模块被导入时不会自动执行。

常见错误

• sqlite3.OperationalError: no such table: my_table：表不存在，检查表名是否正确，或者创建表的代码是否在查询代码之前执行。
• sqlite3.OperationalError: unable to open database file：没有权限访问数据库文件所在的目录，或者路径不正确。
• sqlite3.IntegrityError: UNIQUE constraint failed: users.email：尝试插入一个已存在的唯一值（重复的邮箱）。
• sqlite3.ProgrammingError: Incorrect number of bindings supplied：传递给 execute() 的参数数量与 SQL 语句中的 数量不匹配。

希望这份详细的指南能帮助你熟练掌握 Python 的 sqlite3 框架！