4 ms·
In the past when I've had to insert many rows (for example), I wrapped it inside a transaction. Why and when would someone use executemany() instead? Or does ex
by auxym 9y ago
In the past when I've had to insert many rows (for example), I wrapped it inside a transaction. Why and when would someone use executemany() instead? Or does executemany() do a transaction behind the scenes?
- masklinn 9y agoTransactions are a separate orthogonal concern. What executemany can do is prepare the statement once and call it multiple times with different sets of parameters, whereas execute may be preparing the statement every time it is called (possibly hoping that the database system has a statements cache) or not preparing them at all (and the DBMS only does simple query planning).
- gaius 9y agoWhy and when would someone use executemany() instead? What kills you in this use case is roundtrips - send one row, wait for an acknowledgement (even if you are not committing it yet) then sent one more, and so on and so on. There is a lot of dead time spent in protocol overheads or just idle. A well implemented executemany() sends as many rows at a time as will fit in a network packet and keeps streaming them as fast as it can.