python: sqlite3 模块常用函数用法示例

以下是 sqlite3 模块中常用函数和方法的详细用法示例，结合具体场景说明其功能：

1. sqlite3.connect()

功能：创建数据库连接。
参数：

• database：数据库文件名（如 example.db）或 :memory:（内存数据库）。
• timeout：连接超时时间（默认 5 秒）。
• isolation_level：事务隔离级别（默认 'DEFERRED'）。

示例：

import sqlite3

# 连接到文件数据库（不存在则创建）
conn = sqlite3.connect('mydatabase.db')

# 连接到内存数据库（会话结束后数据丢失）
mem_conn = sqlite3.connect(':memory:')

2. conn.cursor()

功能：创建游标对象，用于执行 SQL 语句。
示例：

cursor = conn.cursor()  # 创建游标
cursor.execute("CREATE TABLE test (id INT, name TEXT)")  # 执行 SQL

3. cursor.execute(sql[, parameters])

功能：执行单条 SQL 语句。
参数：

• sql：SQL 语句（如 SELECT * FROM table）。
• parameters：参数化查询的值（元组或字典）。

示例：

# 插入单条记录（参数化查询）
cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ("Alice", 30))

# 查询记录
cursor.execute("SELECT * FROM users WHERE age > ?", (25,))
results = cursor.fetchall()

4. cursor.executemany(sql, seq_of_params)

功能：批量执行 SQL 语句（如批量插入）。
参数：

• sql：SQL 语句（需包含占位符）。
• seq_of_params：参数序列（列表或元组的列表）。

示例：

data = [
    ("Bob", 25),
    ("Charlie", 35),
    ("David", 40)
]

cursor.executemany("INSERT INTO users (name, age) VALUES (?, ?)", data)
conn.commit()  # 提交事务

5. cursor.executescript(sql_script)

功能：执行多个 SQL 语句（如创建表、插入初始数据）。
参数：

• sql_script：包含多条 SQL 语句的字符串。

示例：

sql_script = """
CREATE TABLE IF NOT EXISTS products (id INT, name TEXT);
INSERT INTO products (id, name) VALUES (1, 'Apple');
INSERT INTO products (id, name) VALUES (2, 'Banana');
"""

cursor.executescript(sql_script)

6. cursor.fetchone() / fetchall() / fetchmany(size)

功能：获取查询结果。

• fetchone()：返回下一行，无结果时返回 None。
• fetchall()：返回所有结果（列表）。
• fetchmany(size)：返回指定数量的结果（列表）。

示例：

cursor.execute("SELECT * FROM users")

# 获取单条记录
row = cursor.fetchone()  # (1, 'Alice', 30)

# 获取多条记录
many_rows = cursor.fetchmany(2)  # 接下来的两条记录
all_rows = cursor.fetchall()     # 剩余的所有记录

7. conn.commit()

功能：提交当前事务（永久保存更改）。
示例：

cursor.execute("UPDATE users SET age = 31 WHERE name = 'Alice'")
conn.commit()  # 提交更新

8. conn.rollback()

功能：回滚当前事务（撤销未提交的更改）。
示例：

try:
    cursor.execute("DELETE FROM users WHERE age < 20")
    # 模拟错误
    1 / 0
except Exception:
    conn.rollback()  # 回滚删除操作

9. conn.close()

功能：关闭数据库连接（释放资源）。
示例：

conn.close()  # 关闭连接

10. conn.row_factory

功能：设置结果集的返回格式（如字典而非元组）。
示例：

# 返回字典格式的结果
conn.row_factory = sqlite3.Row
cursor.execute("SELECT * FROM users")
row = cursor.fetchone()
print(dict(row))  # {'id': 1, 'name': 'Alice', 'age': 30}

11. conn.create_function(name, num_params, func)

功能：创建自定义 SQL 函数。
参数：

• name：函数名（SQL 中使用）。
• num_params：参数数量。
• func：Python 函数。

示例：

def uppercase(text):
    return text.upper()

conn.create_function("upper_case", 1, uppercase)
cursor.execute("SELECT upper_case(name) FROM users")
print(cursor.fetchone())  # ('ALICE',)

12. conn.set_trace_callback(callback)

功能：设置 SQL 执行跟踪（调试用途）。
示例：

def trace_callback(sql):
    print(f"SQL执行: {sql}")

conn.set_trace_callback(trace_callback)
cursor.execute("SELECT 1")  # 会触发打印 SQL 语句

完整示例：图书管理系统

下面是一个使用上述函数的完整示例，实现简单的图书管理系统：

import sqlite3

# 连接数据库
conn = sqlite3.connect('library.db')
conn.row_factory = sqlite3.Row  # 返回字典格式
cursor = conn.cursor()

# 创建表（如果不存在）
cursor.execute('''
CREATE TABLE IF NOT EXISTS books (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    author TEXT,
    year INTEGER
)
''')
conn.commit()

# 添加图书
def add_book(title, author, year):
    cursor.execute(
        "INSERT INTO books (title, author, year) VALUES (?, ?, ?)",
        (title, author, year)
    )
    conn.commit()
    print(f"添加图书: {title}")

# 查询所有图书
def get_all_books():
    cursor.execute("SELECT * FROM books")
    return cursor.fetchall()

# 根据年份筛选图书
def get_books_by_year(year):
    cursor.execute("SELECT * FROM books WHERE year = ?", (year,))
    return cursor.fetchall()

# 更新图书信息
def update_book_title(book_id, new_title):
    cursor.execute(
        "UPDATE books SET title = ? WHERE id = ?",
        (new_title, book_id)
    )
    conn.commit()
    print(f"更新图书ID {book_id} 的标题为: {new_title}")

# 删除图书
def delete_book(book_id):
    cursor.execute("DELETE FROM books WHERE id = ?", (book_id,))
    conn.commit()
    print(f"删除图书ID: {book_id}")

# 使用示例
if __name__ == "__main__":
    # 添加图书
    add_book("Python Crash Course", "Eric Matthes", 2015)
    add_book("Fluent Python", "Luciano Ramalho", 2015)
    add_book("Effective Python", "Brett Slatkin", 2019)

    # 查询所有图书
    print("\n所有图书:")
    for book in get_all_books():
        print(dict(book))

    # 更新图书
    update_book_title(1, "Python Crash Course (2nd Edition)")

    # 按年份筛选
    print("\n2015年出版的图书:")
    for book in get_books_by_year(2015):
        print(dict(book))

    # 删除图书
    delete_book(3)

    # 关闭连接
    conn.close()

注意事项

1. 事务管理：

   • 对数据库的修改（INSERT/UPDATE/DELETE）必须通过 conn.commit() 提交，否则更改会回滚。
   • 批量操作时，建议使用 with conn: 自动管理事务：

     with conn:
         conn.execute("INSERT INTO ...")
     

2. 参数化查询：
   始终使用参数化查询（? 或 :name 占位符），避免 SQL 注入：

   # 安全方式
   cursor.execute("SELECT * FROM users WHERE name = ?", (name,))
   

3. 资源管理：
   使用完毕后，调用 conn.close() 关闭连接，或使用 with 语句自动管理：

   with sqlite3.connect('example.db') as conn:
       # 使用连接
       pass  # 离开 with 块时自动关闭连接
   

通过这些函数，你可以灵活操作 SQLite 数据库，满足大多数小型项目的需求。