Python 使用 SQLite3 数据库

1. 连接到 SQLite 数据库

要使用 SQLite3，首先需要连接到数据库。如果数据库文件不存在，SQLite 会自动创建。

import sqlite3

# 连接到数据库（如果不存在则创建）
conn = sqlite3.connect('example.db')
# 创建游标对象，用于执行 SQL 语句
cursor = conn.cursor()

# 关闭游标和连接
cursor.close()
conn.close()

• sqlite3.connect('example.db') 创建或连接到一个名为 example.db 的数据库文件。
• cursor 用于执行 SQL 语句。
• 操作完成后，必须关闭游标和连接以释放资源。

2. 创建表

在数据库中创建表是存储数据的第一步。以下示例创建一个名为 users 的表，包含 id、name 和 age 字段。

import sqlite3

# 连接数据库
conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# 创建 users 表
cursor.execute('''
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        age INTEGER
    )
''')

# 提交更改
conn.commit()

# 关闭游标和连接
cursor.close()
conn.close()

• IF NOT EXISTS 确保表不存在时才创建，避免重复创建错误。
• PRIMARY KEY AUTOINCREMENT 使 id 字段自动递增。
• conn.commit() 保存更改到数据库。
• 数据类型：SQLite 支持 INTEGER、TEXT、REAL 等基本类型。

3. 插入数据

向表中插入数据使用 INSERT INTO 语句。以下示例插入一条用户记录。

import sqlite3

# 连接数据库
conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# 插入一条记录
cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ('Alice', 25))

# 提交更改
conn.commit()

# 关闭游标和连接
cursor.close()
conn.close()

• 使用 ? 占位符防止 SQL 注入，提高安全性。
• 参数以元组形式传递，例如 ('Alice', 25)。
• 必须调用 conn.commit() 保存插入的数据。

4. 查询数据

查询数据使用 SELECT 语句。以下示例查询所有用户记录。

import sqlite3

# 连接数据库
conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# 查询所有用户
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()

# 打印查询结果
for row in rows:
    print(row)

# 关闭游标和连接
cursor.close()
conn.close()

• cursor.fetchall() 获取所有查询结果，返回一个列表，每项是一个元组。
• 可以使用 cursor.fetchone() 逐行获取，或 cursor.fetchmany(size) 获取指定数量的行。

5. 更新数据

更新数据使用 UPDATE 语句。以下示例将 Alice 的年龄更新为 26。

import sqlite3

# 连接数据库
conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# 更新数据
cursor.execute("UPDATE users SET age = ? WHERE name = ?", (26, 'Alice'))

# 提交更改
conn.commit()

# 关闭游标和连接
cursor.close()
conn.close()

• WHERE 子句指定更新条件，避免影响其他记录。
• 使用占位符 ? 确保参数安全传递。

6. 删除数据

删除数据使用 DELETE 语句。以下示例删除 Alice 的记录。

import sqlite3

# 连接数据库
conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# 删除数据
cursor.execute("DELETE FROM users WHERE name = ?", ('Alice',))

# 提交更改
conn.commit()

# 关闭游标和连接
cursor.close()
conn.close()

• 删除操作需要谨慎，建议使用 WHERE 子句精确指定目标记录。
• 提交更改以确保删除生效。

7. 批量操作

批量插入多条数据可以提高效率，使用 executemany() 方法。

import sqlite3

# 连接数据库
conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# 准备多条数据
users = [('Bob', 30), ('Charlie', 22), ('David', 28)]

# 批量插入
cursor.executemany("INSERT INTO users (name, age) VALUES (?, ?)", users)

# 提交更改
conn.commit()

# 关闭游标和连接
cursor.close()
conn.close()

• executemany() 接受一个参数列表，每个参数是一个元组。
• 适合处理大量数据插入，性能优于逐条插入。

8. 错误处理

数据库操作可能出错，例如表不存在或连接失败。使用 try-except 处理异常。

import sqlite3

try:
    # 连接数据库
    conn = sqlite3.connect('example.db')
    cursor = conn.cursor()

    # 执行可能出错的操作
    cursor.execute("SELECT * FROM nonexistent_table")
    rows = cursor.fetchall()
    for row in rows:
        print(row)

except sqlite3.Error as e:
    print(f"数据库错误: {e}")

finally:
    # 确保关闭游标和连接
    if cursor:
        cursor.close()
    if conn:
        conn.close()

• sqlite3.Error 捕获数据库相关错误。
• finally 块确保资源正确释放，即使发生错误。

9. 事务管理

SQLite 支持事务，用于确保数据一致性。以下示例展示事务的使用。

import sqlite3

try:
    # 连接数据库
    conn = sqlite3.connect('example.db')
    cursor = conn.cursor()

    # 开始事务
    cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ('Eve', 29))
    cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ('Frank', 35))

    # 提交事务
    conn.commit()

except sqlite3.Error as e:
    print(f"错误: {e}")
    # 回滚事务
    conn.rollback()

finally:
    cursor.close()
    conn.close()

• 事务允许多个操作作为一个整体执行，失败时可以回滚。
• conn.rollback() 撤销未提交的更改。