Python 标准库 sqlite3 数据库交互

说明

《Python 教程》持续更新中，提供建议、纠错、催更等加作者微信: gairuo123（备注：pandas教程）和关注公众号「盖若」ID: gairuo。跟作者学习，请进入 Python学习课程。欢迎关注作者出版的书籍：《深入浅出Pandas》和《Python之光》。

sqlite3 是 Python 标准库中的一个模块，用于与 SQLite 数据库进行交互。SQLite 是一个轻量级的嵌入式关系型数据库，适用于小型应用程序、测试和原型开发。sqlite3 模块提供了一个接口，使开发者可以在 Python 中方便地执行 SQL 语句，管理数据库和处理数据。

综合示例

下面是一个综合示例，展示了如何使用 sqlite3 库创建数据库、创建表、插入数据、查询数据以及处理事务。

import sqlite3

# 创建连接
conn = sqlite3.connect('example.db')

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

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

# 插入数据
users = [
    ('Alice', 30),
    ('Bob', 25),
    ('Charlie', 35)
]
cursor.executemany('INSERT INTO users (name, age) VALUES (?, ?)', users)

# 提交事务
conn.commit()

# 查询数据
cursor.execute('SELECT * FROM users')
rows = cursor.fetchall()

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

# 更新数据
cursor.execute('UPDATE users SET age = ? WHERE name = ?', (28, 'Bob'))
conn.commit()

# 删除数据
cursor.execute('DELETE FROM users WHERE name = ?', ('Charlie',))
conn.commit()

# 处理事务
try:
    cursor.execute('INSERT INTO users (name, age) VALUES (?, ?)', ('Dave', 40))
    # 提交事务
    conn.commit()
except sqlite3.Error as e:
    print("An error occurred:", e)
    # 回滚事务
    conn.rollback()

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

在这个示例中，我们首先创建了一个连接并创建了一个游标。然后，我们创建了一张名为 users 的表，并插入了一些数据。接着，我们查询并打印了所有用户的数据。随后，我们更新了一条记录，并删除了一条记录。最后，我们演示了如何处理事务，如果在插入数据时发生错误，将回滚事务。最后关闭游标和连接。

基本概念

连接（Connection）：表示与 SQLite 数据库的连接，可以使用 sqlite3.connect() 方法创建。连接对象允许创建游标、提交事务和关闭连接等操作。
游标（Cursor）：表示数据库中的上下文，用于执行 SQL 语句和从结果集中获取数据。游标对象通过连接对象的 cursor() 方法创建。
事务（Transaction）：一组数据库操作，可以通过提交（commit）和回滚（rollback）来管理事务的原子性。

核心类和函数

sqlite3.connect(database[, timeout, detect_types, isolation_level, check_same_thread, factory, cached_statements, uri]) 创建一个与数据库的连接，如果数据库不存在则创建一个新的数据库文件。database 参数可以是数据库文件的路径，或者是 ":memory:"，表示创建一个内存数据库。
Connection 类
- cursor()：创建并返回一个游标对象。
- commit()：提交当前事务。
- rollback()：回滚当前事务。
- close()：关闭连接。
- execute(sql, [parameters])：执行一条 SQL 语句。
- executemany(sql, seq_of_parameters)：执行多条 SQL 语句。
- executescript(sql_script)：执行一组 SQL 语句。
Cursor 类
- execute(sql, [parameters])：执行一条 SQL 语句。
- executemany(sql, seq_of_parameters)：执行多条 SQL 语句。
- executescript(sql_script)：执行一组 SQL 语句。
- fetchone()：获取查询结果集中的下一行。
- fetchall()：获取查询结果集中的所有行。
- fetchmany(size)：获取查询结果集中的多行。
- close()：关闭游标。
错误处理
- DatabaseError：所有数据库相关错误的基类。
- IntegrityError：数据完整性错误，如外键约束失败。
- OperationalError：数据库操作错误，如数据库锁定。
- ProgrammingError：编程错误，如 SQL 语法错误。
- NotSupportedError：不支持的操作。

常用功能

执行 SQL 查询：

使用 execute() 方法执行 SQL 查询，并使用 fetchone()、fetchall() 或 fetchmany() 获取结果。

参数化查询：

通过使用占位符 ? 和参数元组，可以避免 SQL 注入攻击。

cursor.execute("SELECT * FROM table WHERE column = ?", (value,))

管理事务：

默认情况下，sqlite3 在执行每个命令后会自动提交事务，可以通过 isolation_level 参数禁用自动提交，手动管理事务。

上下文管理：

使用 with 语句可以确保连接和游标在使用后被正确关闭。

with sqlite3.connect('database.db') as conn:
    cursor = conn.cursor()
    cursor.execute("SQL STATEMENT")

参考

https://docs.python.org/zh-cn/3/library/sqlite3.html