第 75 课·模块十四：数据库操作进阶

连接 PostgreSQL/MySQL 数据库

databasespostgresqlmysql

Python 编程：从零基础到精通

第 75 课 - 连接 PostgreSQL/MySQL 数据库

模块十四：数据库操作

1. 学习目标

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

  • 安装并使用 psycopg2 和 mysql-connector-python 驱动程序。
  • 编写 Python 代码 连接本地或远程的 PostgreSQL 和 MySQL 数据库。
  • 执行基本的 CRUD (创建、读取、更新、删除) 操作。
  • 理解并使用 Python DB-API 2.0 标准来操作不同数据库。
  • 安全地管理 数据库连接和游标，避免资源泄露。

2. 核心概念

在上一课中，我们学习了 Python DB-API 这个"通用插座"的标准。现在，我们要将这个标准付诸实践，连接两种主流关系型数据库：PostgreSQL 和 MySQL。

想象一下，数据库是一个巨大的、结构化的仓库（比如图书馆），而 Python 程序是想要存取货物（数据）的人。DB-API 就像是仓库管理员规定的一套标准交接流程和表格。具体连接哪个仓库（PostgreSQL 还是 MySQL），则需要使用对应的"钥匙"（数据库驱动程序）。

关键区别：

  • PostgreSQL：以其强大的功能、标准符合性和扩展性著称，常被开发者认为是更"先进"的选择。驱动通常使用 psycopg2。
  • MySQL：以其易用性、高性能和广泛使用而闻名，尤其是在 Web 领域。官方驱动是 mysql-connector-python。

尽管底层数据库不同，但遵循 DB-API 的 Python 代码在结构上惊人地相似。学习一个，另一个就很容易上手。

3. 代码示例

3.1 环境准备

首先，你需要安装对应的数据库驱动程序。打开终端或命令提示符，运行：

# 安装 PostgreSQL 驱动
pip install psycopg2-binary

# 安装 MySQL 官方驱动
pip install mysql-connector-python

你还需要在本地或服务器上运行一个 PostgreSQL 或 MySQL 服务，并有一个可用的数据库和用户。

3.2 连接数据库

连接字符串（DSN）包含了访问数据库所需的所有信息。

import psycopg2  # 用于 PostgreSQL
import mysql.connector  # 用于 MySQL
from mysql.connector import Error

# --- 连接到 PostgreSQL ---
pg_conn = None
try:
    # 连接字符串格式: host, port, dbname, user, password
    pg_conn = psycopg2.connect(
        host="localhost",
        port="5432",
        dbname="mytestdb",
        user="postgres",
        password="your_password"
    )
    print("成功连接到 PostgreSQL！")
    # 连接对象现在代表一个到数据库的会话
except (Exception, psycopg2.DatabaseError) as error:
    print(f"PostgreSQL 连接错误: {error}")

# --- 连接到 MySQL ---
my_conn = None
try:
    # 连接参数
    my_conn = mysql.connector.connect(
        host="localhost",
        database="mytestdb",
        user="root",
        password="your_password"
    )
    if my_conn.is_connected():
        db_info = my_conn.get_server_info()
        print(f"成功连接到 MySQL 服务器，版本： {db_info}")
except Error as e:
    print(f"MySQL 连接错误: {e}")

3.3 执行 SQL 查询（读取数据）

连接建立后，我们需要创建一个游标（Cursor） 对象来执行 SQL 语句并获取结果。

# 假设我们已经成功建立了连接 `conn`
# 创建一个游标对象
cursor = pg_conn.cursor() # 或 my_conn.cursor()

# 执行一个 SELECT 查询
cursor.execute("SELECT id, name, age FROM students WHERE age > %s", (20,)) # 使用参数化查询，防止SQL注入

# 获取所有结果
rows = cursor.fetchall()
print("查询结果：")
for row in rows:
    # row 是一个元组，如 (1, 'Alice', 22)
    print(f"ID: {row[0]}, 姓名: {row[1]}, 年龄: {row[2]}")

# 使用完游标后，关闭它
cursor.close()

3.4 执行 SQL 更新（创建、修改、删除数据）

对于 INSERT, UPDATE, DELETE 操作，执行后需要提交（commit） 事务才能使更改生效。

cursor = conn.cursor()

try:
    # 插入一条新记录
    insert_query = """INSERT INTO students (name, age, grade) VALUES (%s, %s, %s)"""
    data = ("Bob", 23, "A")
    cursor.execute(insert_query, data)

    # 更新一条记录
    update_query = "UPDATE students SET grade = %s WHERE name = %s"
    cursor.execute(update_query, ("A+", "Bob"))

    # 提交事务，使以上更改持久化到数据库
    conn.commit()
    print("数据更新成功！")

except (Exception, psycopg2.DatabaseError) as error:
    print(f"操作出错: {error}")
    # 如果出错，回滚所有更改
    conn.rollback()
finally:
    # 无论是否成功，都关闭游标
    cursor.close()

3.5 关闭连接

使用完数据库连接后，务必将其关闭，以释放资源。

if pg_conn:
    pg_conn.close()
    print("PostgreSQL 连接已关闭。")

if my_conn:
    my_conn.close()
    print("MySQL 连接已关闭。")

最佳实践提示：在实际项目中，我们通常会使用 with 语句（上下文管理器）或 连接池 来管理连接和游标，确保它们被正确关闭。这里为了演示原理，我们使用了手动管理。

