Python mysql.connector cursor.execute() and connection.commit() not working in a loop

Question

Trying to automate working process with the tables in MySQL using for-loop

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()

And I get:

quote 2014 (HY000): Commands out of sync; you can't run this command now

If I add connection.commit() inside the for-loop after cursor.execute() I'll get:

_mysql_connector.MySQLInterfaceError: Commands out of sync; you can't run this command now

Does this mean that I'll have to use new connections inside loop instead of cursor? I've looked it up and found some methods like fetchall() and nextset() but they seem to do other things than simply refreshing current cursor data. Using connection.autocommit = True seem not to work either as the same error occurs. Using something like sleep() also doesn't help. What am I doing wrong here?

Edit

Getting rid of try/except didn't help:

  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

Fixed:

Seems like I've finally figured it out, it's needed to get results from the cursor using fetchall() instead of directly addressing the cursor as an iterator.

            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)

Thx everybody

Answer 1

Here's some sample code that shows how the "Commands out of sync" error can occur:

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)

Explanation:

The code selects all rows from a table called "ips" the contents of which are irrelevant here.

Now, note that we do not attempt to get a rowset (fetchall is commented out). We then try to commit the transaction (even though no changes were made to the table).

This will induce the "Commands out of sync" error.

However, if we take out the comment line and fetch the rowset (fetchall) this problem does not arise.

Explicitly fetching the rowset is equivalent to iterating over the cursor.

If we change the autocommit parameter to True and remove the explicit commit(), we get another error:- "Unread result found".

In other words, it seems that MySQL requires you to get the rowset (or iterate over the cursor) whenever you select anything!

Note that even if autocommit is enabled (True) explicit calls to commit() are permitted

Solutions:

Either ensure that the client application iterates over the entire cursor after SELECT or in the CONFIG dictionary add: 'consume_results': True