I'm using Connector/Python to insert many rows into a temp table in mysql. The rows are all in a list-of-lists. I perform the insertion like this:
cursor = connection.cursor();
batch = [[1, 'foo', 'bar'],[2, 'xyz', 'baz']]
cursor.executemany('INSERT INTO temp VALUES(?, ?, ?)', batch)
connection.commit()
I noticed that (with many more rows, of course) the performance was extremely poor. Using SHOW PROCESSLIST, I noticed that each insert was executing separately. But the documentation https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-executemany.html says this should be optimized into 1 insert. What's going on?
Answering so other people won't go through the debugging I had to!
I wrote the query modeling it on other queries in our code that used prepared statements and used '?' to indicate parameters. But you can't do that for executemany()! It must use '%s'. Changing to the following:
cursor.executemany('INSERT INTO temp VALUES(%s,%s,%s)', batch)
...led to a hundredfold speed improvement and the optimized single query could be seen using SHOW PROCESSLIST. Beware the standard '?' syntax!