前言Python 连 PostgreSQL 有两条官方路线而且它们的名字容易让人搞混psycopg 2包名psycopg2导入名psycopg2。这是用了很多年的经典版本目前处于维护状态。psycopg 3包名psycopg导入名也就是psycopg——不是psycopg3。这是当前推荐的版本支持同步和异步两套接口。所以「新项目用哪个」的答案很明确用 psycopg 3pip install psycopg。老项目继续用 psycopg2 也没问题但新代码不要以它为起点。第二个要澄清的PostgreSQL 不是「更强的 MySQL」这种线性关系。它们都是客户端-服务器关系型数据库都实现 DB-API但 SQL 方言、类型系统、并发实现各有各的路子。把 MySQL 的代码改成 PostgreSQL 能跑靠的不是「换个驱动」而是逐条核对方言——比如AUTO_INCREMENT要改成SERIAL或GENERATED AS IDENTITY反引号要换成双引号。本文按「选路线 → 连接 → 执行 → 拿返回值 → 事务 → 行工厂与连接池」的顺序讲完整流程并在关键处给出两个版本的写法对照。所有 SQL 都参数化。一、两条路线的写法对照对比项psycopg 2psycopg 3安装pip install psycopg2或psycopg2-binarypip install psycopg导入import psycopg2import psycopg连接psycopg2.connect(dsn)psycopg.connect(conninfo)占位符%s%s行工厂cursor_factoryRealDictCursorrow_factorydict_row上下文管理器with conn:只提交/回滚不关闭连接with psycopg.connect(...) as conn:提交/回滚并关闭连接异步需要额外库如 psycopg2 的异步部分内置AsyncConnection/AsyncCursor最后两行是两版之间最容易踩的差异下面会专门说。二、连接PostgreSQL 的连接参数遵循 libpq 的约定数据库名是dbname不是database。这点和 MySQL 驱动相反照抄 MySQL 代码会直接报错。# 适用于 Python 3.8psycopg 2import osimport psycopg2conn psycopg2.connect(hostos.environ.get(PGHOST, 127.0.0.1),portint(os.environ.get(PGPORT, 5432)),dbnameos.environ[PGDATABASE],useros.environ[PGUSER],passwordos.environ[PGPASSWORD],connect_timeout5,)print(服务端版本:, conn.server_version)conn.close()# 适用于 Python 3.8psycopg 3import osimport psycopgconn psycopg.connect(hostos.environ.get(PGHOST, 127.0.0.1),portint(os.environ.get(PGPORT, 5432)),dbnameos.environ[PGDATABASE],useros.environ[PGUSER],passwordos.environ[PGPASSWORD],connect_timeout5,)conn.close()也可以直接传连接串# 适用于 Python 3.8psycopg 2 用 dsnpsycopg 3 用同名的位置参数conn psycopg2.connect(host127.0.0.1 port5432 dbnamedemo userapp passwordsecret)要点connect_timeout一定写上。不写的话网络不通时会长时间挂起表现像「程序卡死」。密码从环境变量读不要硬编码。PostgreSQL 客户端本身就认PGHOST/PGPORT/PGDATABASE/PGUSER/PGPASSWORD这组环境变量用标准名还能让命令行工具共用同一份配置。默认端口 5432但按实际部署填。三、执行 SQLCRUD 与参数化PostgreSQL 的两个驱动都用%s作占位符参数传序列。# 适用于 Python 3.8psycopg 2import psycopg2conn psycopg2.connect(dbnamedemo userapp passwordsecret host127.0.0.1)try:with conn.cursor() as cur:cur.execute(CREATE TABLE IF NOT EXISTS book (id SERIAL PRIMARY KEY,title VARCHAR(200) NOT NULL,author VARCHAR(100) NOT NULL,price NUMERIC(10,2) NOT NULL))# 增cur.execute(INSERT INTO book (title, author, price) VALUES (%s, %s, %s),(三体, 刘慈欣, 23.00),)# 批量增cur.executemany(INSERT INTO book (title, author, price) VALUES (%s, %s, %s),[(活着, 余华, 28.50), (许三观卖血记, 余华, 26.00)],)# 查cur.execute(SELECT id, title, price FROM book WHERE author %s ORDER BY price DESC,(余华,),)for row in cur:print(row)# 改cur.execute(UPDATE book SET price %s WHERE title %s,(25.00, 三体),)print(更新行数 , cur.rowcount)# 删cur.execute(DELETE FROM book WHERE title %s, (三体,))conn.commit()except Exception:conn.rollback()raisefinally:conn.close()字段和类型上的方言差异MySQL 写法PostgreSQL 写法说明INT AUTO_INCREMENTSERIAL或INT GENERATED BY DEFAULT AS IDENTITY后者是 SQL 标准写法反引号包标识符双引号包标识符双引号只在需要保留大小写或含特殊字符时才用DECIMAL(10,2)NUMERIC(10,2)PostgreSQL 两者都认NUMERIC更常用TINYINTSMALLINTPostgreSQL 没有TINYINTDATETIMETIMESTAMP/TIMESTAMPTZ建议用TIMESTAMPTZ带时区四、拿回新插入的主键RETURNINGPostgreSQL 支持RETURNING子句插入、更新、删除都能直接返回被影响的行不用再查一次。# 适用于 Python 3.8psycopg 2with conn.cursor() as cur:cur.execute(INSERT INTO book (title, author, price) VALUES (%s, %s, %s) RETURNING id,(额尔古纳河右岸, 迟子建, 32.00),)new_id cur.fetchone()[0]print(新记录 id , new_id)这是 PostgreSQL 相比 MySQL 的一个实用优势——MySQL 没有RETURNING只能靠自增主键的LAST_INSERT_ID()在 Python 驱动里通常表现为cursor.lastrowid而且批量插入后那个值不可靠。RETURNING在UPDATE和DELETE上同样能用比如DELETE ... RETURNING id可以一次拿到被删掉的所有 id。五、事务两个版本的关键差异DB-API 要求自动提交默认关闭两个版本都遵守。区别在上下文管理器的语义psycopg 2with conn:只处理事务不关闭连接。# 适用于 Python 3.8psycopg 2try:with conn: # 正常退出 commit异常退出 rollbackwith conn.cursor() as cur:cur.execute(UPDATE wallet SET balance balance - %s WHERE id %s, (30, 1))cur.execute(UPDATE wallet SET balance balance %s WHERE id %s, (30, 2))finally:conn.close() # 关闭连接要自己来psycopg 3with psycopg.connect(...) as conn:在退出时提交/回滚并且关闭连接。# 适用于 Python 3.8psycopg 3with psycopg.connect(dbnamedemo userapp passwordsecret) as conn:with conn.cursor() as cur:cur.execute(UPDATE wallet SET balance balance - %s WHERE id %s, (30, 1))cur.execute(UPDATE wallet SET balance balance %s WHERE id %s, (30, 2))# 到达这里事务已提交或异常时已回滚连接已关闭把 psycopg2 的代码直接搬到 psycopg 3如果连接是在with外面建的、之后还要复用行为就会变。迁移时这一条要专门核对。需要自动提交时比如执行CREATE DATABASE这类不能放在事务里的语句两个版本都用autocommit属性而且要在开启任何事务之前设置# 适用于 Python 3.8conn.autocommit True # psycopg2 和 psycopg 3 都有这个属性六、行工厂与连接池让每行变成字典psycopg 2用cursor_factory# 适用于 Python 3.8from psycopg2.extras import RealDictCursorcur conn.cursor(cursor_factoryRealDictCursor)cur.execute(SELECT id, title FROM book WHERE author %s, (余华,))for row in cur:print(row[title]) # 按列名取值psycopg2.extras里还有DictCursor行是支持按名取值的序列和NamedTupleCursor行是命名元组。要「每行一个真正的 dict」就用RealDictCursor——名字里那个Real就是在区分这一点。psycopg 3用row_factory# 适用于 Python 3.8psycopg 3from psycopg.rows import dict_rowwith psycopg.connect(dbnamedemo userapp passwordsecret, row_factorydict_row) as conn:with conn.cursor() as cur:cur.execute(SELECT id, title FROM book WHERE author %s, (余华,))for row in cur:print(row[title])psycopg.rows提供tuple_row默认、dict_row、namedtuple_row、class_row(Cls)、scalar_row。连接池psycopg 2有内置的纯 Python 池# 适用于 Python 3.8psycopg 2from psycopg2.pool import ThreadedConnectionPoolpool ThreadedConnectionPool(minconn1, maxconn10,dbnamedemo, userapp, passwordsecret, host127.0.0.1,)conn pool.getconn()try:with conn.cursor() as cur:cur.execute(SELECT 1)conn.commit()finally:pool.putconn(conn) # 归还不是 close# 退出时pool.closeall()注意putconn()是归还不是关闭。直接对池里的连接调close()池会以为这个连接还能用却实际已废后续getconn()拿到的就是坏连接。psycopg 3的连接池在单独的分发包psycopg_pool里需要额外安装。用法思路相同从池里取、用完归还。具体 API 以该包官方文档为准。七、动态标识符怎么处理值走%s占位符就够了但表名、列名这类标识符不能参数化。PostgreSQL 官方驱动为此提供了一个安全拼接的模块psycopg 2 里是psycopg2.sqlpsycopg 3 里是psycopg.sql思路是「用SQL对象拼装Identifier负责标识符的引号与转义」# 适用于 Python 3.8psycopg 2from psycopg2 import sqlquery sql.SQL(SELECT {fields} FROM {table} WHERE {pkey} %s).format(fieldssql.SQL(,).join([sql.Identifier(id), sql.Identifier(title)]),tablesql.Identifier(book),pkeysql.Identifier(id),)cur.execute(query, (1,))但更稳妥的做法仍然是白名单如果列名来自外部输入先判断它是否在允许的集合里不在就直接拒绝。sql模块解决的是「引号怎么写对」解决不了「这个列名该不该允许」。常见坑点1. 用database传库名❌psycopg2.connect(host127.0.0.1, databasedemo, userapp)——PostgreSQL 侧走的参数名是dbname。✅psycopg2.connect(host127.0.0.1, dbnamedemo, userapp)。2. 以为 psycopg2 的with conn:会关连接❌with conn: ...之后就不再close()连接一直占着。✅ psycopg2 的with只管事务该close()还得close()。3. 按 psycopg2 的习惯写 psycopg 3❌ 在 psycopg 3 里with psycopg.connect(...) as conn:之后还把conn拿去给别的函数复用——块结束时它已经被关掉了。✅ 记住 psycopg 3 的连接上下文管理器「提交/回滚并关闭」要复用连接就显式管理生命周期。4. 字符串拼接构造 SQL❌cur.execute(fSELECT * FROM book WHERE author {author})——作者名带单引号就能改变语句结构。✅cur.execute(SELECT * FROM book WHERE author %s, (author,))。5. 单元素参数元组漏逗号❌cur.execute(... WHERE id %s, (book_id))——这不是元组会报参数绑定错误。✅cur.execute(... WHERE id %s, (book_id,))。6. 更新后用rowcount判断值有没有变❌ 只把价格设成和原来相同的值rowcount是 0程序误判「记录不存在」。✅rowcount表示受影响的行数判断存在性用一次SELECT或者干脆用UPDATE ... RETURNING id。7. 用完连接池的连接直接close()❌pool.getconn()之后conn.close()——池里记录的仍是这个已关闭的连接后续取出来就报错。✅ 用pool.putconn(conn)归还只在关闭整个池时用pool.closeall()。8. 建库语句放在事务里执行❌CREATE DATABASE在默认的非自动提交模式下执行报「不能在事务块中运行」之类的错误。✅ 在开启事务之前设conn.autocommit TrueCREATE DATABASE、VACUUM这类语句本身就不能在事务里跑。总结环节关键结论选型新项目用 psycopg 3import psycopg老项目可继续用psycopg2连接参数名是dbname/user/password/port设connect_timeout凭据外置执行占位符%s参数传序列标识符不能参数化用白名单或sql模块返回PostgreSQL 支持RETURNING插入/更新/删除都能直接返回受影响行事务默认手动提交psycopg2 的with conn:不关连接psycopg 3 的会关行工厂psycopg2 用cursor_factoryRealDictCursorpsycopg 3 用row_factorydict_row连接池psycopg2 内置ThreadedConnectionPoolpsycopg 3 用psycopg_pool方言SERIAL/IDENTITY、双引号标识符、NUMERIC、TIMESTAMPTZ连接 PostgreSQL 的流程本身很标准装驱动、填dbname那一组参数、参数化执行、显式提交。容易出事的地方集中在版本迁移两个 psycopg 的上下文管理器语义不同和方言差异从 MySQL 搬过来的表结构和 SQL。开工前把这两张表核对一遍比事后排查省力得多。
