Use executemany inside one transaction

By Batu · English technical notes

executemany() applies one parameterized DML statement to every parameter set in an iterable. This example supplies three (name, points) pairs and performs that batch inside one connection context-manager block. With the module’s default legacy transaction control, the first insert opens the transaction; on a normal exit, with con: commits it, while an exception leaving the block causes a rollback.

The cursor returned by executemany() reports a row count of three for this successful batch. The SELECT orders the rows by points, which makes the output deterministic, and the assertion verifies every stored tuple rather than only the count. Python documents that Cursor.executemany() repeatedly executes DML for each parameter set. Its connection context-manager guide describes the commit and rollback behavior without closing the connection.

A transaction gives the statements in its scope an atomic commit-or-rollback boundary; it does not make arbitrary data valid or prescribe retries for lock errors. For a large input, an iterator can avoid retaining all parameter tuples in memory. This example uses no newer Python-specific API and runs wholly in memory. AI assistance was used to draft this article.

Example

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE score (name TEXT PRIMARY KEY, points INTEGER)")
entries = [("Ada", 8), ("Bo", 5), ("Cy", 9)]

with con:
    cursor = con.executemany("INSERT INTO score VALUES (?, ?)", entries)

rows = con.execute(
    "SELECT name, points FROM score ORDER BY points DESC"
).fetchall()

assert cursor.rowcount == 3
assert rows == [("Cy", 9), ("Ada", 8), ("Bo", 5)]

print(f"inserted={cursor.rowcount}")
print(rows)

Expected output:

inserted=3
[('Cy', 9), ('Ada', 8), ('Bo', 5)]