python psycopg2库 操作postgresql

psycopg2介绍

psycopg2是 Python 编程语言中最流行且广泛使用的 PostgreSQL 数据库适配器（接口）。它遵循 Python DB API 2.0 规范，充当了 Python 程序与 PostgreSQL 数据库之间通信的桥梁，允许开发者在 Python 中轻松连接、查询和操作 PostgreSQL 数据库。

核心特性

• 全面支持 DB API 2.0：完全遵循 Python 数据库接口规范，并支持安全的多线程并发操作，适用于高并发场景。
• 底层协议支持：采用 C 语言实现了对libpqv3 协议的完全封装，支持客户端/服务器端游标、异步通信以及COPY TO/COPY FROM命令。
• 自动类型转换：支持 Python 与 PostgreSQL 之间基本数据类型的自动映射与转换（如字符串、整型、浮点型、布尔型、日期时间型、Unicode 以及数组类型等）。
• 安全性与事务管理：支持参数化查询以防止 SQL 注入攻击；支持 ACID 事务特性，需要显式调用commit()提交变更。

安装方式

通常使用pip进行安装，主要有两种方式：

1. 二进制安装包（psycopg2-binary）：无需本地编译环境即可快速部署，适合初学者和开发测试环境。
2. 源码构建版（psycopg2）：需要系统安装 C 编译器和 PostgreSQL 开发库（如libpq-dev）。虽然配置稍显复杂，但官方建议在生产环境中优先使用此版本以获得更好的性能和定制性。

基本使用示例

使用psycopg2的典型流程包括建立连接、创建游标、执行 SQL 和获取结果：

importpsycopg2# 1. 建立数据库连接conn=psycopg2.connect(host="localhost",database="test",user="postgres",password="postgres",port=5432)# 2. 创建游标对象cursor=conn.cursor()# 3. 执行 SQL 脚本（使用 %s 作为参数占位符，防止 SQL 注入）cursor.execute("SELECT * FROM test WHERE id > %s;",(5,))# 4. 获取查询结果# 常用方法有 fetchone() 返回单条, fetchall() 返回所有, fetchmany(n) 返回指定条数results=cursor.fetchall()forrowinresults:print(row)# 5. 提交事务（在执行 INSERT, UPDATE, DELETE 后必须调用）# conn.commit()# 6. 关闭游标和连接cursor.close()conn.close()

版本演进提示

值得注意的是，psycopg2是旧版本，目前官方已推出了新一代的psycopg（即 psycopg3）。新版本提供了更现代的 API、原生异步编程支持、连接池以及更好的性能优化，且仅支持 Python 3。

• 如果是新项目或需要利用最新特性，建议直接使用psycopg（版本 3）。
• 如果是维护依赖 Python 2 的老旧项目或已经深度使用psycopg2的成熟系统，继续使用psycopg2仍是稳妥的选择。

sql.SQL动态 SQL 语句的安全拼接

1. 代码逐行解释

user_name=['Alice','Bob']cur.execute(sql.SQL("SELECT id, username FROM users_user WHERE username IN ({})").format(sql.SQL(', ').join(map(sql.Literal,user_name))))

这段代码的执行过程如下：

1. map(sql.Literal, user_name)：将user_name列表中的每一个元素（比如['Alice', 'Bob']）包装成sql.Literal对象。Literal表示这是一个字面量（值），在执行时会被安全地转义并加上引号（如'Alice'）。
2. sql.SQL(', ').join(...)：使用sql.SQL对象作为分隔符，将上面转换好的Literal对象拼接起来，中间用逗号分隔。结果类似于：sql.Literal('Alice'), sql.Literal('Bob')。
3. sql.SQL("... IN ({})").format(...)：将拼接好的参数安全地填入 SQL 模板的{}占位符中。
4. cur.execute(...)：将最终生成的安全 SQL 发送给 PostgreSQL 执行。

最终执行的 SQL 大致为：

SELECTid,usernameFROMusers_userWHEREusernameIN('Alice','Bob')

2. 为什么用sql.SQL而不是直接组装字符串？

在 Python 中，我们通常被教导使用%s占位符来防止 SQL 注入，例如：

cur.execute("SELECT * FROM users WHERE id = %s",(user_id,))

但是，%s占位符只能用来替换“值”（如字符串、数字），不能用来替换“SQL 关键字、表名、列名或 IN 子句的列表”。

如果你直接用字符串拼接来构建IN子句，会面临以下致命问题：

❌ 错误示范 1：直接字符串拼接（SQL 注入漏洞）

# 假设 user_name = ["Alice'; DROP TABLE users_user; --"]sql_str="SELECT * FROM users_user WHERE username IN ('"+"', '".join(user_name)+"')"cur.execute(sql_str)

如果用户输入了恶意字符，你的数据库可能会被删库或数据泄露。

❌ 错误示范 2：尝试使用%s替换整个 IN 列表（语法错误）

# 假设 user_name = ['Alice', 'Bob']cur.execute("SELECT * FROM users_user WHERE username IN (%s)",(user_name,))

这会导致报错，因为psycopg2会将整个列表转义成一个单一的字符串，生成的 SQL 变成：
WHERE username IN ('{"Alice", "Bob"}')，这在 PostgreSQL 中是无效的语法。

✅ 正确做法：使用psycopg2.sql模块

psycopg2.sql模块的设计初衷就是为了解决动态 SQL 结构的安全问题：

• sql.SQL()：用于包裹 SQL 关键字、表名、列名、运算符等结构部分。它告诉psycopg2：“这是安全的 SQL 代码，不要对它进行转义”。
• sql.Literal()：用于包裹用户传入的数据值。它会自动处理转义、引号和特殊字符，彻底杜绝 SQL 注入。

总结

使用sql.SQL和sql.Literal的组合，既满足了IN (...)这种动态数量参数的语法需求，又完美保留了psycopg2底层的安全转义机制，是处理动态 SQL 结构（如动态表名、动态列名、动态 IN 列表）的唯一官方推荐做法。

(注：如果你使用的是较新版本的psycopg(v3)，它已经支持直接将列表作为参数传入IN %s，但在psycopg2中，必须使用上述的sql模块方案。)