PythonLearn/04_数据库/4_1_PostgreSQL与Psycopg入门

第 4-1 课：PostgreSQL 与 Psycopg 入门

一、本课定位

你已经掌握 SQL、事务和 Java 数据库开发，因此本课不再从表、字段和增删改查讲起，而是集中回答一个问题：Python 程序怎样安全地连接 PostgreSQL 并执行 SQL？

Python 数据库 API（Database API，DB-API）规定了数据库驱动的通用操作方式。Psycopg 3 是 PostgreSQL 的 Python 驱动，本课会把它与 JDBC 逐项对照。

二、本课目标

完成本课后，你将能够：

1. 解释 DB-API、Psycopg 和 PostgreSQL 的关系；
2. 使用本地 TOML 文件保存数据库连接配置；
3. 使用 psycopg.connect() 创建连接；
4. 使用游标执行只读 SQL 并取得结果；
5. 使用参数化查询传递数据；
6. 使用 with 自动释放连接和游标；
7. 对照 JDBC 理解 Python 数据库代码；
8. 识别连接失败、依赖缺失和参数占位符错误。

三、前置知识

本课默认已经掌握：

• PostgreSQL 数据库地址、端口、数据库、用户名和密码的含义；
• SELECT 基础语法；
• JDBC 的 Connection、PreparedStatement 和 ResultSet；
• Python 函数、异常、上下文管理器和文件读取基础。

本课只连接专用练习数据库。不要连接生产数据库，也不要使用具有创建用户、删除数据库等高权限的账号。

四、DB-API、Psycopg 与 JDBC

DB-API 是 Python 数据库驱动遵循的接口约定，不是一个需要单独安装的框架。不同数据库有不同驱动，但常见操作方式比较统一。

Java/JDBC	Python/Psycopg	作用
PostgreSQL JDBC Driver	Psycopg 3	与 PostgreSQL 通信
DriverManager.getConnection()	psycopg.connect()	创建数据库连接
Connection	Connection	表示一次数据库会话
PreparedStatement	Cursor.execute(sql, params)	执行参数化 SQL
ResultSet	Cursor 和 fetchone() 等方法	读取查询结果
try-with-resources	with	自动释放资源
SQLException	psycopg.Error	表示数据库访问错误

Psycopg 的游标（Cursor）同时承担“执行 SQL”和“读取结果”的职责。它不是数据库界面中的鼠标光标。

五、准备远程练习数据库

建议为课程准备：

• 一个独立数据库，例如 python_course；
• 一个专用账号，例如 python_student；
• 只授予课程所需权限；
• 不与生产环境或其他重要测试数据共用。

本课不使用环境变量，也不要求把所有信息拼成数据库连接串，而是把连接参数分字段写入 TOML 配置：

[postgresql]
host = "数据库主机"
port = 5432
dbname = "数据库名"
user = "用户名"
password = "密码"
connect_timeout = 10

仓库中的 config.example.toml 只包含占位内容，可以提交 Git。实际配置写入同目录的 config.toml，该文件已经被项目 .gitignore 排除。

TOML（Tom's Obvious Minimal Language）是一种结构化配置格式。Python 3.11 及以上版本内置 tomllib，读取 TOML 不需要安装额外依赖，也不会修改操作系统或当前进程的环境变量。

六、安装 Psycopg 3

你当前使用 Conda 的 base 环境，可以直接安装 Psycopg：

conda install -n base -c conda-forge "psycopg>=3,<4" psycopg-c

安装完成后验证：

python -c "import psycopg; print(psycopg.__version__)"

如果以后改用 Python 虚拟环境，也可以使用 requirements.txt 安装。无论使用哪种方式，导入时都写 import psycopg，不是 import psycopg3。

七、创建本地 TOML 配置

进入本课目录，复制配置模板：

Copy-Item .\config.example.toml .\config.toml

然后只在本地 config.toml 中填写真实的主机、端口、数据库名、用户名和密码。程序通过 Python 文件自身的位置寻找配置，因此从项目根目录或课程目录启动都可以。

7.1 为什么选择 TOML

• Python 3.13 可以直接使用内置 tomllib；
• 字段和类型清楚，端口可以保持整数；
• 不需要污染环境变量；
• 比 XML 简洁，比 YAML 少一个第三方解析依赖；
• 配置节结构与 Java 项目的 YAML、Properties 配置思路相近。

7.2 代码硬编码可以怎么写

从技术上可以直接构造字典：

database_config = {
    "host": "数据库主机",
    "port": 5432,
    "dbname": "数据库名",
    "user": "用户名",
    "password": "密码",
}

这能帮助理解 psycopg.connect() 接收哪些参数，但真实密码一旦硬编码（Hard Coding）就可能进入 Git 历史。本课程标准示例使用 config.toml；如自行尝试硬编码，只能放在不提交的个人练习文件中。

八、完整示例

示例文件为 connection_example.py。核心结构如下：

database_config = load_database_config(CONFIG_PATH)

with psycopg.connect(**database_config) as connection:
    with connection.cursor() as cursor:
        cursor.execute(
            "SELECT current_database(), current_user, %s::text",
            ("Psycopg 连接成功",),
        )
        database_name, user_name, message = cursor.fetchone()

