用 Python 写后端几乎避不开数据库连接这道坎。如果你的数据库是 PostgreSQL那么 psycopg2 这个名字恐怕每天都在跟你打交道它是 Python 生态里使用最广泛、历史也最悠久的 PostgreSQL 适配器之一SQLAlchemy、Django ORM 这些主流工具在底层都会选择它来做通信。这篇文章我想按实际开发里从安装到上线的顺序把 psycopg2 的安装方式、连接参数、游标、事务模型、参数化查询、批量操作、连接池以及常见报错一次性讲透。内容偏实战如果你正赶工期可以先翻到第 6 章看性能对比或者直接看第 8 章的错误速查表。1. 为什么选择 psycopg2连接 PostgreSQL 的“标准答案”很多人会问现在不是有 asyncpg、pg8000还有新一代的 psycopg3 吗为什么还老提 psycopg2我的看法是psycopg2 在兼容性和生态覆盖上有压倒性优势存量项目和新项目的初期阶段它都是最不容易出错的选择。它可能不是性能最强的但一定是最“稳”的。1.1 psycopg2 到底在扮演什么角色把它理解成“翻译官”最合适。你的 Python 代码发出 SQL 语句psycopg2 负责把它翻译成 PostgreSQL 能理解的协议包发给服务器再把服务器返回的二进制结果翻译回 Python 对象。这个过程涵盖了三块核心工作连接管理建连、保活、断连、数据转换把 Python 的 datetime、Decimal、UUID 等类型安全映射成 PostgreSQL 类型、执行管理把 SQL 和参数打包发送并处理事务提交与回滚。它实现了数据库编程领域的 DB-API 2.0 规范。这个规范的含金量在于你熟悉了 psycopg2 的接口风格以后换用其他遵循 DB-API 的驱动上手成本会非常低。很多中间件和框架就是看中这一点才把它作为默认依赖。1.2 和 asyncpg、psycopg3、pg8000 怎么选说实话这几个驱动各有千秋选型主要看项目形态。我整理了一张对比表驱动类型同步/异步适合场景psycopg2C 扩展DB-API 2.0同步为主传统 Web 后端、ORM 底层、存量系统迁移psycopg3C 扩展重构协议层同步 原生异步新项目想要异步能力和官方长期维护asyncpg纯协议实现高性能原生异步高并发、异步框架FastAPI 等深度使用pg8000纯 Python 实现同步/异步无扩展编译环境、教学验证、特殊部署场景具体建议是如果你写同步代码或者用 SQLAlchemy/Django 这类 ORM那直接用 psycopg2省心如果你从零开发一个以 async/await 为主的新服务追求极致并发那 asyncpg 更合适其实 psycopg3 现在已经很成熟新项目也可以直接考虑它。psycopg2 的价值不在于“最新”而在于“最不缺文档、最少暗坑”。经常有人问 PostgreSQL 和 MySQL 怎么选这个问题和驱动无关。但只要数据库选定了 PG驱动层面的默认答案就非常明确——老项目用 psycopg2新项目看要不要异步。1.3 binary 包与源码包先想清楚再安装这是新手最容易踩的坑。pip install psycopg2会尝试从源码编译编译过程需要系统里有pg_config、Python 开发头文件和完整的 C 编译工具链。很多人的机器上并没有这些于是安装时刷出一大屏红色报错然后就开始怀疑人生。官方为了缓解这个问题提供了预编译好的 wheel 包pip install psycopg2-binary这个包把所需的运行时库一起打进 Wheel 里装完直接用不需要本地有 PostgreSQL 开发环境。我的开发机基本都是这么装的省事到离谱。但如果要上生产环境我会更倾向于用系统包管理器安装或者从源码编译这样能让 psycopg2 链接到与线上 PostgreSQL 一致的 libpq 版本避免一些二进制兼容层面的奇怪问题。提示psycopg2-binary 官方定位是方便开发与快速测试生产环境如果对性能和稳定性要求高建议关注 libpq 版本匹配问题。2. 安装与前置准备先把环境跑通这一章不是废话铺垫。我见过太多人安装失败最后发现是 Python 版本、PostgreSQL 服务、虚拟环境这三件事互相打架。按下面的顺序捋一遍基本不会再出幺蛾子。2.1 Python 与 PostgreSQL 版本怎么选psycopg2 2.9.x 系列对 Python 3.8 以上版本支持得都很好所以只要不是还在用 Python 3.6 这种老古董直接装最新 2.9.x 就行。如果你的服务器上 Python 版本很旧先考虑升级解释器而不是去装老版本 psycopg2 硬凑。PostgreSQL 版本稍微值得一提。经常有人纠结“下载哪个版本”其实对 psycopg2 来说PostgreSQL 12 到 17 都没任何兼容性问题。选版本的逻辑应该来自业务你需要的扩展生态、社区维护周期、团队熟悉度。我做项目时比较保守新项目现在会直接选 PostgreSQL 16 或 17老项目保持原版本直到有明确升级理由。2.2 三步完成安装Linux/macOS/Windows先说最推荐的 Python 环境管理方案为了不污染系统 Python无论什么平台都先建虚拟环境。下面以 Linux 为例sudo apt install libpq-dev python3-dev # Debian/Ubuntu 系 python -m venv .venv source .venv/bin/activate pip install psycopg2-binarymacOS 用户如果要用 Homebrew 装 PostgreSQL 本体则是brew install postgresql16然后用brew services start postgresql16启动服务。如果只是想跑通 Python 侧代码不装数据库本体也完全没问题用pip install psycopg2-binary那一步即可连接远程数据库一样能用。Windows 用户更简单Python 装好后直接python -m venv .venv .venv\Scripts\activate pip install psycopg2-binaryWindows 上最容易出问题的是 PostgreSQL 服务启动失败。装完 PG 后去“服务”里找到postgresql-x64-16确认启动类型是“自动”手动点一下“启动”。如果启动时报端口占用或权限问题多半是postgresql.conf里的端口或数据目录权限配置有问题。2.3 写个最小脚本验证连通环境配没配好一条 SQL 就能验出来。新建一个test_conn.py内容如下import psycopg2 conn psycopg2.connect( host127.0.0.1, port5432, dbnamepostgres, userpostgres, password你的密码 ) cur conn.cursor() cur.execute(SELECT version();) print(cur.fetchone()) cur.close() conn.close()如果输出一条以PostgreSQL开头的结果环境就算通了。如果报错不要慌记住这条报错原文后面第 8 章有一张速查表专门应对这些问题。3. 连接与游标把最核心的两个对象搞明白连接和游标是 psycopg2 里最常碰面的两个对象。很多人会用但并不知道它们内部的行为边界结果一到复杂场景就翻车。3.1 connect() 参数太多记住这几个psycopg2.connect()是从数据库角度看排面最大的一个入口参数十几个起步。实际项目里真正需要经常调整的就这么几个参数作用备注host数据库地址本地填 127.0.0.1远程填内网/公网 IPport端口默认 5432dbname数据库名不是用户名user / password认证信息生产建议用单独业务账号connect_timeout连接超时秒数不设可能让程序长时间卡住sslmode是否加密生产环境建议require或verify-fullapplication_name会话名设置后 DBA 看 pg_stat_activity 能认出你一个带齐关键参数的真实示例conn psycopg2.connect( host127.0.0.1, port5432, dbnameapp_db, userapp_user, passwordsecret, connect_timeout5, sslmoderequire, application_nameorder_worker_01, keepalives1 )connect_timeout非常重要。如果不设置网络异常时操作系统默认 TCP 超时可能长达几分钟你的进程会像死机一样挂在那里。keepalives1则能让操作系统定期发送探测包及时感知链路断裂。3.2 游标与 with 语句别被“自动”两个字骗了游标负责真正执行 SQL 并读取结果。最推荐的写法是配合with使用with psycopg2.connect(...) as conn: with conn.cursor() as cur: cur.execute(SELECT id, name FROM users WHERE id %s;, (1,)) row cur.fetchone()这里最大的坑隐藏在with conn里。很多人以为with conn:退出时会像文件对象一样自动关闭连接甚至自动提交事务。实际完全不是这样psycopg2 中with conn:正常退出时既不会提交也不会关闭连接只有在块内抛出异常时它才会隐式执行rollback()。所以上面的代码并没有提交任何事务尤其是做了INSERT、UPDATE时数据可能无声无息地丢失。正确做法是显式提交with psycopg2.connect(...) as conn: with conn.cursor() as cur: cur.execute(INSERT INTO users(name) VALUES (%s);, (Alice,)) conn.commit()提示psycopg2 在 2.5 版本前后调整过连接上下文管理器的行为把提交动作从连接里移了出去。如果你在网上看到老教程说 with conn 会自动提交别信实测一下就知道了。3.3 连接出错的排查思路连接阶段最常见的两个错误connection refused和password authentication failed。前者说明 TCP 层就没通先检查 PostgreSQL 服务是不是真的起来了再确认端口对不对、host 是否解析正确。后者说明服务在监听但认证没过排查重心要转移到用户名密码和pg_hba.conf的认证策略上。还有一种隐蔽情况服务器在监听 IPv6 的::1而你连接的是 IPv4 的127.0.0.1同样会拒绝。遇到连接问题先psql用同样参数连一次如果 psql 能连而你的脚本不能连那问题几乎可以锁定在驱动参数或环境差异上。4. 事务管理与自动提交最容易丢数据的一个环节事务是数据库正确性的根基但 psycopg2 的事务默认行为恰恰最容易让人误判。这里把事务模型彻底讲清楚比背十遍 commit 语法都管用。4.1 psycopg2 的事务默认是怎么开的psycopg2 默认autocommitFalse。在这个模式下第一条 SQL 执行之前psycopg2 会隐式发送BEGIN开启一个事务。从这一刻起之后所有 SQL 都处于同一个事务中直到你调用commit()或rollback()才结束。如果你一直没提交直接关闭连接PostgreSQL 会直接回滚这个事务。这意味着一个非常常见的丢数据场景程序执行完 INSERT 后没 commit然后连接被释放或程序退出你以为写进去了实际什么都没发生。4.2 commit、rollback、autocommit 怎么配合最稳妥的写法是这样的模板try: with conn.cursor() as cur: cur.execute(UPDATE accounts SET balance balance - %s WHERE id %s;, (100, 1)) cur.execute(UPDATE accounts SET balance balance %s WHERE id %s;, (100, 2)) conn.commit() except psycopg2.Error: conn.rollback() raise两笔更新要么都成功要么都回滚这就是事务的原子性。注意我把commit()放在with conn.cursor()块外面因为游标退出只释放游标资源不负责提交。如果连接的autocommitTrue则每条 SQL 执行完都会立刻提交不需要手动 commit。这种模式适合执行一连串独立的 DDL 语句或者明确知道每条操作不需要原子性的场景。代价是失去了跨语句的原子性一旦中途出错前面已执行的语句无法回滚。4.3 事务“休克”后如何恢复几乎每个用 PG 的人都会遇到这个报错current transaction is aborted, commands ignored until end of transaction block翻译一下事务块里有一条 SQL 报错了PostgreSQL 会把整个事务标记为 aborted 状态此后你在这个事务里再发任何 SQL它都不执行只重复返回这个错误。你必须先rollback()或者执行一次 commit 来结束事务块才能恢复连接。写代码时一定要处理这种情况。不处理的话报错后的重试逻辑会连环失败连错误都被原错误盖住。标准做法就是上面那个 try/except 模板出错先回滚再决定下一步。另外建议把“长事物”当作头号敌人。一个连接一旦开启事务它持有的行锁、表锁都会一直保留到 commit 或 rollback。如果事务里混入了耗时的 Python 逻辑数据库锁持有时间会远超想象。原则是事务越短越好和数据库无关的计算尽量放事务外。5. 参数化查询与防注入写 SQL 的底线这章没有捷径可走。直接拼接 SQL 字符串十有八九会出事轻则报错重则数据被删。5.1 用字符串拼接 SQL 的代价假设你写下这样一行cur.execute(fSELECT * FROM users WHERE name {name})当name的取值是admin --时整条 SQL 就变成了SELECT * FROM users WHERE name admin ----把后面的内容全部注释掉查询逻辑被彻底改写。更极端的还有; DROP TABLE users; --后果不用多说。任何外部输入只要有机会进入 SQL 文本注入风险就不可控。5.2 参数化查询的正确姿势psycopg2 的解决方案是占位符%scur.execute( INSERT INTO users(name, age, email) VALUES (%s, %s, %s), (name, age, email) )psycopg2 会将参数安全转义后作为字面量传给 PostgreSQL单引号、反斜杠这些特殊字符都由驱动处理从源头杜绝注入。注意几点占位符个数必须和参数个数完全一致多一个少一个都会报错。%s只能替代“值”不能替代表名、列名、排序方向这些 SQL 结构。这些结构如果必须动态拼接请自己维护一份白名单。参数传None时数据库会收到NULL。参数类型是 Python 类型不是已经格式好的字符串。5.3 用 mogrify 调试别只靠猜有时候 SQL 报错但看不出来哪里不对可以用mogrify()看驱动实际拼出的 SQLquery cur.mogrify( SELECT * FROM users WHERE name %s AND age %s, (Alice, 20) ) print(query)输出类似bSELECT * FROM users WHERE name Alice AND age 20注意它是bytes类型。这个方法非常适合排查参数转义是否正确、SQL 语法是否符合预期。调试完再用cur.execute()执行就好。5.4 LIKE 与百分号一个高频坑%s占位符和 LIKE 的百分号混在一起时容易翻车。如果你想查以abc开头的名字正确写法是把通配符放进参数里cur.execute(SELECT * FROM users WHERE name LIKE %s;, (abc%,))不要写在 SQL 字符串里比如LIKE %s%这种。如果 SQL 里确实需要字面量百分号比如写100%这种格式串记得转义成%%否则 psycopg2 会认为它是占位符然后报出经典错误TypeError: not all arguments converted during string formatting6. 批量操作与大数据读取从“能跑”到“跑得快”业务量上来之后数据库操作的性能差别会非常明显。同样是插入五万行数据写法不同耗能能差一个数量级。6.1 executemany 不是大批量插入的救星executemany是 DB-API 自带的批量接口但它内部本质上是循环执行单条语句。五万条数据就要和数据库通信五万次网络往返时间全被吃掉了。在本地环境还好一旦数据库在远程服务器上延迟会成倍放大性能问题。所以千万别指望它。把它当成“少量数据、方便写法”还凑合真要大批量写入下面两种方式才是正解。6.2 execute_batch 和 execute_values性能提升的两个利器两个函数都在psycopg2.extras里。execute_batch会把多条语句合并成一条复合 SQL分批发给服务器执行从而减少网络往返from psycopg2.extras import execute_batch, execute_values data [(Alice, 20), (Bob, 25), (Carol, 30)] # execute_batch 按批发送 execute_batch( cur, INSERT INTO users(name, age) VALUES (%s, %s), data, page_size500 )execute_values更进一步直接把参数拼成多条VALUES列表一次性执行性能最高execute_values( cur, INSERT INTO users(name, age) VALUES %s, data, page_size1000 )注意execute_values的 SQL 里VALUES后面只有一个%s驱动会把它替换成(..),(..),(..)的结构。page_size可以限制一批最多多少行防止单条 SQL 太大。实际项目里我会更倾向于用execute_values在数据量几十万行时仍然表现很好。6.3 读取大量数据fetchall 会撑爆内存fetchall()会把所有结果一次性加载到客户端内存。当查询返回几十万、几百万行时内存很容易被打满。最直接的替代方案是fetchmany()分块处理cur.execute(SELECT * FROM logs WHERE created_at %s;, (since,)) while True: rows cur.fetchmany(size5000) if not rows: break for row in rows: process(row)如果数据量极大比如全表扫描分析还可以用“服务端游标”。指定游标名字后数据会留在 PostgreSQL 服务器端客户端只取需要的部分cur conn.cursor(namebig_data_cursor) cur.execute(SELECT * FROM huge_table;) while True: rows cur.fetchmany(size10000) if not rows: break process(rows)用服务端游标要记住两个限制一是游标未关闭时同一连接不适合再执行其他 SELECT 语句因为会话被“取不完的游标”占用二是用完记得cur.close()否则连接回到连接池时会残留一个半开游标影响后续使用。7. 连接池与性能保障别让数据库连接成为瓶颈在 Web 服务里每来一个请求就新建一次数据库连接付出的代价远超想象。连接池是解决这个问题的常规武器。7.1 为什么要连接池PostgreSQL 的架构里每个连接都会在服务器端对应一个后端进程。建连过程包括 TCP 握手、认证、进程 fork、初始化会话这些开销在并发高了以后非常可观。连接池把建连成本摊薄到多次请求上相当于把“每次打电话都重新拨号”变成“电话一直保持在线”。psycopg2 自带psycopg2.pool虽然功能比专业中间件简单但对大多数中小项目完全够用。7.2 ThreadedConnectionPool 的用法与参数多线程环境下推荐用ThreadedConnectionPool。初始化时指定连接池大小和数据库连接参数from psycopg2.pool import ThreadedConnectionPool, PoolError pool ThreadedConnectionPool( minconn5, maxconn20, host127.0.0.1, port5432, dbnameapp_db, userapp_user, passwordsecret )使用时严格遵循“取连接-使用-归还”的流程def run_query(sql, params): conn pool.getconn() try: with conn.cursor() as cur: cur.execute(sql, params) return cur.fetchall() finally: pool.putconn(conn)putconn(conn)必须放在finally里。如果忘了归还连接会被一直占用连接池很快耗尽后续请求全部卡死。minconn是池启动时预先建立的连接数maxconn是上限。参考经验是单机服务minconn5, maxconn20起步高并发时先把maxconn往上调但同时也得确认 PostgreSQL 的max_connections配置能覆盖。7.3 连接池里的“僵尸连接”怎么办连接池不是万能的它管理的连接可能因为网络闪断、服务器重启等原因已经失效。psycopg2 自带的池比较朴素不会自动识别这种“僵尸连接”。当你从池里拿到一个假连接时第一次execute就会抛OperationalError。我的处理习惯是封装一个取连接的健康检查def get_healthy_conn(): while True: conn pool.getconn() try: with conn.cursor() as cur: cur.execute(SELECT 1;) return conn except psycopg2.OperationalError: pool.putconn(conn, closeTrue) continue注意putconn(conn, closeTrue)会告诉池“这个连接坏了帮我销毁而不是回收”。这种检查会多一些开销但换来的稳定性非常值得。8. 常见报错与排查实录一份可以直接收藏的速查表拦截所有干货的最后一步是对着真实错误做排查。下面几张表和两个案例基本覆盖了我这几年在项目里碰到的高频问题。8.1 报错速查表按错误原文快速定位错误信息典型片段常见原因处理建议connection refused服务没启动/端口不对/网络不通用 psql 或telnet 127.0.0.1 5432验证确认监听地址password authentication failed用户名密码错误或 pg_hba.conf 认证方式不匹配检查用户密码再看 pg_hba 认证规则column xx does not existPostgreSQL 对不加引号的标识符一律视为小写建表字段命名统一小写避免大小写混用relation xx does not exist表真的不存在或 search_path 未包含目标 schema确认 schema或写schema.tablecurrent transaction is aborted事务内一条语句出错后未回滚先rollback()再继续执行别的 SQLtype xx does not exist使用了数据库扩展类型但扩展未创建提前CREATE EXTENSION如 hstore、uuidnot all arguments converted during string formattingSQL 里的%没转义或参数个数不匹配字面量%写作%%检查参数个数ModuleNotFoundError: No module named psycopg2虚拟环境未激活或装了 binary 又被卸载激活环境后pip install psycopg2-binaryconnection already closed池里拿到过期连接或手动 close 后继续使用做健康检查重新获取连接8.2 两个真实的线上案例第一个案例是批量入库卡死。每天的定时任务会往一张大表里写入几百 MB 数据偶尔卡到任务超时。现场排查时发现连接池的 20 个连接全部被占用而且pg_stat_activity里能看到一条长事务一直开着持有大量锁。后续的写操作全部堵在锁等待上把连接池一点一点耗尽。解决方法是把入库任务拆成合理大小的批次缩短单次事务的持有时间同时给写入 session 设置了statement_timeout避免个别语句无限期等待。第二个案例是连接池拿到“死连接”。PostgreSQL 实例重启过一次连接池里的旧连接没有及时淘汰导致线上部分请求偶发报错。当时排查到问题后加上了前面说的SELECT 1健康检查发现连接不可用时主动关闭并从池中剔除之后的报错率直接归零。这个经验非常值得写进生产代码。8.3 我是怎么设计一套数据库访问层的说点个人体会。我用了这么多年的 psycopg2最重要的经验是把所有数据库访问收口到一个统一的数据访问模块里。这个模块对外暴露的接口就几个query、execute、batch_execute内部统一处理连接池获取、健康检查、提交回滚、异常重试。对外界来说调用方根本不需要知道 psycopg2 的细节。具体实现时我会把事务边界和业务逻辑严格分开。业务层只负责组织 SQL 和参数访问层负责管理连接与事务生命周期。多年下来这个模式帮我挡掉了大量低级错误忘记 commit、忘记归还连接、事务内部重试导致状态混乱。这也是我能给同行的最实在的一条建议——分层虽然听起来玄但它真的能救命。