第74课 - Python DB-API 与数据库连接

1. 学习目标

完成本课学习后，你将能够：

理解 Python DB-API 2.0 规范的核心思想和作用。
掌握使用 sqlite3 模块（内置数据库）进行数据库连接、执行查询和关闭连接的完整流程。
熟练使用游标（Cursor）对象执行 SQL 语句并处理结果。
理解并正确管理数据库事务（Transaction）。

2. 核心概念

什么是 DB-API？

DB-API 是 Python 定义的一套通用数据库接口规范（PEP 249）。你可以把它想象成一个"标准插座"：无论你使用的是 SQLite、MySQL 还是 PostgreSQL，只要你安装了相应的数据库驱动（适配器），就能使用几乎相同的 Python 代码来操作数据库。这使得你的数据库代码具有很强的可移植性。

核心对象与流程

使用 DB-API 操作数据库通常遵循一个固定流程，涉及两个关键对象：

连接对象 (Connection)：代表与数据库的一次物理连接。由驱动模块的 connect() 函数创建。它负责管理事务、处理连接生命周期。
游标对象 (Cursor)：从连接中创建，代表数据库中的一个"操作句柄"。你通过游标来执行 SQL 语句、遍历查询结果。

标准流程： 导入模块 -> 建立连接 -> 创建游标 -> 执行SQL -> 处理结果 -> 提交事务 -> 关闭游标/连接

事务管理

数据库操作通常包含在事务中。一组操作要么全部成功（commit），要么全部失败并撤销（rollback）。在 DB-API 中，默认情况下连接会开启隐式事务，你必须显式调用 commit() 来使 INSERT, UPDATE, DELETE 等操作永久生效，否则断开连接后更改会丢失。SELECT 语句无需提交。

3. 代码示例

下面是一个使用 Python 内置 sqlite3 模块操作内存数据库（数据存于内存，程序结束即销毁）的完整、可运行示例。我们模拟一个简单的"用户"表。

import sqlite3

# 1. 建立连接 (此处使用内存数据库)
# connect() 会返回一个连接对象。如果文件已存在，则连接到该文件；若不存在，则创建。
# ":memory:" 表示使用内存数据库。
conn = sqlite3.connect(":memory:")
print("数据库连接已建立。")

# 2. 创建游标对象
cursor = conn.cursor()

# 3. 创建一个表
create_table_sql = """
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    age INTEGER
);
"""
cursor.execute(create_table_sql)
print("表 'users' 已创建。")

# 4. 插入数据 (使用参数化查询，防止SQL注入)
insert_sql = "INSERT INTO users (name, age) VALUES (?, ?)"
users_data = [("张三", 28), ("李四", 32), ("王五", 25)]
cursor.executemany(insert_sql, users_data) # executemany 可高效插入多行

# 5. 提交事务，使 INSERT 操作生效
conn.commit()
print(f"成功插入 {len(users_data)} 条数据。")

# 6. 查询数据
select_sql = "SELECT id, name, age FROM users WHERE age > ?"
cursor.execute(select_sql, (26,)) # 参数要以元组形式传递，注意末尾的逗号
print("\n年龄大于26岁的用户：")
print("-" * 30)
print(f"{'ID':<5} {'姓名':<10} {'年龄':<5}")
print("-" * 30)

# fetchall() 获取所有结果行，每行是一个元组
results = cursor.fetchall()
for row in results:
    print(f"{row[0]:<5} {row[1]:<10} {row[2]:<5}")

# 7. 更新数据
update_sql = "UPDATE users SET age = ? WHERE name = ?"
cursor.execute(update_sql, (29, "张三"))
print(f"\n更新了 {cursor.rowcount} 行数据。") # rowcount 属性返回受影响的行数

# 8. 再次查询，验证更新
cursor.execute("SELECT name, age FROM users WHERE name = '张三'")
updated_user = cursor.fetchone() # fetchone 获取一行结果
if updated_user:
    print(f"更新后的张三: 姓名={updated_user[0]}, 年龄={updated_user[1]}")

