做Python后端开发这么久我写过不少数据库操作代码也带过一些新人。几乎每个刚接触数据库操作的人都会问同一个问题Python连MySQL到底用什么我的答案一直很固定先学PyMySQL。PyMySQL是纯Python实现的MySQL客户端库一条pip命令就能装好不需要编译C扩展也不依赖乱七八糟的系统库跨平台跑得都很稳。这篇指南就是围绕PyMySQL把MySQL操作这件事讲透从环境搭建、核心API、增删改查到事务、连接池和常见坑完整覆盖你在真实项目里会遇到的绝大部分场景。不管是完全没接触过的初学者还是想系统梳理一遍的老手都能找到直接能用的代码和思路。1. 为什么是PyMySQL驱动选型的几个信号1.1 纯Python实现的天然优势PyMySQL的前身可以追溯到Python 2时代当时最流行的是MySQLdb——一个基于C扩展的驱动。MySQLdb性能确实好但它有个致命问题编译安装依赖系统的MySQL客户端开发库而且对Python 3的兼容一直拖了很久。PyMySQL的出现正好填补了这个空白它用纯Python重写了客户端与服务端的通信协议直接通过socket和MySQL服务器交互因此不需要任何编译步骤。这意味着什么在你的服务器上只要能装Python能跑pip就能用PyMySQL不必为了一个数据库驱动去装gcc、装mysql-devel也不必担心生产环境的操作系统版本太老导致编译失败。我早期在CentOS 6上部署过一次那时候需要一个能连MySQL的驱动在那边编译折腾了一下午换成PyMySQL五分钟就通了。这种“零编译依赖”的好处在云服务器、容器镜像、离线环境里尤其明显。1.2 PyMySQL、MySQLdb和官方驱动怎么选目前市面上Python连MySQL的方案无非这么几种我列一张表对比驱动实现方式安装成本维护状态适用场景PyMySQL纯Pythonpip一键活跃绝大多数业务场景入门首选MySQLdbC扩展需编译依赖维护缓慢老项目遗留不建议新项目使用mysql-connector-python官方纯Pythonpip一键官方需要官方支持或特殊认证插件aiomysql基于PyMySQL的异步版pip一键活跃asyncio异步项目如果项目里用了ORM——比如SQLAlchemy、Django ORM——它们底层的MySQL驱动也可以指定为PyMySQL比如SQLAlchemy的连接串可以写mysqlpymysql://。这说明PyMySQL在生态里的渗透率非常扎实。至于mysql-connector-python它在某些认证插件和特定客户端协议上跟官方服务器更同步但日常用法和PyMySQL几乎一样从社区、文档、踩坑案例的数量来看PyMySQL仍然是更主流的选择。这里补充一点我的判断如果你只是写业务逻辑不需要纠结驱动的底层性能差异Python这一层已经给你做了缓冲。除非你的QPS高到数据库成为瓶颈否则PyMySQL和C扩展驱动的性能差距几乎可以不考虑与其纠结驱动不如把精力花在SQL本身和索引设计上。1.3 什么时候该上SQLAlchemy这类中间层PyMySQL是直连层意味着所有SQL都要你自己写。写得多以后你会觉得繁琐——要管理连接、游标、事务、类型转换。这时候可以选择引入SQLAlchemy CoreSQL表达式层或者直接用ORM。但注意SQLAlchemy并不是PyMySQL的替代品它底层仍然需要一个驱动最常见的就是PyMySQL。所以我的建议是学习阶段一定先用PyMySQL裸写把连接、游标、事务这些本质概念吃透之后再用SQLAlchemy会觉得一切都是顺理成章的。很多新人一上来就上ORM遇到复杂查询时完全不知道SQL是怎么执行的排查问题两眼一抹黑这就是底子没打好。2. 环境搭建从Python解释器到一条能跑的连接2.1 Python环境准备PyMySQL支持Python 3.6及以上理论上到你手里的任何Python 3环境都行。确认Python环境最直接的方式python3 --version pip3 --version如果没有pip先用系统包管理装一下。Windows用户我习惯建议直接去官网下载安装包安装时把Add Python to PATH勾上macOS上可以用brew。这里有个容易忽略的地方PyMySQL本身是纯Python包依赖的第三方库极少所以安装它几乎是无害的不会动你系统里已有的包。这也意味着你可以放心地把它装到全局环境而不需要像某些依赖很重的包那样非要在虚拟环境里操作才敢装。2.2 MySQL服务器的安装与基础配置数据库那边以MySQL 8.x为例。Windows上直接下载MSI安装包一路下一步就行重要的一点是记住你设置的root密码并且建议选择utf8mb4作为默认字符集。Linux走包管理器sudo apt update sudo apt install mysql-server装完以后sudo systemctl start mysql sudo mysql_secure_installationMySQL 8默认的认证插件是caching_sha2_password。PyMySQL从1.0.0版本开始支持这个认证方式所以如果你装的是新版PyMySQL问题不大。但如果你发现连接时报Authentication plugin ... not supported这说明PyMySQL版本太老升级到最新版即可。另外需要确认root用户是否只允许localhost登录。开发环境无所谓如果代码要和MySQL分开部署就给程序创建一个专用账号比如CREATE USER pyuser% IDENTIFIED BY your_password; GRANT ALL PRIVILEGES ON py_test.* TO pyuser%; FLUSH PRIVILEGES;建议使用程序专用账号而不直接拿root去连库这是我从一个线上事故学到的教训——当时甲方团队直接把root密码写进了配置文件后来安全审计被点名非常被动。2.3 安装PyMySQL并验证一条连接pip install pymysql装完后验证版本python3 -c import pymysql; print(pymysql.VERSION)然后写一个最简单的“能通就行”的连接测试脚本import pymysql conn pymysql.connect( host127.0.0.1, port3306, userpyuser, passwordyour_password, databasepy_test ) with conn.cursor() as cur: cur.execute(SELECT 1) print(cur.fetchone()) conn.close()如果打印出(1,)恭喜你链路通了。这一步如果报错先把host换成127.0.0.1而不是localhost——PyMySQL在某些环境下解析localhost的默认路由时会走IPv6的::1而MySQL只监听了IPv4这个细节能劝退很多人。3. Connection与CursorPyMySQL核心API的逐层拆解3.1 连接参数里那些容易被忽略的细节pymysql.connect返回的Connection对象本质上是对底层socket、认证、数据库环境变量等状态的封装。常用参数hostMySQL服务器地址本机建议写127.0.0.1port默认3306user、password、database身份和库charset连接字符集开发中几乎必填建议填utf8mb4autocommit是否自动提交事务默认False后文专门讲connect_timeout、read_timeout、write_timeout控制等待时间连接字符集这个坑我当初踩过如果建表时用了utf8mb4但连接charset设的是utf8那么四字节的emoji写入时会报错或变成乱码。原因在于MySQL的utf8实际是utf8mb3最多支持三字节编码而utf8mb4才是完整的四字节UTF-8。所以统一用utf8mb4是稳妥的习惯。如果出现mysql ssl连接错误比如报SSL connection error或SSL not enabled这类问题大方向是MySQL服务端启用了TLS但客户端协商失败或者反过来服务端要求SSL而client没开启。排查思路在后面第8节专门展开。3.2 Cursor的工作原理与三种fetch方式Connection建立后本质上就是一条可以传输数据的通道。要执行SQL并取回结果得先创建Cursorcur conn.cursor() cur.execute(SELECT id, name FROM users LIMIT 10) rows cur.fetchall()cur.execute()把SQL发给服务器服务器执行完后把结果集暂存在客户端内存中对默认游标而言。然后fetchall()把结果全部取到Python列表每行是一个元组fetchone()只取一行fetchmany(size)取指定行数。row cur.fetchone() # 单行 rows cur.fetchmany(5) # 一次5行很多人只记得fetchall遇到大结果集直接把内存撑爆。比如一张千万级的表执行SELECT *然后fetchallPython进程可能会占掉好几个G。这时候有两种选择分批fetchmany或者改用服务端游标SSCursor第7节会讲。这里先记住一个判断标准结果集会很大时不要盲目fetchall。3.3 DictCursor让字段有名字可读默认游标返回的是元组访问第n列靠下标写着写着就晕了。尤其SQL里SELECT的列很多时rows[0][2]这种代码完全没法维护。可以传入DictCursorcur conn.cursor(cursorpymysql.cursors.DictCursor) cur.execute(SELECT id, name FROM users LIMIT 3) for row in cur.fetchall(): print(row[id], row[name])从维护角度我几乎在所有写业务代码的地方都用DictCursor返回的每行是dict字段清晰。元组游标只在上手练习时用。3.4 自动关闭资源with上下文管理器的正确用法下面这样写是常见的坑conn pymysql.connect(...) cur conn.cursor() cur.execute(...) conn.close()如果execute或fetch过程中抛了异常conn.close()根本执行不到连接就会泄露。虽然GC最终会回收但长循环里反复如此连接数会积累到MySQL的max_connections。用上下文管理器把打开和关闭交给withwith pymysql.connect( host127.0.0.1, userpyuser, password..., databasepy_test, charsetutf8mb4 ) as conn: with conn.cursor() as cur: cur.execute(SELECT 1) result cur.fetchone() print(result)注意Connection对象作为上下文管理器时无论正常结束还是异常退出都会调用close关闭底层连接。但有个细节它不会自动commitcommit得你自己记得调用这个后面事务部分会展开。4. 落业务一张订单表上的完整CRUD与批量写入4.1 建表脚本与数据约定直接用一个贴近现实的例子——订单表CREATE TABLE orders ( id BIGINT AUTO_INCREMENT PRIMARY KEY, order_no VARCHAR(64) NOT NULL UNIQUE, user_id BIGINT NOT NULL, amount DECIMAL(10,2) NOT NULL DEFAULT 0.00, status TINYINT NOT NULL DEFAULT 0 COMMENT 0待支付 1已支付 2已退款, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;DECIMAL(10,2)是为了金额精度坚决不用FLOAT。created_at和updated_at用数据库默认值生成程序就不用管了。这里的DEFAULT 0也顺便回答了为什么有些场景喜欢把status这种字段设成默认0——初始化状态由数据库保证应用层少一次判断。4.2 用参数化SQL写单条增删改查插入一条with conn.cursor() as cur: sql INSERT INTO orders (order_no, user_id, amount, status) VALUES (%s, %s, %s, %s) cur.execute(sql, (NO202501010001, 1001, 199.90, 0)) conn.commit()注意PyMySQL默认autocommitFalseINSERT后必须调用conn.commit()否则数据只是事务里被“看见”提交前回滚就没了。很多新人第一个“数据丢了”的坑就是忘了commit。获取自增IDinsert_id cur.lastrowid print(insert_id)查询with conn.cursor(pymysql.cursors.DictCursor) as cur: cur.execute(SELECT id, order_no, amount, status FROM orders WHERE user_id%s, (1001,)) rows cur.fetchall()更新cur.execute(UPDATE orders SET status%s WHERE order_no%s, (1, NO202501010001)) print(影响行数:, cur.rowcount)删除cur.execute(DELETE FROM orders WHERE id%s, (12345,))rowcount值得多说一句它表示服务端实际匹配修改的行数不是客户端判断出来的。有时候你执行UPDATE条件匹配一行且值完全相同MySQL返回的影响行数可能是0因为值没变化。知道这个区别就不会在判断“到底有没有更新成功”时出错。4.3 executemany批量操作与游标元数据批量插入的性能差很大。比如一次要写一万条订单明细如果循环单条insert不仅每轮都有网络往返Python层还有大量函数调用开销。用executemany可以大幅减少交互次数data [] for i in range(100): data.append((fNO20250101000{i}, 1001, 10.0 i, 0)) sql INSERT INTO orders (order_no, user_id, amount, status) VALUES (%s, %s, %s, %s) with conn.cursor() as cur: cur.executemany(sql, data) conn.commit()实测过同样是1万条插入单条execute循环大概十几秒executemany批量不到一秒。原因一是底层会把多条语句打包发送二是减少了Python与数据库之间的协议往返。批量UPDATE、DELETE同样可以用executemanyMySQL的DML都支持这种批处理模式。这里有一个很多人不知道的细节executemany在处理大数据列表时默认把整份参数列表一次性发给MySQL。如果数据特别大几十万条内存和时间都会激增。更稳妥的做法是分批比如每5000条提交一次既控制内存又不会把binlog写得太猛。5. 为什么参数化查询是安全底线SQL注入场景推演5.1 字符串拼接的隐患看起来什么样最直观的暴力写法sql SELECT * FROM orders WHERE order_no order_no 如果order_no来自用户输入输入是abc OR 11拼出来的SQL就变成SELECT * FROM orders WHERE order_no abc OR 11整个表的数据都能查出来了。更严重的是DELETE、UPDATE被拼接那就是数据灾难。这个道理几乎所有教程都讲但还是会有人在生产环境写出类似代码——尤其是赶工期的外包方案。我在代码评审阶段遇到这种写法第一反应就是退回让改因为这不是性能问题是安全问题。5.2 %s占位符的真正工作机制PyMySQL的参数化不是简单的字符串替换而是把参数经过类型转换和安全转义之后作为独立的值绑定到SQL语句上。也就是说客户端把SQL和参数分开送给服务端服务端在解析阶段就把参数当作字面量参数绝不参与SQL语法解析。于是即使参数是恶意的它也只是“一个值”不会变成一个新的SQL片段。cur.execute(SELECT * FROM orders WHERE order_no %s, (order_no,))这里的%s是占位符不是Python字符串格式化的%s直接用元组或列表传参即可。需要特别留意一点PyMySQL不支持?这种占位符风格一律用%s。如果字段名、表名这类标识符需要动态拼接那属于另一个问题——因为语法上不是值不能参数化必须在代码里做白名单校验后再拼。5.3 like、in这些特殊场景的参数化处理LIKE查询时你希望模糊匹配用户输入的关键词keyword 产品 cur.execute(SELECT * FROM articles WHERE title LIKE %s, (f%{keyword}%,))注意占位符本身不接受两边带%的写法%要拼在参数里。IN操作也一样ids [1, 2, 3] format_strings ,.join([%s] * len(ids)) sql fSELECT * FROM orders WHERE user_id IN ({format_strings}) cur.execute(sql, ids)这个写法用Python生成占位符列表再把ids作为扁平参数传进去。写习惯了以后你会觉得参数化反而是最省事的写法不用自己处理引号、转义和类型判断。5.4 参数化对性能的隐性帮助参数化不仅安全还对数据库执行效率有好处。MySQL收到参数化语句后可以基于语句模板做执行计划缓存同一形态的SQL命中缓存省去重复解析的开销。你在慢日志里如果看到清一色的WHERE id 1001、WHERE id 1002那是拼接SQL导致的计划无法复用而WHERE id %s这种计划缓存就能正常复用。另外参数化还是处理类型转换的一把好手。比如日期字符串转日期你可以在SQL里写SELECT ... WHERE created_at STR_TO_DATE(%s, %%Y-%%m-%%d)参数传字符串服务端负责转换避免自己在Python端反复处理格式问题。6. 事务控制commit、rollback与应用层的一致性保证6.1 PyMySQL默认的自动提交策略PyMySQL的Connection构造函数里autocommit默认是False。这意味着你每一条DML操作都在一个隐式事务里如果不主动commit数据不会真正落盘。这个设计其实符合数据库的安全直觉——显式控制避免业务逻辑没跑完就悄悄提交一半数据。但开发初期看着总觉得别扭写完insert不commit数据查不着然后满世界找bug。我的建议是开发测试时保持autocommitFalse逼自己养成显式commit的习惯生产环境如果是简单脚本任务可以显式地在每个事务结束时commit。不要图省事全局开启autocommit因为一旦脚本中途异常退出没有commit的部分可以安全回滚全局autocommit反而会留着半截数据。6.2 一套可复用的with事务包装写法在业务里原子操作往往不是单条SQL而是多条DML需要一起成功或一起失败。比如支付回调更新订单status1同时给用户账户加余额。如果只更新成功但加余额失败账就对不上了。用事务包起来try: with conn.cursor() as cur: cur.execute(UPDATE orders SET status%s WHERE order_no%s, (1, order_no)) cur.execute(UPDATE user_accounts SET balancebalance%s WHERE user_id%s, (amount, user_id)) conn.commit() except Exception as e: conn.rollback() raise关键点任何异常都要rollback不然连接回到连接池或下一次使用时事务里未提交的数据会产生脏状态。如果要保存点SAVEPOINTMySQL支持相关语法PyMySQL直接execute即可。但在绝大多数业务场景中把事务控制在单条连接里、采用单一try/commit/rollback就够不必为了所谓的精致引入savepoint。6.3 隔离级别选择与并发安全的基本认知MySQL InnoDB默认隔离级别是REPEATABLE READ。在这个级别下同一连接里多次SELECT看到的可能是同一份快照。对大多数业务来说默认隔离级别就是正确的选择。还有一个常见的坑事务开着不提交长时间持有连接数据库端的undo log和锁会一直积累。有一次我发现生产库的连接数和锁等待飙高最后定位到的根因是循环里对每条数据都开启事务但忘了commit事务一直挂在那一行后面所有针对这行的操作都阻塞了。这个教训我记到现在事务范围要小逻辑处理完立刻commit尤其不要在事务里做网络请求或耗时计算。7. 性能优化连接池、批量写入和流式读取的实测数据7.1 为什么高并发场景必须复用连接MySQL连接的本质是一次TCP连接加上服务端认证和会话初始化。创建一次连接要完成TCP三次握手、SSL协商如果有、认证、设置会话变量等步骤。高并发下如果每次数据库操作都新建连接性能会非常难看。我实测过在同样一次简单SELECT下新建连接耗时通常是复用已就绪连接耗时的几倍到十几倍网络延迟高的时候更明显。所以线上应用不直接每次都pymysql.connect()而是要维护一批常驻连接的连接池。请求来了从池里拿用完归还而不是销毁。7.2 一个基于queue的手写连接池实现生产环境可以用现成的库比如DBUtils、SQLAlchemy自带的pool但自己写一个简单的也能帮助理解原理。下面这个是我常用的简化版import pymysql import queue import threading class ConnectionPool: def __init__(self, size5, **conn_kwargs): self._size size self._conn_kwargs conn_kwargs self._pool queue.Queue(maxsizesize) self._count 0 self._lock threading.Lock() for _ in range(size): self._create_conn() def _create_conn(self): with self._lock: if self._count self._size: conn pymysql.connect(**self._conn_kwargs) self._pool.put(conn) self._count 1 def get_conn(self): try: return self._pool.get(timeout5) except queue.Empty: raise RuntimeError(连接池耗尽请配置更大的池或检查连接回收) def return_conn(self, conn): if conn: try: conn.rollback() except Exception: pass self._pool.put(conn) def close_all(self): while not self._pool.empty(): conn self._pool.get() conn.close()使用时有两点必须注意一是多线程场景下必须保证连接不会被两个线程同时使用所以get_conn()之后连接就相当于“借出”归还前不能再被其他人拿到二是归还时最好rollback防止把上一个事务的脏状态带给下一个使用者。最容易被忽视的是连接长时间不用MySQL服务端会依据wait_timeout参数把它断开。池里的连接如果一直闲置等你拿出来用时可能已经失效第一次execute会报MySQL Connection not available或类似错误。常规解法是拿到连接后先做一次心跳检测比如SELECT 1失败则重建连接。很多成熟连接池内置了这个检查手写池时一定要自己补上。7.3 流式读取大结果集SSCursor的使用场景默认Cursor会先把完整结果集从服务端拉到客户端内存。对大查询比如导出几百万行数据内存压力惊人。PyMySQL提供了SSCursor服务端游标它是真正“边读边取”的流式方式conn pymysql.connect(...) cur conn.cursor(pymysql.cursors.SSCursor) cur.execute(SELECT * FROM big_table WHERE id 100000) while True: rows cur.fetchmany(1000) if not rows: break process_rows(rows)但SSCursor有几个约束必须注意使用SSCursor时同一连接上不能同时执行其他语句直到把当前结果集全部读完或显式关闭游标结果集不关闭会一直占用服务端资源。另外SSCursor读取期间连接不能归还连接池否则会串掉状态。这个机制和我用生成器逐批读文件的思路一样内存好了很多但对连接的管理要求提高了。8. 踩坑实录SSL连接错误、字符集与连接泄漏的修复复盘8.1 mysql ssl连接错误一次完整排查链路某次在客户环境的Windows服务器上跑脚本连接数据库报SSL connection error。这里复盘一下完整排查流程先看PyMySQL版本。如果过老可能根本不认识服务端开启的TLS。升级pymysql到最新版。看MySQL服务端配置。执行SHOW VARIABLES LIKE %ssl%看have_ssl的值。如果服务端开了require_secure_transportON客户端必须支持TLS。看连接参数。PyMySQL处理SSL的方式是服务端支持且要求时让客户端尝试协商。如果内网环境或测试库开启了强制SSL而客户端经常因为CA证书不对导致协商失败一个变通方式是显式传ssl参数控制校验级别或者从服务端侧临时关闭require_secure_transport先定位到底是不是证书问题。最后用命令行客户端验证mysql -h ... -u ... -p。如果命令行能连而PyMySQL不能基本就是客户端TLS参数不匹配围绕ssl参数调整即可。这个问题最常见的原因是MySQL 8.0的默认配置下服务端自动启用了TLS而客户端这边没有配置合适的CA证书。连接串里加下面这样一般就能过pymysql.connect( hostdb.example.com, userpyuser, password..., databasepy_test, ssl{ca: /path/to/mysql-ca.pem} )如果只是开发调试且能接受不加密可以临时在服务端关闭require_secure_transport但生产上我建议严格走TLS并配置好证书。8.2 字符集乱码utf8和utf8mb4的根因乱码问题十次有八次是字符集不一致。MySQL里utf8实际上是utf8mb3最多只支持三字节编码而现在的emoji和一些生僻字是四字节。如果你把表建成了utf8连接charset也写utf8写入emoji时INSERT会报错或数据变成???。解决办法是三层都统一成utf8mb4建库建表DEFAULT CHARSETutf8mb4连接参数charsetutf8mb4已建的表ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4注意连接charset和表的charset是两个层面的东西都要统一。我曾遇到过一个库表全是utf8mb4但连接参数漏写charset导致中文乱码的问题。一补上charsetutf8mb4就恢复正常。8.3 时区、默认时间戳和游标泄漏时区问题同样隐蔽。MySQL的连接会话有自己的time_zone参数。如果你的应用服务器和数据库服务器不在同一时区DATETIME字段的读写就可能偏8小时。统一的做法是在连接参数里指定init_commandpymysql.connect( ..., init_commandSET time_zone 08:00 )默认时间戳建表时用DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP这样程序层完全不用管创建和更新时间。但如果有人直接往created_at塞字符串时间就会变成业务代码来背锅。建议把默认值交给数据库代码里不要显式传这两个字段。游标泄漏Connection对象不关、Cursor对象不关是新手写PyMySQL最常见的小毛病。短期看不出问题因为Python会垃圾回收但高并发Web服务里一个请求一查库就new一个连接且不关闭连接数迟早打满max_connections。排查时可以执行SHOW PROCESSLIST;如果看到一大串Sleep状态的连接基本就是泄漏了。另外如果你用Flask/Django这类Web框架连接的生命周期最好和请求生命周期对齐要么每请求创建、结束时关闭要么用连接池统一管理。最忌讳的是模块级全局conn变量被多个线程共用因为连接不是线程安全的。多线程并发操作同一个连接轻则数据串位重则协议栈直接崩溃——这个问题在高并发下才会暴露前期极难察觉等发现时往往已经线上故障了。