When does sqlite3.Cursor.executemany beat a loop of execute calls, and what will it refuse to run?

### What executemany is `sqlite3.Cursor.executemany(sql, seq_of_parameters)` compiles one statement and runs it once per parameter set. Its second argument is any iterable of parameter containers — a list of tuples, a list of dicts for named placeholders, or a **generator**, which matters because it means a large batch never has to exist in memory at once: ```python rows = ((f"case-{n:03d}.pdf", n) for n in range(340)) cur = con.executemany("INSERT INTO job VALUES(?, ?)", rows) print(cur.rowcount) # 340 ``` The saving over a Python `for` loop calling `sqlite3.Cursor.execute` is real but narrower than people assume: the statement is parsed and compiled once instead of being looked up per iteration, and the per-row Python overhead of a bound-method call plus argument packing disappears. What it does **not** do is change transactions or batching semantics at the database level — it is still one statement execution per parameter set. ### The refusal you must know `executemany` accepts only statements that do not return rows. Give it a SELECT and it raises: ```python con.executemany("SELECT doc FROM job WHERE pages = ?", [(1,), (2,)]) # sqlite3.ProgrammingError: executemany() can only execute DML statements. ``` This is the right design — a cursor has one result set, so "run this query 340 times" has nowhere to put 340 of them — but it surprises people who expect the rows to simply be discarded, which is what older behaviour allowed. If you want per-key results, loop with `execute` and consume each result before the next call, or restructure into a single statement that takes the whole key set at once. Two related limits: `sqlite3.Cursor.lastrowid` is `None` after `executemany`, because there is no single "last row" that is meaningful for the caller, and `sqlite3.Cursor.executescript` is not the batching tool either — it runs a multi-statement script and accepts **no** parameters at all, so it is for schema setup, never for data. ### The throughput lesson: it is the transaction, not the call This is where a senior answer separates itself. Consider a document-conversion queue that persists the outcome of a 340-case regression pack after each run. The naive version calls `execute` in a loop and `sqlite3.Connection.commit` after every row, and it is slow by orders of magnitude — each commit is a durable flush. Switching to `executemany` appears to fix it, but the fix is mostly that the loop now sits inside one transaction that commits once at the end. Under the module's default legacy transaction control, `sqlite3` opens a transaction implicitly before a DML statement and holds it until you call `commit`. So a plain `execute` loop followed by a single `commit` is already within a couple of percent of `executemany`; a loop that commits per row is catastrophic no matter which call you use. Python 3.12 added `sqlite3.Connection.autocommit` (PEP 249-style control) alongside the legacy `isolation_level` mechanism, and being explicit about which one you are using is the difference between a service whose durability is designed and one whose durability is inherited. The practical shape for a batch of a few hundred rows is: one `executemany`, one `commit`, and — if the batch is very large — chunking into commits of some thousands of rows so a failure does not force replaying everything and so the write transaction does not block readers for the whole run. ### Failure semantics If a parameter set part-way through violates a constraint, the exception (`sqlite3.IntegrityError`, a subclass of `sqlite3.DatabaseError`) propagates out of the `executemany` call and the **remaining sets are not attempted**. The rows applied before the failure remain in the open transaction; only `sqlite3.Connection.rollback` discards them. So the atomicity you get is the transaction's, never the call's — an `executemany` that raises leaves a partially-applied batch unless you handle it, which for a queue means either rolling the whole batch back and retrying it, or resolving the conflict in SQL so no row raises in the first place. Also worth saying: `cur.rowcount` after a successful `executemany` is the total across all parameter sets, which makes it a usable assertion in a test ("340 rows written"), and a useless one for detecting *which* set failed. ### How to answer Lead with the mechanism — one compile, many binds, any iterable including a generator. State the refusal — DML only, `ProgrammingError` on a row-returning statement, `lastrowid` is `None`. Then make the senior point: the batching win people attribute to `executemany` is mostly the transaction boundary, and the batch's atomicity and retry story belong to the transaction, not to the call.