示例只读取当前数据库名和当前用户，不创建表、不修改数据。

8.1 为什么参数使用 %s

Psycopg 使用 %s 表示值参数，即使参数是整数也仍然使用 %s。参数值通过 execute() 的第二个参数单独传入：

cursor.execute("SELECT %s::text", (message,))

不要使用 f-string、字符串拼接或 % 运算符把数据直接写进 SQL：

# 错误示例：数据被直接拼进 SQL，可能产生 SQL 注入。
cursor.execute(f"SELECT '{message}'")

8.2 单个参数为什么有逗号

(message,)

这是只有一个元素的元组。写成 (message) 只是在字符串外加括号，不是元组。

8.3 with 做了什么

• 离开游标的 with 时关闭游标；
• 离开连接的 with 时结束事务并关闭连接；
• 正常离开连接块时提交当前事务；
• 块内出现异常时回滚当前事务。

本课执行的是只读查询，但仍要建立正确的资源和事务管理习惯。

九、运行方法

确认 config.toml 已经创建并填写完成，然后在项目根目录运行：

python .\04_数据库\4_1_PostgreSQL与Psycopg入门\connection_example.py

正常情况下会看到类似结果：

连接成功。
当前数据库：python_course
当前用户：python_student
参数化查询结果：Psycopg 连接成功

数据库名和用户名应以你的远程练习环境为准。

十、关键执行顺序

1. main() 调用 load_database_config(CONFIG_PATH)；
2. Path 根据当前 Python 文件的位置定位 config.toml；
3. tomllib.load() 读取 [postgresql] 配置节；
4. 缺少文件、配置节或必填字段时主动抛出中文 RuntimeError；
5. psycopg.connect(**database_config) 把字典展开为连接参数；
6. connection.cursor() 创建游标；
7. cursor.execute() 将 SQL 和查询参数分别交给驱动；
8. cursor.fetchone() 读取一行结果；
9. 两层 with 依次释放游标和连接；
10. main() 输出结果，并分别处理配置异常和数据库异常。

十一、常见错误

11.1 ModuleNotFoundError: No module named 'psycopg'

含义：当前 Python 环境没有安装 Psycopg。

处理：激活 .venv，再使用本课的 requirements.txt 安装依赖。可以运行 python -m pip show psycopg 检查安装位置。

11.2 未找到 config.toml

含义：本课目录中还没有实际配置文件。

处理：把 config.example.toml 复制为 config.toml 并填写连接参数。不要直接把真实信息写进示例模板。

11.3 TOML 格式错误

含义：配置不符合 TOML 语法，例如字符串缺少引号或同一个键重复出现。

处理：对照 config.example.toml 检查配置节、等号、引号和字段名。

11.4 connection refused 或连接超时

含义：程序无法到达数据库地址和端口。

检查数据库服务、主机名、端口、防火墙、白名单和 VPN，不要先假设一定是密码错误。

11.5 password authentication failed

含义：服务器已经收到连接，但用户名或密码校验失败。

检查 config.toml 中的账号、密码和数据库名。分字段传参时无需手动拼接 URL，也避免了连接串中特殊字符编码问题。

11.6 execute() 参数写错

下面两种写法都不符合本课要求：

cursor.execute("SELECT '%s'", (message,))
cursor.execute("SELECT %s", message)

占位符外不要加引号，参数序列只有一个值时要写成 (message,)。

11.7 使用 fetchone() 却没有处理空结果

fetchone() 在没有数据时可能返回 None。本课查询必然返回一行，所以可以直接解包；以后查询业务表时必须判断空结果。

十二、课堂练习

打开 practice.py，按照注释完成练习。练习要求你独立完成：

1. 使用 Path 定位本地 config.toml；
2. 使用 tomllib 读取 PostgreSQL 配置；
3. 使用两层 with 管理连接和游标；
4. 执行包含两个参数的只读查询；
5. 使用 fetchone() 保存并输出结果；
6. 分别捕获配置读取异常和数据库访问异常。

练习仍然只执行只读 SQL，不创建、修改或删除远程数据。

十三、本课小结

• DB-API 是 Python 数据库驱动的通用接口约定；
• Psycopg 3 是 PostgreSQL 的 Python 驱动；
• Psycopg 的基础层次与 JDBC 相似；
• Connection 表示数据库会话，Cursor 负责执行 SQL 和读取结果；
• SQL 与参数必须分开传递；
• with 用于可靠地结束事务和释放资源；
• 数据库密码保存在被 Git 忽略的本地 TOML 配置中，不写入环境变量或源码。

十四、验收标准

• 能说明 DB-API、Psycopg 和 PostgreSQL 的关系；
• 能说出 Psycopg 与 JDBC 的主要对象对应关系；
• 能复制模板并通过本地 config.toml 配置连接参数；
• connection_example.py 可以连接练习数据库并输出三项查询结果；
• practice.py 使用参数化查询，没有拼接 SQL；
• 连接和游标都通过 with 管理；
• 能区分网络不可达、认证失败和依赖缺失；
• 程序没有读取、设置或修改环境变量；
• 未把 config.toml 或真实数据库连接信息写入 Git 跟踪文件。