深入理解 Python 中的 psycopg2：PostgreSQL 的强大适配器

什么是 psycopg2？

psycopg2 是一个用于 Python 的 PostgreSQL 数据库适配器（adapter），它实现了 Python 的 DB-API 2.0 规范（PEP 249），允许 Python 程序连接、查询和操作 PostgreSQL 数据库。

💡 简单来说：psycopg2 就是让 Python 能“听懂”PostgreSQL 并与其通信的桥梁。

主要特性：

• 支持事务处理（commit / rollback）
• 自动类型转换（如 Python 的 datetime ↔ PostgreSQL 的 timestamp）
• 支持预编译语句（防止 SQL 注入）
• 可扩展性高，支持异步模式（通过 psycopg2.extras 或 psycopg3）
• 与 SQLAlchemy、Django、Flask-SQLAlchemy 等框架无缝集成

安装 psycopg2

你可以使用 pip 安装 psycopg2。根据你的需求，有两种常见方式：

方式一：安装 psycopg2-binary（推荐初学者）

pip install psycopg2-binary

✅ 优点：

• 不需要本地编译，安装简单快捷
• 包含所有依赖项，适合开发和测试环境

⚠️ 注意：

• 不建议用于生产环境（官方不推荐长期部署使用）

方式二：安装源码版 psycopg2（推荐生产环境）

pip install psycopg2

✅ 优点：

• 性能更优，可定制性强
• 更适合生产部署

⚠️ 要求：

• 需要系统已安装 PostgreSQL 开发库（如 libpq-dev on Ubuntu）
• 编译时需要 C 编译器和 PostgreSQL 头文件

例如，在 Ubuntu 上先运行：

sudo apt-get install libpq-dev python3-dev
pip install psycopg2

基本使用示例

下面是一个完整的例子，展示如何使用 psycopg2 连接 PostgreSQL 数据库并执行增删改查操作。

步骤 1：导入模块并建立连接

import psycopg2
from psycopg2 import sql

# 连接数据库
conn = psycopg2.connect(
host="localhost",
database="mydb",
user="postgres",
password="yourpassword",
port=5432
)

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

📌 提示：不要把密码硬编码在代码中！建议使用环境变量或配置文件管理敏感信息。

步骤 2：创建表

cur.execute('''
CREATE TABLE IF NOT EXISTS users (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
age INTEGER,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
''')

# 提交事务
conn.commit()

步骤 3：插入数据（INSERT）

单条插入：

cur.execute(
"INSERT INTO users (name, email, age) VALUES (%s, %s, %s)",
("张三", "zhangsan@example.com", 28)
)
conn.commit()

批量插入：

users_data = [
("李四", "lisi@example.com", 32),
("王五", "wangwu@example.com", 25),
("赵六", "zhaoliu@example.com", 30),
]

cur.executemany(
"INSERT INTO users (name, email, age) VALUES (%s, %s, %s)",
users_data
)
conn.commit()

✅ 使用占位符 %s 而非字符串拼接，可以有效防止 SQL 注入攻击！

步骤 4：查询数据（SELECT）

cur.execute("SELECT * FROM users WHERE age > %s", (25,))
rows = cur.fetchall()

for row in rows:
print(row)
# 输出示例: (1, '张三', 'zhangsan@example.com', 28, datetime.datetime(…))

其他获取结果的方法：

• fetchone()：返回一条记录
• fetchmany(n)：返回最多 n 条记录
• fetchall()：返回所有结果（注意内存消耗）

步骤 5：更新与删除

# 更新
cur.execute("UPDATE users SET age = %s WHERE name = %s", (30, "张三"))
conn.commit()

# 删除
cur.execute("DELETE FROM users WHERE name = %s", ("赵六",))
conn.commit()

步骤 6：关闭连接

cur.close()
conn.close()

⚠️ 务必记得关闭游标和连接，否则可能导致连接泄漏。

高级用法与最佳实践

1. 使用上下文管理器自动释放资源

import psycopg2
from contextlib import contextmanager

@contextmanager
def get_db_connection():
conn = psycopg2.connect(
host="localhost",
database="mydb",
user="postgres",
password="yourpassword"
)
try:
yield conn
except Exception:
conn.rollback()
raise
finally:
conn.close()

# 使用方式
with get_db_connection() as conn:
with conn.cursor() as cur:
cur.execute("SELECT COUNT(*) FROM users")
count = cur.fetchone()[0]
print(f"共有 {count} 名用户")

这样可以确保异常时回滚事务，并始终关闭连接。

2. 动态构建 SQL 查询（避免拼接）

使用 psycopg2.sql 模块安全地构造动态 SQL：

from psycopg2 import sql

table_name = "users"
field_name = "name"

# 安全地插入表名和字段名（这些不能用 %s 参数化）
query = sql.SQL("SELECT * FROM {} WHERE {} = %s").format(
sql.Identifier(table_name),
sql.Identifier(field_name)
)

cur.execute(query, ("张三",))
result = cur.fetchall()

❗ 注意：普通参数只能用于值，不能用于表名、列名等结构部分。必须使用 sql.Identifier 来防止注入。

3. 类型映射（自动转换）

psycopg2 支持丰富的类型映射，例如：

例如查询 JSON 字段：

cur.execute("SELECT metadata FROM users WHERE id = 1")
meta = cur.fetchone()[0] # 直接得到 Python 字典
print(meta.get("location"))

4. 与 ORM 集成（如 SQLAlchemy）

虽然 psycopg2 是底层驱动，但它常被用作更高层工具的基础。例如：

from sqlalchemy import create_engine

engine = create_engine('postgresql+psycopg2://user:password@localhost/mydb')

此时 SQLAlchemy 使用 psycopg2 作为数据库连接引擎。

常见问题与解决方案

psycopg2 vs psycopg3

🆕 新一代驱动：psycopg3 已发布！

• psycopg2：稳定、成熟、广泛应用
• psycopg3：全新架构，支持异步（async）、更好的类型映射、更简洁 API

如果你正在启动新项目，可以考虑尝试 psycopg3：

pip install psycopg[binary]

但目前大多数项目仍以 psycopg2 为主流选择。

总结

psycopg2 是 Python 连接 PostgreSQL 的事实标准驱动，具有以下优势：

✅ 成熟稳定 ✅ 性能优异 ✅ 安全可靠（支持参数化查询） ✅ 易于集成到各类框架中

推荐学习路径