Python mysql.connector cursor.execute()和connection.commit()在

Question

尝试使用for循环自动处理MySQL中的表

from mysql.connector import connect, Error
def main():
try:
with connect(host="host", user="user",password="password") as connection:
connection.autocommit = True
no_pk_tables_query = """
select tab.table_schema as database_name,
tab.table_name
from information_schema.tables tab
left join information_schema.table_constraints tco
on tab.table_schema = tco.table_schema
and tab.table_name = tco.table_name
and tco.constraint_type = 'PRIMARY KEY'
where tco.constraint_type is null
and tab.table_schema not in('mysql', 'information_schema', 
'performance_schema', 'sys')
and tab.table_type = 'BASE TABLE'
order by tab.table_schema,
tab.table_name;
"""
tables_to_cure = []
with connection.cursor() as cursor:
cursor.execute(no_pk_tables_query)
for table in cursor:
tables_to_cure.append(table[1])
print(table[1])

for s_table in tables_to_cure:
cure = """
USE mission_impossible;
ALTER TABLE `{}` MODIFY `ID` int(18) NOT NULL auto_increment PRIMARY KEY;
""".format(s_table)
cursor.execute(cure)
print("Cured {}".format(s_table))             
except Error as e:
print(e)
finally:
print("End")
main()

我得到：

引用2014(HY000(：命令不同步；您现在不能运行此命令

如果我在cursor.execute((之后的for循环中添加connection.commit((，我会得到：

_mysql_connector。MySQLInterfaceError：命令不同步；您现在不能运行此命令

这是否意味着我将不得不在循环中使用新的连接而不是游标？我查找了它，发现了一些方法，如fetchall()和nextset()，但它们似乎可以做其他事情，而不仅仅是刷新当前的光标数据。使用connection.autocommit = True似乎也不起作用，因为出现了相同的错误。使用类似sleep()的东西也没有帮助。我在这里做错了什么？

编辑

摆脱尝试/例外没有帮助：

File "/usr/local/lib/python3.8/dist-packages/mysql/connector/connection_cext.py", line 523, in cmd_query
self._cmysql.query(query,
_mysql_connector.MySQLInterfaceError: Commands out of sync; you can't run this command now
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "db.py", line 40, in <module>
main()
File "db.py", line 36, in main
cursor.execute(cure)
File "/usr/local/lib/python3.8/dist-packages/mysql/connector/cursor_cext.py", line 269, in execute
result = self._cnx.cmd_query(stmt, raw=self._raw,
File "/usr/local/lib/python3.8/dist-packages/mysql/connector/connection_cext.py", line 528, in cmd_query
raise errors.get_mysql_exception(exc.errno, msg=exc.msg,
mysql.connector.errors.DatabaseError: 2014 (HY000): Commands out of sync; you can't run this command now

已修复：

似乎我终于明白了，需要使用fetchall()从光标中获取结果，而不是直接将光标作为迭代器进行寻址。

with connection.cursor() as cursor:
cursor.execute(no_pk_tables_query)
rows = cursor.fetchall()
with connection.cursor() as cursor:
for table in rows:
try:
print(table[1])
cure = """
ALTER TABLE `{}` MODIFY `ID` int(18) NOT NULL auto_increment PRIMARY KEY;
""".format(table[1])
cursor.execute(cure)
res = cursor.fetchall()
print(res)
except Error as e:
print(e)

谢谢大家

Answer 1

以下是一些示例代码，展示了；命令不同步"；可能发生错误：

from mysql.connector import connect, Error
# replace asterisks in the CONFIG dictionary with your data
CONFIG = {
'user': '*',
'password': '*',
'host': '*',
'database': '*',
'autocommit': False
}
try:
with connect(**CONFIG) as conn:
try:
with conn.cursor() as cursor:
cursor.execute('select * from ips')
# cursor.fetchall()
finally:
conn.commit()
except Error as e:
print(e)

解释：

该代码从一个名为"行"的表中选择所有行；ips"；其内容在此无关。

现在，请注意，我们不会尝试获取行集(fetchall被注释掉(。然后，我们尝试提交事务(即使没有对表进行任何更改(。

这将引起"；命令不同步"；错误

但是，如果我们去掉注释行并获取行集(fetchall(，就不会出现这个问题。

显式获取行集相当于在光标上迭代。

如果我们将autocommit参数更改为True并删除显式commit((，我们将得到另一个错误：-"；找到未读结果"；。

换句话说，MySQL似乎需要无论何时选择任何内容，都必须获得行集(或迭代光标(！

请注意，即使启用了自动提交(True(，也允许显式调用commit((

解决方案：

确保客户端应用程序在SELECT之后在整个游标上迭代，或者在CONFIG字典中添加："consumer_results"：True