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.