刚开始接触 Python 操作 MySQL 的时候我也是先装个 pymysql写个conn.execute(sql)就跑但做到后面发现连接管理、参数传递、事务边界、性能调优每个环节都有不少讲究。标题里“高级”两个字不是说要用多玄的语法而是能把 Python 和 MySQL 之间的那层胶水粘得足够牢固跑得足够快也足够稳。这篇内容就是围绕这些实操经验展开的适合已经会用SELECT和INSERT、但想搞明白连接池、executemany、事务隔离级别、防注入这些进阶玩法的朋友。1. 环境准备与连接管理1.1 选对驱动别让基础拖后腿Python 操作 MySQL 的常用驱动无非三个PyMySQL、mysqlclient、MySQL Connector/Python。先说结论绝大多数 Web 项目和自动化脚本PyMySQL就够了纯 Python 实现装起来没有编译坑pip install pymysql一行搞定。mysqlclient是 C 扩展性能确实好一点但 Windows 上安装经常要装 Visual C Build Tools很多新手就在这一步卡住。MySQL Connector/Python是官方驱动功能全但稍微笨重除非是公司强制要求否则日常开发没必要特意用它。我自己的习惯是本地开发用PyMySQL生产环境如果发现性能瓶颈再评估换成mysqlclient。其实对于大部分业务逻辑瓶颈根本不在驱动那几毫秒的差异上而在 SQL 写法、索引和事务控制上。与其纠结驱动不如先把查询写好。1.2 连接参数里的门道很多教程只写host、user、password、database四个参数但实际工程里这几个参数必须重视charset必须显式指定charsetutf8mb4否则遇到 emoji 或者生僻字会报错或者乱码。utf8mb4是 MySQL 真正的 UTF-8兼容 BMP 和补充平面字符。autocommit默认是False意味着你执行INSERT后必须手动commit()不然关闭连接时会回滚。我见过不少同事在脚本里写完INSERT没提交数据就是不出来排查半天才发现。cursorclass常用的是pymysql.cursors.DictCursor这样查询结果就是字典列表字段名直接通过row[name]访问比默认的元组下标方式可读性好得多。connect_timeout和read_timeout设置连接超时和读取超时避免数据库假死时程序一直傻等。一个标准连接示例import pymysql from pymysql.cursors import DictCursor conn pymysql.connect( host127.0.0.1, port3306, userroot, passwordyour_password, databaseyour_db, charsetutf8mb4, autocommitFalse, cursorclassDictCursor, connect_timeout5, read_timeout10, )注意不要在生产代码里把密码直接写在源码中。运维规范一点的团队会用环境变量或者配置中心至少也要放在.env文件里并且不要提交到 Git。2. 核心操作与参数化查询2.1 别再拼接 SQL 了参数化才是正路新手时期最喜欢写这种代码sql SELECT * FROM user WHERE id user_id cursor.execute(sql)一旦user_id是从前端传进来的这就是标准的 SQL 注入漏洞。比如传入1 OR 11你的整张表数据都有可能被拉出来。高级的做法是参数化查询把 SQL 结构和参数分开sql SELECT * FROM user WHERE id %s cursor.execute(sql, (user_id,))%s是PyMySQL的占位符参数以元组形式传入。MySQL 驱动会把参数转义后交给服务端而不是简单拼字符串从而避免注入。INSERT、UPDATE、DELETE也一样sql INSERT INTO user (name, age) VALUES (%s, %s) cursor.execute(sql, (张三, 25)) conn.commit()这里有个细节写UPDATE和DELETE时务必带上WHERE条件并且条件里也要用参数化。我见过有人为了方便直接execute(DELETE FROM user)然后表空了别问我是怎么知道的。2.2 批量操作要会用 executemany如果要插入上千条数据逐条execute不仅慢还会频繁访问 MySQL 服务端。正确姿势是executemanydata [ (张三, 25), (李四, 30), (王五, 28), ] sql INSERT INTO user (name, age) VALUES (%s, %s) cursor.executemany(sql, data) conn.commit()executemany底层会把多条数据组装成复合语句发送给 MySQL节省了网络往返次数。实测插入一万条数据逐条执行大概要三四秒executemany能达到毫秒到百毫秒级别取决于字段数和表索引。注意executemany也有单次上限如果数据量特别大比如几十万条建议分批次每批 5000 条左右避免一次发送的 SQL 报文过大把 MySQL 的max_allowed_packet顶爆。2.3 游标的使用与资源释放每次执行查询后游标和连接都要记得关闭。我习惯用上下文管理器with conn.cursor() as cursor: cursor.execute(SELECT * FROM user WHERE id %s, (1,)) rows cursor.fetchall()with块结束后游标会被自动关闭但连接不会自动关。所以更彻底的做法是外面再套一层with pymysql.connect(...) as conn: with conn.cursor() as cursor: cursor.execute(...)连接实现__enter__和__exit__退出时如果事务没有提交会隐式回滚。要注意别在with conn块内忘记commit()否则所有写操作都不生效。想自动提交的话要么把autocommitTrue写在连接参数里要么每次写操作后显式commit()。我个人不喜欢全局autocommitTrue因为那样事务边界不好控制下面会展开说。3. 事务控制与隔离级别3.1 把多条写操作放进同一个事务批量数据的完整性往往需要多条 SQL 要么全部成功、要么全部回滚。比如转账从 A 扣钱、给 B 加钱两步必须在一个事务里。示例conn pymysql.connect(..., autocommitFalse) try: with conn.cursor() as cursor: cursor.execute(UPDATE account SET balance balance - 100 WHERE id 1) cursor.execute(UPDATE account SET balance balance 100 WHERE id 2) conn.commit() except Exception: conn.rollback() raise finally: conn.close()这里总结几个事务使用的关键点事务开始前的SELECT也算事务的一部分吗MySQL 默认的autocommit0下SELECT也会开启事务直到 commit 或 rollback。所以避免在事务里执行耗时很长的只读查询锁持有时间会变长。事务中遇到异常要么 rollback要么 close。close时未提交的事务默认回滚所以会丢失未提交的修改。不要在一个事务里混合大量UPDATE和长时间的 Python 计算。锁等待时间越长死锁概率越高。3.2 事务隔离级别与锁的影响MySQL InnoDB 默认隔离级别是REPEATABLE READ在某些场景下会导致幻读插入新行后同样的查询出来的行数不一样但对大部分业务是安全的。Python 连接中可以通过会话变量修改SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;或者在 Python 里执行这条 SQL。什么场景需要改比如统计报表频繁读取时希望每次读到的都是已提交数据避免读到别的事务未提交的“脏数据”。这里不建议新手乱调隔离级别先理解两个概念即可READ COMMITTED只能读到已提交事务的数据能避免脏读但不可重复读同一事务中两次读同一行结果可能不同。REPEATABLE READ默认级别同一事务中多次读同一结果一致使用 MVCC 实现避免不可重复读。如果遇到死锁先不要急着改隔离级别多数死锁是索引失效导致的锁范围过大。排查时用SHOW ENGINE INNODB STATUSMySQL 会打印出最近一次死锁涉及的 SQL 和锁信息这是高级排查的第一步。4. 性能优化与安全加固4.1 连接池别再每次请求都连接数据库Python 脚本短任务中每次创建一个连接、用完关闭问题不大。但放到 Web 服务里每次请求都新开 MySQL 连接三次握手 权限校验的消耗会被放大高并发时数据库连接数直接飙升到max_connections上限后面请求全部排队超时。连接池是必选项。PyMySQL本身不带连接池但可以使用DBUtils.PooledDBpip install DBUtils示例from dbutils.pooled_db import PooledDB import pymysql pool PooledDB( creatorpymysql, maxconnections10, mincached2, maxcached5, blockingTrue, host127.0.0.1, userroot, passwordyour_password, databaseyour_db, charsetutf8mb4, cursorclasspymysql.cursors.DictCursor, )使用方式conn pool.connection() try: with conn.cursor() as cursor: cursor.execute(SELECT 1) conn.commit() finally: conn.close() # 不是真关闭而是归还给池子参数“blockingTrue”表示连接池满时新请求阻塞等待有空闲连接而不是直接抛异常。生产环境中连接池大小不是越大越好通常设置为“数据库最大连接数除以服务实例数”左右。比如 MySQLmax_connections200你有 4 个服务实例每个实例连接池maxconnections可以设为 40~50留一些缓冲给管理工具和其他连接。4.2 慢查询优化先拿 EXPLAIN 说话任何 Python 操作 MySQL 的调优最后都要落到 SQL 本身。当一段查询变慢我第一反应不是调代码而是用EXPLAIN看执行计划EXPLAIN SELECT * FROM user WHERE email testexample.com;关注type字段ALL表示全表扫描必须优化ref或const说明走了索引。再看rows预估扫描行数如果特别大即使走了索引也可能是因为索引区分度不够。常见优化手段给WHERE和ORDER BY涉及的字段建联合索引但不要建太多索引会拖慢写入。避免在 WHERE 条件字段上做函数运算比如WHERE YEAR(created_at) 2023数据库没法用索引要改成WHERE created_at 2023-01-01 AND created_at 2024-01-01。分页查询用LIMIT时越到后面越慢因为数据库要扫描前面所有结果。可以用“延迟关联”或基于主键的分页方式比如WHERE id last_id ORDER BY id LIMIT 20。4.3 安全加固权限与备份缺一不可Python 代码层面做到参数化后还要管好数据库账号权限。给应用连接用的账号不要用 root单独建一个只允许SELECT / INSERT / UPDATE / DELETE的账号禁止DROP、TRUNCATE等高危操作。如果应用只需要查询就只授权 SELECT。宁可应用崩了也不能让它把库删了。备份是保命符。平时写脚本操作数据前最好先SELECT COUNT(*)或者把原数据备份到临时表尤其做批量更新前。我习惯先跑一次SELECT把受影响的主键列表打印出来核对无误再执行 UPDATE。数据库备份是另外一个话题但既然操作 MySQL至少要知道mysqldump的存在mysqldump -u root -p your_db backup.sql5. 常见问题与避坑清单5.1cant connect to local MySQL server through socket /tmp/mysql.sock热词里出现了这个报错非常典型。根因有两类一是连接参数里用了localhostMySQL 会优先走 socket 而不是 TCP。PyMySQL中hostlocalhost时默认走 TCP但某些驱动或配置下会尝试 socket。解决办法是统一使用host127.0.0.1强制走 TCP二是 MySQL 服务真的没启动用systemctl status mysql或service mysql status查一下。5.2execute后数据没有写入排除代码逻辑后九成可能没commit()。PyMySQL默认autocommitFalse执行INSERT、UPDATE、DELETE后必须conn.commit()。很多人写了关闭连接才看到数据没有变化就是这个原因。写个工具函数统一提交或者开启自动提交都会省事但事务控制要自己权衡。5.3 游标里的字段顺序不稳定用默认游标时fetchall()返回的是元组字段顺序依赖 SELECT 写的字段顺序。一旦 SQL 调整字段顺序代码里的下标访问就会跟着变容易出错。解决方案就是前面说的用DictCursor让结果以字典形式返回字段名自解释可维护性大幅提升。5.4 字符集乱码与 emoji 报错连接参数没设charsetutf8mb4数据库表本身也要是utf8mb4才行ALTER TABLE user CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;如果只是单个字段需要存 emoji也可以只改字段ALTER TABLE user MODIFY nickname VARCHAR(100) CHARACTER SET utf8mb4;5.5 批量插入导致max_allowed_packet超限executemany插入大量数据时SQL 报文过大MySQL 默认max_allowed_packet64M不同版本不同超过后直接报错。解决思路减少批次大小或者临时调大 MySQL 参数SET GLOBAL max_allowed_packet 128 * 1024 * 1024;注意最好改成批量每批几千条而不是一味调大参数因为过大的包也会占用服务器内存。除了上面这些还有两个常用原生优化技巧。第一cursor.scroll可以移动游标位置但我不建议在数据量大时用它做随机跳跃查询效率不高。第二使用SELECT ... FOR UPDATE时注意要在事务中并且用到索引行否则会锁很多行性能事故就是这么来的。我个人在实际操作中的体会是Python 操作 MySQL 的代码量不需要多但每一行连接和游标的边界都要心里有数。尤其写脚本时多问自己一句“如果中间失败了数据处于什么状态”比如批量更新前先备份执行后用SELECT验证受影响行数最后再提交。不要嫌多余生产环境的很多事故都是从“我以为不会出错”开始的。还有一个值得养成的习惯所有写操作函数都接收一个显式的connection参数而不是在函数内部新建连接。这样既方便单元测试时用事务回滚模拟数据也方便将来接连接池。我在多个项目里这么重构后代码维护成本明显降下来了。如果你现在还在纠结“为什么我的数据总是不对”大概率就是连接、提交、回滚这三者的生命周期没理清。把这条理顺你离真正的“高级”也就不远了。