写 Python 连 MySQL 的代码十有八九都逃不过这几行import pymysql conn pymysql.connect(host127.0.0.1, userroot, passwordxxx, databasetest) cursor conn.cursor() cursor.execute(SELECT * FROM user WHERE age 18) rows cursor.fetchall()cursor这个对象你可能已经背得滚瓜烂熟但有没有想过一个问题为什么连接都建立好了还要再创建一个游标才能执行 SQLfetchall、fetchmany、fetchone有什么区别为什么有时候明明只是取一条数据程序内存却突然爆掉这篇文章想从底层机制和实际踩坑两个角度把游标这个最熟悉的陌生人彻底讲清楚。我会以 PyMySQL 为主必要时也会提到 MySQLdbmysqldb的差异因为这两套接口几乎同源但具体实现有细微区别。适合刚学会用 PyMySQL 但总感觉哪里没吃透的读者也适合那些已经写过一段时间业务代码、被大数据量查询或者连接耗尽问题折磨过的朋友。1. 一次 SQL 查询从发出到取回游标在中间扮演什么角色1.1 为什么有了连接还不够还要再拉一个 cursor 出来很多初学者会困惑conn已经是连接到 MySQL 的一条通道了为什么执行 SQL 还要conn.cursor()直接conn.execute()不行吗这要追溯到 Python 数据库 API 的标准——PEP 249也就是 DB-API 2.0。这套规范把数据库交互分成了两个核心对象Connection管连接、事务、提交回滚Cursor管语句执行和结果读取。为什么不把这两件事合并成一个对象实际写业务的时候你就明白了一个连接上的会话状态是互斥的而业务经常需要在一个事务里先查点东西做判断再更新数据再用另一条查询验证结果。如果连接本身又当执行器又当结果集容器那同一连接上并发执行两条 SQL 时结果集就全串味了。游标本质上是一块隔离出来的工作区。你可以把 Connection 想象成一条电话线MySQL 服务端就是客服中心。你拨通电话建立连接告诉客服要查什么SQL然后你不可能让整条电话线一直举着纸等你记笔记——你需要一个专门的小窗口Cursor来接收并逐条翻阅客服递出来的记录。一个连接上可以同时开多个游标各查各的就像同一通电话里你让客服开多个工单每个工单有自己的编号。1.2 execute 执行之后数据到底经过了哪些站我见过太多人以为cursor.execute()一执行完结果集就已经整整齐齐摆在 Python 里了。实际上不是。一条 SELECT 从发出到拿到行数据在底层至少经过这 5 步客户端把 SQL 文本以及参数绑定后的完整语句通过网络发送到 MySQL 服务端。服务端解析 SQL、做语法检查、优化器生成执行计划、执行语句把命中的行组织成一个结果集暂存在 MySQL 内部的结果区。服务端开始向客户端“吐”结果。这一步有个关键分支驱动用的是缓冲模式store result还是非缓冲模式use result。在缓冲模式下C API 的mysql_store_result()会把服务端传来的所有行一次性缓存在客户端内存里后再由 Python 侧逐行返回。在非缓冲模式下C API 的mysql_use_result()只初始化结果集的元数据不把行拉完客户端每调用一次读取函数才从连接 socket 里读一行。PyMySQL 的默认 Cursor 走的是懒加载 首次全量缓存的路子执行完 SQL 后并不会立刻把所有行拉到 Python 列表里而是等你第一次调用fetchone或fetchall时才通过内部方法一次性把剩余的所有行读进内存。MySQLdbmysqldb的默认 Cursor 则更激进它会在execute时就全部拉回客户端。但无论哪种普通 Cursor 在第一次触碰结果集这个时间点前后数据就已经整个躺在客户端内存里了。1.3 游标控制的是取数节奏不是取数入口顺着上面的逻辑你会发现一个反直觉的结论普通游标下你调fetchone虽然是一次拿一条但底层早就把几百行一次性搬到本地了fetchone只是在本地缓存里往后挪了一下下标。这叫客户端游标的行为。真正能做到一行一行从服务端拉的是后面的 SSCursor服务器端/流式游标。很多事故就是没搞清这个区别有人辛辛苦苦写了fetchmany(1000)做分页结果内存还是炸原因就是普通 Cursor 的fetchmany只是把已经全量缓存的行切块返回内存根本没省下来。所以理解游标的关键词不是入口而是取数节奏。你用什么姿态去消费结果集决定了这条 SQL 对客户端内存、对连接占用、对数据库服务器造成的压力。2. fetchone / fetchmany / fetchall 的选择远不止取一条这么简单2.1 execute 的返回值、rowcount、lastrowid 这些隐藏属性你多半没用上先纠正一个我见过无数次的误解cursor.execute()的返回值不是结果集。在 PyMySQL 里execute返回的是本次操作受影响的行数int。比如affected cursor.execute(UPDATE user SET status 1 WHERE id %s, (100,)) print(affected) # 被更新的行数这套接口沿袭自 PEP 249execute返回受影响行数或 None由驱动自行决定。一开始就指望cursor.execute()返回查询结果的人后面全踩坑。除了返回值游标对象上还挂着几个非常实用的属性cursor.rowcount上一次 DML 操作影响的行数或者 SELECT 已经读到的行数。它对 UPDATE、DELETE、INSERT 这类语句最有用经常用来判断这次到底改没改到数据。对于 SELECT不同驱动的表现不太一样有些驱动在结果集没取完前rowcount可能是不准确的所以别拿它充当查询结果总数。cursor.lastrowid执行 INSERT 后被插入行的自增 ID。这个属性太实用了省掉了插入后再跑一次SELECT LAST_INSERT_ID()的额外查询。比如cursor.execute(INSERT INTO article (title, content) VALUES (%s, %s), (标题, 正文)) new_id cursor.lastrowidcursor.description结果集的列信息。它是一个元组列表每个元素包含列名、类型、显示宽度等信息。动态导出 CSV 时可以直接从这里拿列名生成表头。2.2 三种取数方式的真实差异直接上对比表这是普通 Cursor同时也是 PyMySQL 默认 Cursor的行为方法返回值内存表现典型场景fetchone()单个元组无数据时返回 None首次调用时底层可能已把剩余结果全部读入本地缓存只判断是否存在、取一条做后续判断fetchmany(size)元组列表size 默认取arraysize在本地缓存上切片返回实际数据可能已全量在内存需要分批处理但结果集不算夸张fetchall()元组列表无数据时返回空列表剩余所有行一次性进入内存结果集小、需要全量参与运算注意上面的内存表现那一列。PyMySQL 普通 Cursor 的典型行为是第一次 fetch 时把结果集剩余行全部拉到内存之后fetchone、fetchmany都只是在这个内存列表上移动游标位置。MySQLdb 的普通 Cursor 更直接execute阶段就已全量缓冲。所以这里有一个非常重要的实操结论用一个普通 Cursor fetchmany(1000)来处理 100 万行结果省不了多少内存。真正想省内存必须换 SSCursor。那普通 Cursor fetchmany是不是完全没用也不是。它至少能把业务处理逻辑拆成小批量避免写出一个巨大到没法 debug 的全量结果集对象。对于中等规模结果集fetchmany的收益主要体现在代码结构上而不是内存上。给你们看一个比较稳妥的批处理写法cursor.execute(SELECT id, name, created_at FROM big_table WHERE create_date %s, (2024-01-01,)) batch_size 1000 while True: batch cursor.fetchmany(batch_size) if not batch: break for row in batch: process(row)2.3 executemany批量写入时游标的另一个身份游标不光管读还管写。批量写入时经常用executemany它比一条一条execute快一个数量级data [ (1, a, 18), (2, b, 20), (3, c, 22), ] cursor.executemany( INSERT INTO user (id, name, age) VALUES (%s, %s, %s), data, )不同驱动对executemany的底层实现差别很大。PyMySQL 会把多条 SQL 拼成一段批量语句一次性发给 MySQL这带来一个很常见的坑当单条数据很长、数据条数又多拼出来的 SQL 可能超过 MySQL 的max_allowed_packet限制直接报OperationalError: (1153, Got a packet bigger than max_allowed_packet bytes)。解决思路不是去无限调大 MySQL 参数而是在应用层控制每次executemany的数据条数比如每 500 条一批循环多次提交。另外注意批量 INSERT 之后的lastrowid它通常只对第一条插入记录有意义别指望它返回本批次所有自增 ID。如果你需要拿到每一行插入后的自增 ID并且在 MySQL 5.7 以下版本老实说很难进一步优化通常的做法是业务表里加业务唯一键插入后再用唯一键反查MySQL 8.0 支持某些情况下用RETURNING但 PyMySQL 原生不支持需要别的方案这里不展开。3. 服务器端游标SSCursor才是大结果集的正确打开方式3.1 普通 Cursor 与 SSCursor后厨到底一次做几杯奶茶把结果集从 MySQL 搬到 Python 内存这个过程可以用奶茶店来类比。普通 Cursor 像一家批量出杯的奶茶店你下单后后厨一次性把这一批 100 杯全做好整整齐齐放在取餐台上然后一杯一杯叫号。你看起来是一杯一杯拿但你背后的取餐台上已经堆满了。这个取餐台就是客户端内存。SSCursor 则像单杯现做的模式后厨做一杯直接递到窗口给你下一杯才开始做。你拿多少杯后厨就做多少杯取餐台上永远只放一杯。这种模式下客户端不需要一大块空地来堆结果集所以内存占用很平稳。在代码上SSCursor 就是替换了游标类而已import pymysql conn pymysql.connect( host127.0.0.1, userroot, passwordxxx, databasetest, cursorclasspymysql.cursors.SSCursor, # 关键在这里 ) with conn.cursor() as cursor: cursor.execute(SELECT id, name, create_time FROM big_log) for row in cursor: # SSCursor 支持直接迭代 print(row)3.2 SSCursor 的三个硬性限制不知道就别随便用SSCursor 不是银弹它有非常明确的限制我用三个不能来总结第一结果集没有消费完之前同一连接不能执行其他 SQL。因为驱动从连接 socket 里一行一行读数据如果中间再往这条连接上发一条新 SQL就会出现协议错乱。最典型的表现就是报 2014 错误。解决办法是全部取完或者干脆关掉这个游标再复用连接。第二不能随意回退。普通 Cursor 的scroll()方法可以在本地缓存的行之间来回移动但 SSCursor 的数据没在本地缓存自然谈不上回退。对它调用scroll()会直接抛不支持错误。需要来回跳跃读取的场景SSCursor 不合适。第三用完必须关闭。SSCursor 依赖服务端保存结果集状态如果拿到一半就关程序不管了MySQL 要一直维护这个结果集直到超时白白占着服务器内存。所以务必把游标放进with里或者手动保证最终close()。我用 SSCursor 导出文件的常见姿势长这样def export_large_table(): conn pymysql.connect( host127.0.0.1, userroot, passwordxxx, databasetest, cursorclasspymysql.cursors.SSCursor, ) try: with conn.cursor() as cursor: cursor.execute(SELECT id, name, create_time FROM operation_log WHERE create_time 2024-01-01) # 拿列名 cols [desc[0] for desc in cursor.description] with open(export.csv, w, encodingutf-8) as f: f.write(,.join(cols) \n) while True: batch cursor.fetchmany(2000) if not batch: break for row in batch: f.write(,.join(str(v) for v in row) \n) finally: conn.close()3.3 DictCursor 与 SSDictCursor可读性与流式的搭配DictCursor本身不解决内存问题它只改变行的返回格式——从元组变成字典。对于字段多的表row[user_name]比row[3]可读性强太多。PyMySQL 也提供了SSDictCursor组合了流式读取和字典返回import pymysql conn pymysql.connect( host127.0.0.1, userroot, passwordxxx, databasetest, cursorclasspymysql.cursors.SSDictCursor, )用SSDictCursor做大结果集导出代码可比元组版本舒服多了。但它同样继承 SSCursor 的所有限制别因为返回类型变字典了就忘掉不能同时跑第二条查询这条规矩。另外一个藏在DictCursor里的细节如果你执行SELECT COUNT(*) FROM user拿到的字典 key 是COUNT(*)不是你以为的count(*)或者count。这时候row[count]会抛 KeyError排查起来还挺迷惑。建议写 SQL 时给聚合列加别名cursor.execute(SELECT COUNT(*) AS cnt, MAX(age) AS max_age FROM user) row cursor.fetchone() print(row[cnt], row[max_age])4. 游标和事务、连接池之间的资源边界比 API 本身更值得注意4.1 别把游标 close 当成事务提交新手最容易踩的一个坑cursor.close()执行完以为数据已经落库了。实际上事务状态挂在连接上不挂在游标上。MySQL 默认是关闭 autocommit 的PyMySQL 的 Connection 默认也继承这个行为——你执行 INSERT、UPDATE、DELETE 后如果没有显式conn.commit()那么这些修改只对当前事务可见别的连接看不到甚至当你关闭连接时未提交的事务会被回滚掉。数据消失的经典场景脚本运行完不报错查数据库也好像有数据但第二天看数据没了。多半就是前一天的执行连接在退出时把没提交的事务回滚了。正确的姿势是把 commit 的时机和位置放在业务边界上try: with conn.cursor() as cursor: cursor.execute(UPDATE account SET balance balance - 100 WHERE user_id %s, (1,)) conn.commit() # 事务提交放这里 except Exception: conn.rollback() raise4.2 with conn.cursor() as cur 和 with conn: 是两个完全不同的东西PyMySQL 的游标支持上下文管理器但那个with块只保证一件事退出时帮你调用cursor.close()。它不提交事务、不回滚事务。PyMySQL 的 Connection 也支持上下文管理器它才管事务with conn: # 退出块时正常就 commit异常就 rollback with conn.cursor() as cursor: cursor.execute(INSERT INTO user (name) VALUES (%s), (abc,))把这两层with叠在一起用是我比较推荐的写法内层管资源释放外层管事务边界。但注意不同驱动的Connection.__exit__行为不完全一致有些老驱动只做了 close 没做 commit/rollback所以在生产环境里切换到新驱动前一定要看一眼文档别把这套约定默认为所有驱动通用。4.3 连接池里布满僵尸游标的后果如果你的应用用了连接池比如 DBUtils、django-db-connection-pool或者公司自研的池子那游标和连接的生命周期管理会更微妙。连接池的核心是复用一个连接给多个请求。一个请求从池子里借走连接用完还回去。但如果借走连接时游标没有正确关闭或者事务没有提交这个连接就已经脏了。下次你再从池里拿到它可能还残留着上一次未消费的结果集、未提交的事务甚至卡在某个错误状态里。你在这个脏连接上执行新 SQL轻则数据错乱重则报commands out of sync。排查这类问题最有效的一招是让 DBA 或自己在 MySQL 里看连接状态SHOW PROCESSLIST;如果看到一大片Sleep状态的连接Time 秒数还特别大而且来源 IP 全是应用服务器十有八九是连接回收逻辑没写好。再往深了走可以查有没有一直开着的事务SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id FROM information_schema.INNODB_TRX;这条 SQL 能揪出那些开了事务但一直没提交的连接。把这些连接 Kill 掉应用再配合每次从池里拿连接都先 rollback 一下重置状态的策略基本能把僵尸连接控制住。4.4 autocommit 的开关按场景来定很多人嫌事务麻烦直接连接参数加个autocommitTrue所有 SQL 执行完立刻不可回滚。这在不同场景下利弊完全不同。做数据导入、跑报表、纯查询分析时开 autocommit 能省掉一堆忘了 commit 的麻烦也让连接状态更干净。但做业务交易类代码比如下单扣库存、转账必须把一组 SQL 放在一个事务里保证原子性。这种情况如果开着 autocommit一条 UPDATE 成功就生效了后续 SQL 失败时你没法回滚到初始状态资金数据就乱了。我的建议是数据库连接别全局统一设 autocommit而是按函数级别控制。查询类函数开 autocommit 图省心写操作类函数显式 commit/rollback。游标本身不负责这些事但游标所在的连接是干净还是脏直接决定你后续操作顺不顺利。5. 五个真实踩坑记录与排查链路5.1 commands out of sync同一个连接上贪心开游标有一次我写数据同步程序在一个循环里反复用同一个连接先cursor1查一批 ID处理完一批后又用同一个连接的cursor2去更新状态。代码逻辑看起来没问题但跑一会儿就报pymysql.err.ProgrammingError: (2014, Commands out of sync; you cant run this command now)当时第一反应是 MySQL 版本问题后来仔细一查才明白问题出在我用的是SSCursor结果集还没消费完同一个连接上又发了新 SQLMySQL 服务端的前一个结果集还没发完新命令根本没法处理。排查链路可以记一下找到报错位置看它属于哪个连接。检查这个连接上是否有未消费完的结果集。如果是 SSCursor 且只用fetchone取了几行就没管了这就是元凶。要么把结果集全部 fetch 完要么在发下一条 SQL 前cursor.close()要么给只读查询单独开一个连接。从此以后我给自己定了个规矩同一连接上结果集的生命周期必须严格限定在最小的代码块里。5.2 fetchall 导致的内存耗尽一次夜班导出任务的复盘朋友的公司做过一个运营数据导出功能从一张近 5000 万行的流水表里按月导出数据。第一次上线用的就是普通的 Cursor fetchall()跑了不到半分钟Java 侧倒是没事Python 脚本直接 OOM 被杀。当时第一反应是我是不是取完数据没释放内存排查完发现不是——fetchall()把整月数据全塞进列表几百万行嵌套元组内存当然扛不住。第二次改成普通 Cursor fetchmany(5000)以为完事了结果跑起来内存还是缓慢上涨。这正好印证了前面讲的PyMySQL 普通 Cursor 的第一次fetch会把结果集整个拉进本地缓存fetchmany只是切成小块往外吐内存并没有真正省下来。最后换成SSCursor fetchmany(5000)内存曲线变得非常平稳导出 300 万行数据稳定在几十 MB 级别。复盘结论就一条需要处理可能超过几十万行的大结果集时从一开始就选择 SSCursor而不是抱着普通 Cursor 硬扛。改进后的核心代码就是 3.2 节那个导出模板这里不再重复贴。关键点在于cursorclasspymysql.cursors.SSCursor换掉默认游标类剩下的逻辑不用大改。5.3 聚合查询列名的别名陷阱有一次在服务端接口里写统计逻辑用的DictCursorSQL 是cursor.execute(SELECT COUNT(*), MAX(create_time) FROM orders WHERE status 1) row cursor.fetchone()然后我想用row[count]和row[max]结果一跑就 KeyError。当时觉得特别奇怪明明查出来两列。后来打印row才发现字典的 key 竟然是{COUNT(*): 12345, MAX(create_time): datetime.datetime(...)}处理这类问题的思路很简单要么用row[COUNT(*)]要么一开始就在 SQL 里写别名。我强烈建议写聚合 SQL 时养成加别名的习惯因为在DictCursor之外一些 ORM 工具或者报表组件对列名的解析也依赖别名。cursor.execute( SELECT COUNT(*) AS order_cnt, MAX(create_time) AS last_time FROM orders WHERE status %s, (1,), ) row cursor.fetchone() print(row[order_cnt], row[last_time])5.4 忘记 commit 导致的数据神秘消失那是刚接触 PyMySQL 没多久时遇到的事写了一个半夜执行的积分结算脚本本地测试一切正常上线第二天对账发现一部分用户的积分根本没有更新。代码没有任何报错日志显示 UPDATE 执行成功了但数据库里就是查不到变更。后来查了一上午才发现是conn.commit()漏写了。因为本地测试时用的 Navicat 连接同一个库看起来像是生效了而生产环境的服务端连接在脚本结束后自动断开MySQL 把未提交的事务回滚了数据就消失了。这个坑的教训已经写进我自己的代码规范里所有写操作必须走异常回滚 正常提交的模板不允许执行完就不管的裸 SQL。尤其是游标对象只负责执行语句事务控制权永远在 Connection 手里。5.5 游标未关闭引发连接耗尽完整的现场排查回顾还有一次线上应用突然出现大量接口超时错误日志里反复出现pymysql.err.OperationalError: (1040, Too many connections)第一反应是 MySQL 最大连接数太小上去一查max_connections是 1000不算少。再看SHOW PROCESSLIST发现密密麻麻全是来自应用服务器的连接状态清一色是Sleep而且Time都已经几百秒了。顺着应用日志定位发现是一个定时任务里写了这样的代码def fetch_one_record(): conn get_conn_from_pool() cursor conn.cursor() cursor.execute(SELECT * FROM task LIMIT 1) row cursor.fetchone() return row问题很直接连接从池里拿出来了游标和连接都没有归还。fetchone虽然把行取回来了但游标对象还握着连接池子等不到归还只能不断新建连接直到把 MySQL 连接数打满。修复方案是三层代码层所有连接和游标都走with上下文管理器确保作用域结束自动归还。框架层如果用的是自定义连接池一定要在 borrow 连接 时校验连接是否健康不健康就丢弃重连。运维层临时用KILL thread_id清掉那些恶意占用连接的会话快速止血长期则要在连接池层面设置空闲回收时间避免连接被无限期占用。排查这种问题的路径我一般总结成四步先看SHOW PROCESSLIST确认连接状态再查information_schema.INNODB_TRX看有没有挂起事务接着看应用日志里连接获取和归还的代码路径最后统一改造成上下文管理器。最后分享一个我现在写 PyMySQL 的固定模板踩过这些坑以后我现在写任何和 PyMySQL 相关的代码起步都是这样的固定格式def db_query_many(sql, argsNone, batch_size1000, dict_cursorFalse): conn get_connection() cursor_cls pymysql.cursors.DictCursor if dict_cursor else pymysql.cursors.Cursor try: with conn.cursor(cursor_cls) as cursor: cursor.execute(sql, args) while True: batch cursor.fetchmany(batch_size) if not batch: break yield from batch finally: conn.close()查询走生成器边取边消费大小结果集都扛得住。写操作另写一个db_execute统一用with conn:包事务。游标这个东西API 本身确实简单但它的资源边界、读取节奏、事务归属才是真正决定一个 Python 后端系统稳不稳定的核心细节。希望这篇能帮你在下次遇到奇怪数据库问题时少走几条弯路。