Python使用mysql.connector循环操作MySQL表时触发Commands out of sync错误的问题排查

Let's break down exactly what went wrong in your original code and why the fix with fetchall() works.

The Root Cause of "Commands out of sync" #

This error happens when you try to run a new SQL command on a cursor before it has fully finished reading all results from a previous query.

MySQL's client-server protocol requires that once you execute a query that returns a result set, you have to consume every row of that result set (either by iterating all the way through it, calling fetchall(), or using nextset() for multiple result sets) before you can run another command on the same connection/cursor.

What Was Wrong in Your Original Code? #

In your first version, you did:

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:
    # Trying to run ALTER TABLE on the same cursor
    cursor.execute(cure)

When you iterate over the cursor directly (for table in cursor), the cursor is still in the middle of fetching rows from the server. Even though you've looped through all visible rows, the C extension cursor (the default for mysql.connector) might not have fully finalized the result set with the server under the hood.

So when you try to run the ALTER TABLE command on the same cursor immediately after, the server still thinks the cursor is busy processing the previous result set—hence the "Commands out of sync" error.

Adding connection.commit() didn't help because this isn't a transaction issue; it's a cursor/result set state problem. Autocommit also doesn't affect how the cursor handles result sets.

Why fetchall() Fixes It #

When you call cursor.fetchall(), you pull all rows from the server into local memory immediately. This tells the server that you've fully consumed the result set, freeing up the cursor (and connection) to run new commands.

In your fixed code, you first fetch all rows into a list (rows = cursor.fetchall()), then either close that cursor or switch to a new one (like you did with another with connection.cursor() block). Either way, the original result set is fully processed, so the connection is ready for new queries.

A Couple of Extra Notes #

1. Avoid USE in cursor commands: Your original cure query included USE mission_impossible;—this is unnecessary and can cause issues. Instead, either specify the database when creating the connection (connect(..., database="mission_impossible")) or qualify the table name in the ALTER command: ALTER TABLE mission_impossible.{} ....
2. Reusing vs. creating new cursors: You don't strictly need a new cursor for the ALTER commands, but creating a new one is a clean way to ensure the previous result set is fully closed. Alternatively, you could call cursor.fetchall() first, then reuse the same cursor for the ALTERs.

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