Python操作PostgreSQL的利器：psycopg2 & psycopg3
一、概述
1.1 psycopg系列简介
psycopg 是 Python 生态中最成熟、最广泛使用的 PostgreSQL 数据库适配器（Adapter）。它实现了 Python DB-API 2.0 规范，允许 Python 应用以标准化接口与 PostgreSQL 数据库交互。自 2004 年首次发布以来，psycopg 已成为 Django、SQLAlchemy、Apache Airflow 等众多知名项目的基础依赖。
psycopg 系列包含两个主版本分支：
- psycopg2：经典稳定版，采用纯C扩展实现，支持 Python 2/3，性能优异，社区生态成熟
- psycopg3（项目包名 psycopg）：新一代重构版本，采用 Python + 可选C加速架构，原生支持异步（asyncio）、连接池、Pipeline模式等现代化特性
1.2 版本演进与定位
psycopg2 作为第二代适配器（psycopg1 已废弃），在长达15年的发展中积累了庞大的用户基础。其最新版本为 2.9.x，当前处于维护模式，不再添加新功能。
psycopg3 于 2021 年发布 1.0 正式版（包名变更为 psycopg），是 psycopg2 的继任者而非简单升级。它在保持 DB-API 接口兼容性的同时，带来了架构层面的根本性变化。当前最新版本为 3.3.x。
两者的定位关系可总结为：psycopg2 是「经典稳定之选」；psycopg3 是「面向未来的现代化方案」。
二、psycopg2 深度解析
2.1 安装与基本架构
psycopg2 提供两种安装方式：
- 预编译二进制包：pip install psycopg2-binary（适用于开发/测试环境）
- 源码编译安装：pip install psycopg2（需要 libpq-dev 和 gcc，适用于生产环境）
psycopg2 底层采用纯 C 扩展实现，直接调用 PostgreSQL 的 C 客户端库 libpq，在解析、序列化等 CPU 密集型操作上具有天然性能优势。但其模块结构为单体架构——所有功能集中在单一 C 扩展中。
2.2 核心 API
psycopg2 的核心 API 遵循 Python DB-API 2.0 规范，主要包含以下组件：
| 组件 | 说明 |
|---|---|
| psycopg2.connect() | 创建数据库连接，返回 Connection 对象 |
| Connection.cursor() | 创建游标对象，用于执行 SQL 语句 |
| Cursor.execute() | 执行单条 SQL 语句，支持参数化查询 |
| Cursor.executemany() | 批量执行同一条 SQL 语句（多条参数组合） |
| Cursor.fetchone() | 获取结果集的下一行 |
| Cursor.fetchall() | 获取所有剩余行 |
| Cursor.fetchmany(n) | 获取最多 n 行 |
| Connection.commit() | 提交当前事务 |
| Connection.rollback() | 回滚当前事务 |
基本使用示例：
import psycopg2
conn = psycopg2.connect(
host="localhost", port=5432,
dbname="mydb", user="postgres", password="secret"
)
cur = conn.cursor()
cur.execute('SELECT id, name FROM users WHERE age > %s', (18,))
rows = cur.fetchall()
cur.close()
conn.close()2.3 连接管理
psycopg2 的连接对象（Connection）管理数据库会话。默认情况下，连接处于「非自动提交」模式——每条语句需显式调用 commit() 或 rollback()。可通过设置 autocommit=True 切换到自动提交模式。
psycopg2 的连接对象支持上下文管理器（with 语句），退出时自动提交或回滚事务，但不会关闭连接本身：
with conn: # 退出时：异常→rollback，正常→commit
cur.execute("INSERT INTO logs VALUES (%s)", ("event",))psycopg2 内置了简单的线程安全连接池 psycopg2.pool，支持 SimpleConnectionPool 和 ThreadedConnectionPool 两种实现，但功能较为基础，缺乏超时、健康检查等现代连接池能力。
2.4 游标与行工厂
游标（Cursor）是执行 SQL 和获取结果的核心对象。psycopg2 通过不同的游标子类改变结果行的返回格式：
| 游标类 | 返回格式 | 示例 |
|---|---|---|
| Cursor | 元组（默认） | (1, 'Alice') |
| DictCursor | 类字典（可索引+键值） | 可 cur['name'] 访问 |
| RealDictCursor | 纯字典 | {'id': 1, 'name': 'Alice'} |
| NamedTupleCursor | 命名元组 | Row(id=1, name='Alice') |
使用示例：
from psycopg2.extras import RealDictCursor
cur = conn.cursor(cursor_factory=RealDictCursor)
cur.execute("SELECT id, name FROM users")
row = cur.fetchone()
print(row['name']) # 按列名访问2.5 事务处理
psycopg2 的事务管理相对简洁，遵循 PostgreSQL 的原生事务语义：
- 默认使用 读已提交（READ COMMITTED） 隔离级别
- 可通过 connection.set_isolation_level() 调整隔离级别
- 支持保存点（Savepoint），通过 with conn: 自动管理
- 两阶段提交（XA）需要额外封装
注意：psycopg2 使用 with conn: 管理事务；而 psycopg3 中 with conn: 会关闭整个连接，事务需用 conn.transaction() 管理。这是迁移中最容易踩坑的变化之一。
2.6 COPY 操作
psycopg2 提供了 PostgreSQL COPY 协议的高效封装，支持多种形式的批量数据导入：
- copy_from(file, table)：从文件对象导入数据到表
- copy_to(file, table)：将表数据导出到文件对象
- copy_expert(sql, file)：执行任意 COPY SQL 语句
- 大数据量导入场景下，COPY 比逐行 INSERT 快 10-50 倍
2.7 连接池（psycopg2.pool）
psycopg2 内置两个基础连接池实现：
| 类名 | 特点 | 适用场景 |
|---|---|---|
| SimpleConnectionPool | 非线程安全，单线程使用 | 脚本、CLI 工具 |
| ThreadedConnectionPool | 线程安全，多线程共享 | 多线程 Web 应用 |
连接池使用示例：
from psycopg2 import pool
pool = pool.ThreadedConnectionPool(2, 10, host="localhost", dbname="mydb")
conn = pool.getconn()
# ... 使用连接 ...
pool.putconn(conn)2.8 最佳实践与性能调优
生产环境中使用 psycopg2 的关键优化策略：
- 使用连接池：避免频繁创建/销毁连接的开销
- 批量操作优先使用 executemany() 或 COPY：单条 INSERT 在循环中是最低效的做法
- 使用服务端游标处理大数据集：通过 cursor(name) 创建命名游标，避免一次性加载所有结果到内存
- 合理设置 autocommit：批量 DML 时关闭 autocommit 减少网络往返
- 预编译语句缓存：使用 psycopg2.extras.execute_values() 加速批量 INSERT
三、psycopg3 深度解析
3.1 安装与架构变革
psycopg3 拆分为多个可选包，依赖管理更灵活：
| 安装命令 | 说明 |
|---|---|
| pip install "psycopg[binary]" | 安装核心包 + 预编译二进制加速模块（推荐入门） |
| pip install "psycopg[c]" | 安装核心包 + 源码编译 C 加速模块（生产推荐） |
| pip install "psycopg[pool]" | 安装核心包 + 连接池模块（含同步/异步） |
| pip install "psycopg[all]" | 安装所有可选依赖 |
架构层面的核心变化：psycopg3 的主体功能由纯 Python 实现，C 加速模块为可选的 drop-in replacement。这种「Python 为主、C 为辅」的架构带来了更好的跨平台兼容性、更易于调试，并降低了编译环境依赖。
3.2 核心 API 变化
psycopg3 保留了 DB-API 核心接口，但有一些重要调整：
| 特性 | psycopg2 | psycopg3 |
|---|---|---|
| 连接函数 | psycopg2.connect() | psycopg.connect() |
| 游标工厂 | cursor_factory 参数 | row_factory 参数 |
| 上下文管理 | with conn: 管理事务 | with conn: 关闭连接 |
| 事务管理 | commit/rollback | conn.transaction() |
| 批量执行 | executemany() | executemany()（内建 Pipeline 加速） |
| 存储过程 | cursor.callproc() | 已移除，改用 execute() |
| 行格式 | DictCursor 等子类 | rows.dict_row / namedtuple_row |
psycopg3 基本使用示例：
import psycopg
from psycopg.rows import dict_row
conn = psycopg.connect("host=localhost dbname=mydb user=postgres")
cur = conn.cursor(row_factory=dict_row)
cur.execute('SELECT id, name FROM users WHERE age > %s', (18,))
for row in cur:
print(row['name'])3.3 服务端绑定
psycopg3 最关键的底层变化是默认使用服务端参数绑定（Server-side Binding）。在 psycopg2 中，%s 占位符在客户端被替换为实际值，拼接成完整 SQL 后发送；psycopg3 则将参数与 SQL 分离，通过 PostgreSQL 的扩展查询协议（Extended Query Protocol）传递。
这一变化带来的影响：
- 优点：防止 SQL 注入更彻底、支持预编译语句缓存、二进制参数传输更高效
- 限制：某些 DDL 语句（如 SET、CREATE TABLE）不能使用 %s 占位符，需改用 psycopg.sql 模块或 ClientCursor
- 兼容：可通过 ClientCursor 切换回 psycopg2 风格的客户端绑定模式
服务端绑定下的 IN 查询正确写法：
# 错误写法（psycopg2 风格，psycopg3 不支持）
conn.execute("SELECT * FROM t WHERE id IN %s", [(1,2,3)])
# 正确写法（psycopg3 推荐）
conn.execute("SELECT * FROM t WHERE id = ANY(%s)", [[1,2,3]])3.4 原生异步支持
这是 psycopg3 相比 psycopg2 最具战略意义的新增能力。psycopg3 提供了完整的 asyncio 接口：
| 同步 API | 异步 API | 说明 |
|---|---|---|
| psycopg.connect() | psycopg.AsyncConnection.connect() | 异步创建连接 |
| Connection.cursor() | AsyncConnection.cursor() | 获取异步游标 |
| Cursor.execute() | AsyncCursor.execute() | 异步执行查询 |
| Cursor.fetchone() | AsyncCursor.fetchone() | 异步获取结果 |
异步使用示例：
import asyncio
import psycopg
async def main():
async with await psycopg.AsyncConnection.connect(
"host=localhost dbname=mydb"
) as aconn:
async with aconn.cursor() as acur:
await acur.execute('SELECT %s + %s', (10, 20))
result = await acur.fetchone()
print(result[0]) # 30
asyncio.run(main())异步支持的意义在于：在 FastAPI、aiohttp 等异步 Web 框架中，数据库 I/O 不再阻塞事件循环，可以大幅提升并发吞吐量。
3.5 Pipeline 模式
Pipeline（管线）模式是 psycopg3 在 3.1 版本引入的实验性特性。其核心思想是：在普通请求-响应模式下，客户端发出一条 SQL 后必须等待服务器返回结果才能发送下一条查询；Pipeline 模式允许客户端连续发送多条查询，服务器批量响应，将多次网络往返合并为一次。
性能量化：假设网络延迟（RTT）为 100ms，需要执行 100 条独立查询：
| 模式 | 网络延迟总时间 | 加速比 |
|---|---|---|
| 普通模式 | 100 × 100ms = 10秒 | 1× |
| Pipeline 模式 | ≈ 100ms | ≈ 100× |
Pipeline 使用示例：
with conn.pipeline():
conn.execute("INSERT INTO logs VALUES (%s)", ["event1"])
conn.execute("INSERT INTO logs VALUES (%s)", ["event2"])
conn.execute("INSERT INTO logs VALUES (%s)", ["event3"])
# 退出 pipeline 块时自动发送同步点，获取所有结果从 3.1 版本起，executemany() 内部已自动启用 Pipeline 加速，无需手动包裹。
3.6 新一代连接池
psycopg3 的连接池作为独立包 psycopg_pool 发布，功能远超 psycopg2 的基础连接池：
| 类名 | 类型 | 特点 |
|---|---|---|
| ConnectionPool | 同步 | 固定/弹性连接数，后台维护线程，超时控制 |
| AsyncConnectionPool | 异步 | asyncio 原生支持，协程安全 |
| NullConnectionPool | 同步 | 不预留空闲连接，按需创建 |
| AsyncNullConnectionPool | 异步 | 异步版空连接池 |
关键配置参数：
| 参数 | 说明 | 默认值 |
|---|---|---|
| min_size | 最小连接数 | 4 |
| max_size | 最大连接数 | 无限制 |
| timeout | 获取连接超时（秒） | 30 |
| max_lifetime | 连接最大存活时间（秒） | 3600 |
| max_idle | 空闲连接回收时间（秒） | 600 |
| max_waiting | 最大排队请求数 | 0（无限制） |
| check | 取连接时健康检查回调（3.2+） | None |
连接池使用示例：
from psycopg_pool import ConnectionPool
pool = ConnectionPool(
conninfo="host=localhost dbname=mydb",
min_size=2, max_size=10, timeout=30
)
with pool.connection() as conn:
conn.execute('SELECT ...')
# 退出上下文自动归还连接到池中3.7 COPY 与二进制通信
psycopg3 的 COPY 接口被重新设计，统一为 Cursor.copy() 方法：
with cur.copy('COPY mytable (col1, col2) FROM STDIN') as copy:
for record in data_source:
copy.write_row(record)同时支持异步 COPY 和块式读写。COPY 对象返回可读写的类文件对象，在异步上下文中同样适用。
psycopg3 还支持二进制格式的数据传输，避免文本序列化/反序列化开销，尤其适合数值计算和科学数据场景。
3.8 类型注解与静态检查
psycopg3 的纯 Python 实现使其天然支持静态类型注解（Type Hints），与 mypy、Pyright 等工具无缝集成。这是一项对大型项目和团队协作极为重要的工程化改进：
import psycopg
from psycopg.rows import dict_row
def get_users(conn: psycopg.Connection) -> list[dict]:
cur = conn.cursor(row_factory=dict_row)
cur.execute('SELECT id, name FROM users')
return cur.fetchall() # mypy 可以正确推断返回类型四、psycopg2 vs psycopg3 全面对比
4.1 API 差异对照表
| 维度 | psycopg2 | psycopg3 |
|---|---|---|
| 导入包名 | import psycopg2 | import psycopg |
| 连接函数 | psycopg2.connect(dsn) | psycopg.connect(conninfo) |
| 参数绑定 | 客户端绑定（%s 拼接） | 服务端绑定（扩展查询协议） |
| 异步支持 | 不支持 | 原生 asyncio（AsyncConnection） |
| Pipeline 模式 | 不支持 | 支持（conn.pipeline()） |
| 连接池 | 内置简单池（pool 子模块） | 独立 psycopg_pool 包，功能丰富 |
| 预编译语句 | 需手动 PREPARE | 自动缓存（prepared statements） |
| 二进制传输 | 有限支持 | 完整二进制协议支持 |
| COPY 接口 | 多方法（copy_from/copy_to/copy_expert） | 统一 cursor.copy() 接口 |
| with conn: | 管理事务 | 关闭连接 |
| 事务管理 | commit/rollback 直接调用 | conn.transaction() 上下文管理器 |
| 游标工厂 | cursor_factory 参数 | row_factory 参数（更简洁） |
| 存储过程 | cur.callproc() | 已移除，统一用 execute() |
| 类型注解 | 无 | 完整 typing 支持 |
4.2 性能基准对比
根据社区基准测试数据，psycopg3 在多种场景下表现出显著性能优势：
| 测试场景 | psycopg2 | psycopg3 | 提升幅度 |
|---|---|---|---|
| 单条 INSERT（含网络） | 基线 | ≈ 0.95× | 基本持平 |
| 批量 INSERT（1000行） | 基线 | ≈ 1.3× | 提升约 30% |
| 批量 SELECT（10000行） | 基线 | ≈ 1.2× | 提升约 20% |
| 高 RTT 批量操作（Pipeline） | 基线 | ≈ 5-100× | 网络延迟越高提升越明显 |
注意：性能数据受网络延迟、数据量、查询复杂度、硬件配置等多因素影响，实际效果请以自身环境基准测试为准。Pipeline 模式的提升幅度取决于 RTT 与查询执行时间的比例。
4.3 迁移指南
从 psycopg2 迁移到 psycopg3 的关键步骤：
- 更换包依赖：pip install psycopg[binary] 替换 psycopg2-binary
- 更新导入语句：import psycopg 替换 import psycopg2
- 检查 with conn: 语义：psycopg2 中管理事务，psycopg3 中关闭连接——改用 conn.transaction()
- 处理 SQL 占位符兼容性：DDL 中的 %s 改用 psycopg.sql 或 ClientCursor；IN 查询改用 = ANY(%s)
- 替换 cursor_factory 为 row_factory：使用 psycopg.rows 模块
- 移除 callproc() 调用：改用 SELECT func() 或 CALL proc()
- 替换自定义连接池为 psycopg_pool
对于已有的大型项目，建议采用「渐进迁移」策略：先在新模块中试用 psycopg3，对于复杂查询逐步验证兼容性，生产流量切换前完成充分的回归测试
五、实战场景与选型建议
5.1 场景分类推荐
| 场景 | 推荐方案 | 核心理由 |
|---|---|---|
| 传统 Django/Flask 同步应用 | psycopg2 或 psycopg3 | psycopg2 生态最成熟；psycopg3 若已适配也可使用 |
| FastAPI / aiohttp 异步服务 | psycopg3（必需） | 唯一支持原生 asyncio 的方案 |
| 大批量数据导入导出 | psycopg3（Pipeline） | 管线模式可减少数十倍网络延迟 |
| 高并发微服务 | psycopg3 + AsyncConnectionPool | 异步连接池 + 事件循环，吞吐量最高 |
| 数据分析 / ETL 管道 | psycopg3（COPY） | 统一 COPY 接口 + 异步流式处理 |
| 遗留系统维护 | psycopg2 | 稳定性优先，避免迁移风险 |
| 新项目启动 | psycopg3 | 面向未来，功能全面，社区活跃 |
5.2 选型决策树
可按以下决策路径快速判断：
- 项目需要 asyncio 异步支持吗？ → 是 → psycopg3（唯一选择）
- 需要高并发连接管理吗？ → 是 → psycopg3（psycopg_pool 远优于 psycopg2 pool）
- 需要执行大量小查询（如批量写入）吗？ → 是 → psycopg3（Pipeline 大幅提速）
- 是遗留代码库、稳定度要求极高吗？ → 是 → 保持 psycopg2，择机渐进迁移
- 新项目，无历史包袱？ → psycopg3（未来趋势，持续演进）
六、总结与展望
psycopg2 与 psycopg3 代表了同一个项目在不同时代的技术选择。psycopg2 以纯 C 扩展实现了极致的同步性能，凭借多年积累成为 Python + PostgreSQL 的标配。psycopg3 则在继承 DB-API 接口兼容性的基础上，以纯 Python + 可选C加速的新架构，带来了原生异步、Pipeline、新一代连接池、静态类型等现代化能力。
从行业趋势来看：
- 异步编程已经成为 Python Web 开发的主流范式（FastAPI、Starlette 等框架的崛起）
- Django 5.x+ 已原生支持基于 psycopg3 的异步 ORM 操作
- SQLAlchemy 2.0+ 全面支持 psycopg3 作为 PostgreSQL 驱动
- psycopg2 已进入维护模式，活跃开发集中在 psycopg3
综上所述：对于新项目，推荐直接采用 psycopg3，享受其现代化特性带来的开发效率与运行性能双重提升。对于存量项目，建议根据业务节奏制定渐进迁移计划——psycopg3 的设计充分考虑了与 psycopg2 的平滑过渡，95% 以上的日常查询无需修改或仅需少量调整即可运行。
—— END ——
浙公网安备 33010602011771号