使用python mysql.connector执行update查询时报Commands out of sync错误

错误根源 #

你当前触发Commands out of sync报错的核心原因是：mysql-connector默认不支持单次cursor.execute()调用执行多条SQL语句，你的代码中将SET变量赋值和UPDATE更新两条SQL拼接后传入执行，执行完第一条语句后残留的未处理结果集导致连接状态不同步，后续执行commit就触发了该错误。

解决方案 #

方案1：拆分SQL语句分别执行（推荐） #

将两条SQL拆开，分两次调用execute执行，无需修改任何连接配置，稳定性最高，修改后的代码如下：

import mysql.connector

cnx = mysql.connector.connect(user='XXXX', password='XXXXX',
                              host='XXXXXXXX',
                              database='sql4456946')
cursor = cnx.cursor()

# 拆分SQL分别执行
cursor.execute("SET @lastid = (SELECT MAX(`id`) FROM `stand`);")
cursor.execute("UPDATE `stand` SET `price` = 9999 WHERE `id` = @lastid")

cnx.commit()
# 操作完成后记得关闭游标和连接
cursor.close()
cnx.close()

方案2：开启多语句执行支持 #

如果确实需要单次执行多条SQL，可以在调用execute时添加multi=True参数，执行后需要主动消费所有返回的结果集，避免连接状态异常：

import mysql.connector

cnx = mysql.connector.connect(user='XXXX', password='XXXXX',
                              host='XXXXXXXX',
                              database='sql4456946')
cursor = cnx.cursor()

maxID = ("SET @lastid = (SELECT MAX(`id`) FROM `stand`); "
         "UPDATE `stand` SET `price` = 9999 WHERE `id` = @lastid")
# 开启多语句执行
results = cursor.execute(maxID, multi=True)
# 消费所有结果集
for res in results:
    pass

cnx.commit()
cursor.close()
cnx.close()

内容的提问来源于stack exchange，提问作者Miguel Rodrigues