# 9. 提交更新操作
conn.commit()

# 10. 删除数据并提交
delete_sql = "DELETE FROM users WHERE age < ?"
cursor.execute(delete_sql, (27,))
conn.commit()
print(f"删除了 {cursor.rowcount} 条年龄小于27岁的数据。")

# 11. 查看最终表内容
cursor.execute("SELECT * FROM users")
all_users = cursor.fetchall()
print("\n最终表中的所有用户：")
for user in all_users:
    print(user)

# 12. 关闭游标和连接 (良好实践)
cursor.close()
conn.close()
print("\n数据库连接已关闭。")

4. 实践练习

练习一：基础操作巩固 创建一个内存数据库，建立一个名为 books 的表（字段：id 自增主键，title 书名，author 作者，price 价格）。执行以下操作并提交：

插入三本书的信息。
查询所有价格低于50元的书，并打印书名和作者。
将一本特定书的价格上调10%。

练习二：事务回滚模拟 基于上一题的 books 表，尝试进行以下操作序列：

开始一个操作（例如更新一本书的价格）。
模拟一个错误（例如，打印一条"操作中发生错误！"的信息）。
在错误发生后，调用 conn.rollback() 回滚事务。
最后查询该书价格，验证是否已恢复原值。

练习三：数据导入与查询 给定一个包含电影信息的列表，将数据导入 movies 表中，然后查询并打印出所有由特定导演执导的电影名称。

# 给定数据
movies_data = [
    ("肖申克的救赎", "弗兰克·德拉邦特", 9.7),
    ("阿甘正传", "罗伯特·泽米吉斯", 9.5),
    ("盗梦空间", "克里斯托弗·诺兰", 9.3),
    ("泰坦尼克号", "詹姆斯·卡梅隆", 9.4),
    ("星际穿越", "克里斯托弗·诺兰", 9.4)
]
# 目标：导入后，查询并打印所有导演为"克里斯托弗·诺兰"的电影名。

5. 常见错误

忘记提交事务：执行 INSERT、UPDATE、DELETE 后，没有调用 conn.commit()，导致数据未被保存。
```
cursor.execute("INSERT INTO ...")
# conn.commit() 这行被注释掉了！
conn.close() # 数据不会被写入数据库
```
错误地使用游标：试图从一个已关闭的游标或连接中获取数据。
```
cursor.close()
results = cursor.fetchall() # 抛出 ProgrammingError: Cannot operate on a closed cursor.
```

SQL 注入风险：直接使用字符串拼接来构建 SQL 语句。

user_input = "Robert'; DROP TABLE users; --"
# 危险！
bad_sql = f"SELECT * FROM users WHERE name = '{user_input}'"
cursor.execute(bad_sql)
# 安全做法：使用参数化查询
safe_sql = "SELECT * FROM users WHERE name = ?"
cursor.execute(safe_sql, (user_input,))

事务管理不当：在循环中多次执行 DML 语句（增删改）却只在最后提交一次。如果中途出错，之前的操作也会丢失。更稳健的做法是明确事务边界。

6. 小结

本节课，我们学习了 Python 操作数据库的基石——DB-API：

DB-API 是一套标准，让 Python 代码能够以统一的方式访问不同数据库。
连接对象 (Connection) 管理与数据库的连接和事务。
游标对象 (Cursor) 是执行 SQL 和获取结果的通道。
标准流程 为：连接 -> 游标 -> 执行SQL -> 处理结果 -> 提交 -> 关闭。
事务至关重要，务必使用 commit() 持久化更改，出错时用 rollback() 撤销。
始终使用参数化查询 (? 占位符) 来防止 SQL 注入攻击。

掌握了 sqlite3 这个"沙盒"环境，你已经具备了使用 DB-API 的核心能力。下一课，我们将学习如何安装和使用真实的外部数据库（如 PostgreSQL、MySQL）的驱动，将这套标准知识应用到生产环境中。

练习编辑器

python