Use a transaction context manager for two inserts

By Batu · English technical notes

A connection can be the transaction boundary for a small unit of work. Here, the two inserts create two numbered attendees. The with con: block completes normally, so Python's sqlite3.Connection context-manager behavior commits the open transaction on exit. The following query orders by id, making the displayed result independent of any unspecified row order.

The assertions check both the values and their order after the block has exited. That demonstrates the example's intended outcome in this single connection; it does not test durability after a crash, concurrent writers, or every possible database error. The context manager also does not close the connection, so con.close() remains explicit.

This example uses only the Python standard library and requires Python 3.6 or later because its output uses an f-string. No newer sqlite3 API is required. Python documents that a connection context manager commits on normal completion and rolls back when an uncaught exception leaves the block. SQLite documents that writes occur within transactions.

AI-assistance disclosure: this article was drafted with AI assistance and should be adapted to an application's error-handling requirements.

Sources: Python sqlite3 connection context manager, Python formatted string literals, and SQLite transactions.

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE attendee(id INTEGER PRIMARY KEY, name TEXT NOT NULL)")

with con:
    con.execute("INSERT INTO attendee(name) VALUES (?)", ("Ada",))
    con.execute("INSERT INTO attendee(name) VALUES (?)", ("Linus",))

rows = con.execute("SELECT id, name FROM attendee ORDER BY id").fetchall()
assert rows == [(1, "Ada"), (2, "Linus")]
print(f"rows: {rows}")

con.close()

rows: [(1, 'Ada'), (2, 'Linus')]