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?

解决方案

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.