MySQL why cursor.execute(sql, multi=True) does not work but 2 cursor.execute(sql) works?

Question

This code works:

sql = """TRUNCATE TABLE product_groups;"""
cursor.execute(sql)

sql = """INSERT INTO product_groups (origin, type, name, brand, concentration, gender) 
            SELECT origin, type, name, brand, concentration, gender
            FROM products
            GROUP BY origin, type, name, brand, concentration, gender
            ORDER BY brand, name;"""
cursor.execute(sql)

cursor.close()
conn.commit()
conn.close()

this code does not work:

sql = """TRUNCATE TABLE product_groups;
            INSERT INTO product_groups (origin, type, name, brand, concentration, gender) 
            SELECT origin, type, name, brand, concentration, gender
            FROM products
            GROUP BY origin, type, name, brand, concentration, gender
            ORDER BY brand, name;"""
cursor.execute(sql, multi=True)

cursor.close()
conn.commit()
conn.close()

The difference between two codes is just the cursor.execute(). In the first code, we have 2 cursor.execute(sql). In the second code, we have 1 cursor.execute(sql, multi=True) with 2 SQL statements.

Both codes doesn't rise errors, but, the second code does not insert rows.

why just the first code works?

Answer 1

This statement:

cursor.execute(sql, multi=True)

creates an iterator over the results. It looks like it's lazy (i.e., it executes SQL statements only as needed). You're never asking for the results for the second statement, so it is only executing the first one. Try:

for _ in cursor.execute(sql, multi=True): pass

In general it's better to just use separate execute() calls.