4. 实践练习

练习 1：基础连接与查询（易）

编写一个 Python 脚本，连接到你的 MySQL（或 PostgreSQL）数据库中的 school 数据库。从 courses 表中查询所有课程，并打印出课程名称和学分。

  • 要求：
    1. 处理连接可能出现的错误。
    2. 使用参数化查询（即使查询条件固定）。
    3. 确保正确关闭游标和连接。
  • 预期输出示例： 
    成功连接到数据库。
课程列表：
数学分析 - 学分: 4
大学英语 - 学分: 3
Python编程 - 学分: 3
数据库原理 - 学分: 3

练习 2：参数化查询与异常处理（中）

扩展练习 1 的脚本。编写一个函数 get_students_by_major(conn, major_name)，它接受一个数据库连接和一个专业名称作为参数，返回该专业的所有学生信息（学号、姓名、入学年份）。

  • 要求：
    1. 函数内部处理数据库游标操作和可能的查询错误。
    2. 使用 %s 或 %s 进行参数化查询。
    3. 在主程序中调用该函数，并打印结果。
  • 预期输出示例（输入 major_name="计算机科学"）： 
    专业: 计算机科学 的学生：
学号: 2023001, 姓名: 张三, 入学年份: 2023
学号: 2023015, 姓名: 李四, 入学年份: 2023
学号: 2022088, 姓名: 王五, 入学年份: 2022

练习 3：综合应用 - 事务与上下文管理器（难）

编写一个"学生成绩更新"脚本。该脚本从一个文件 grade_updates.csv 中读取数据（格式：学号,课程号,新成绩），然后更新数据库中的 scores 表。

  • 要求：
    1. 所有更新应在同一个事务中。如果一行数据更新失败（例如，学号不存在），则整个事务回滚，不保存任何更改。
    2. 使用 with 语句来管理数据库连接（如果驱动支持）。psycopg2 的连接对象支持上下文管理器。
    3. 将成功更新的记录数和失败的原因记录到日志文件 update_log.txt。
  • CSV 文件 grade_updates.csv 示例内容： 
    2023001, CS101, 92
2023015, CS101, 88
9999999, CS101, 75  # 假设这是一个无效学号
2022088, CS101, 91
  • 预期输出示例： 
    开始处理成绩更新...
更新成功: 学号 2023001, 课程 CS101, 成绩 92
更新成功: 学号 2023015, 课程 CS101, 成绩 88
更新失败（事务已回滚）: 无效学号 9999999 或课程 CS101 不存在。
详细错误已记录到 update_log.txt。
本次操作因错误未保存任何更改。

5. 常见错误

  1. 错误：OperationalError: could not connect to server: Connection refused

    • 原因：数据库服务未运行，或 host/port 配置错误。
    • 解决：确保数据库服务已启动，并检查连接参数。

  2. 错误：ProgrammingError: syntax error at or near "%"

    • 原因：在非参数化查询的 SQL 字符串中误用了 %。或者，在使用 psycopg2 时，参数化查询的占位符写成了 %s，但在字符串中意外地使用了 %（如 LIKE 'Joh%'）。
    • 解决：确保在 SQL 字符串中，只有作为参数占位符的 %s 是 % 开头。对于 LIKE 中的通配符，应将其作为参数的一部分传递：cursor.execute("... WHERE name LIKE %s", (search_term + '%',))

  3. 错误：DataError: Incorrect integer value: 'abc' for column 'age'

    • 原因：尝试插入或更新的数据类型与数据库列定义不匹配。
    • 解决：检查插入数据的类型，或在 Python 中进行类型转换。

  4. 错误：忘记调用 commit()，导致数据未保存

    • 现象：程序执行没有报错，但数据库中的数据没有改变。
    • 解决：对于 INSERT, UPDATE, DELETE 操作，必须在操作完成后调用 connection.commit()。

  5. 错误：资源泄露（连接/游标未关闭）

    • 现象：长时间运行的程序可能因为数据库连接数耗尽而崩溃。
    • 解决：务必在 finally 块中或使用 with 语句来确保连接和游标被关闭。养成使用 with 语句的习惯。

6. 小结

本课我们迈出了与真实数据库交互的关键一步：

  • 驱动程序 是连接 Python 和特定数据库的桥梁，psycopg2 和 mysql-connector-python 是两把最常用的"钥匙"。
  • DB-API 的统一性 让我们能够用几乎相同的代码结构（连接 -> 游标 -> 执行 -> 获取/提交 -> 关闭）操作 PostgreSQL 和 MySQL，极大提高了代码的可移植性。
  • 参数化查询 (%s) 是防止 SQL 注入攻击的必须遵守的安全实践，切勿使用字符串拼接来构造查询。
  • 事务管理 通过 commit() 和 rollback() 保证了数据操作的原子性和一致性。
  • 资源管理 至关重要，始终确保游标和连接被正确关闭。

掌握了数据库连接和基本 CRUD，你就拥有了构建数据驱动应用的基石。在下一课，我们将学习如何使用 Peewee 这个轻量级 ORM，它能让我们用更 Pythonic、更简洁的方式（而不是直接写 SQL）来操作数据库。

练习编辑器

python
Loading...

继续学习

完成本课后，建议继续学习下一课「轻量级 ORM：Peewee 入门」 以巩固所学知识。