《叶问》32期，一样的Python代码，为什么可以删表，却不能更新数据
《叶问》32期，一样的Python代码，为什么可以删表，却不能更新数据
老叶茶馆
发布于 2021-05-31 10:48:20
发布于 2021-05-31 10:48:20
问题
运行下面的这段Python代码，却总是无法更新数据：
import pymysql
conn=pymysql.connect(
host = '127.0.0.1', user = 'yewen', passwd='YeWen.3306',
port= 3306, db='test', charset='utf8mb4')
cur = conn.cursor()
sql = "update t1 set c3 = rand()*10240 where c1 = rand()*1024"
cur.execute(sql)
cur.close()
conn.close()
而运行下面的这段看起来一样的代码，却可以正常删表：
import pymysql
conn=pymysql.connect(
host = '127.0.0.1', user = 'yewen', passwd='YeWen.3306',
port= 3306, db='test', charset='utf8mb4')
cur = conn.cursor()
sql = "drop table tmp1"
cur.execute(sql)
cur.close()
conn.close()
回答
其实问题并不复杂，有几个原因：
- 要写入的表是InnoDB引擎，而InnoDB引擎是支持事务的，也就是写入后，要提交事务才是真正完成写入。
- 连接数据库时，需要自行设定事务自动提交模式，是开启还是关闭。
- pymysql模块里，默认不启用自动提交模式。
- 所以对表进行DML操作时，需要提交事务后才能成功。
- 而删除表是DDL操作，目前DDL操作还不支持事务，所以即便没有开启自动提交，也能成功。
知道上面的原因就好办了。我们先看下pymysql源码中关于自动提交的设定：
[root@yejr-mgr1 pymysql]# cat /usr/lib/python2.7/site-packages/pymysql/connections.py
...
#约158行附近
158 :param autocommit: Autocommit mode. None means use server default. (default: False)
...
所以，解决方法有好几种：
1. 在连接初始化时开启自动提交模式，例如：
#设置属性autocommit=1亦可
conn=pymysql.connect(
host = '127.0.0.1', user = 'yewen', passwd='YeWen.3306',
port= 3306, db='test', charset='utf8mb4', autocommit=True)
2. 或者执行完DML操作后，再执行一次commit请求，例如：
sql = "update t1 ...
cur.execute(sql)
cur.execute("commit")
3. 又或者在创建完连接后，修改autocommit模式，例如：
conn=pymysql.connect(
host = '127.0.0.1', user = 'yewen', passwd='YeWen.3306',
port= 3306, db='test', charset='utf8mb4')
cur = conn.cursor()
cur.execute("set autocommit=1")
到这里，自动提交的问题解决了。
但还要更进一步，开启或关闭autocommit有什么利弊呢？简言之，有几点建议：
- 当有大批量数据更新时，可以先关闭autocommit，等事务结束后，再手动提交。事务commit时要刷新redo log、binlog等，代价还是比较大的。
- 关闭autocommit的缺点在于，当忘记主动提交事务时，可能会造成相应的行锁一直持有不释放，其他事务会被长时间阻塞，如果是线上生产环境，则可能造成严重后果（业务长时间不可用）。
- 因此，需要根据实际情况动态调整autocommit的模式，并没有通用的设置。
- 不少开发框架都会默认设置
set autocommit=0，更有甚者，每次执行一个SQL前，都要发送一次set请求，增加了无谓的开销，如果有这种情况，可以自行调整开发框架的代码。
Enjoy MySQL :)
本文参与 腾讯云自媒体分享计划，分享自微信公众号。
原始发表：2021-05-14，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
目录
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server （TencentDB for SQL Server）是业界最常用的商用数据库之一，对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权，可持续为用户提供最新的功能，避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。