Make changes atomically

Observe a failed transaction rolling back all of its changes.

This example inserts one valid row and then violates a constraint inside the same transaction. The first insert rolls back too. Catch the error outside the context manager so it can observe the failure. An earlier committed setup remains available.

Run it on your computer

python3 transactions.py

Requires Python 3.12+. The script creates its own in-memory sample. It does not read your browser database or upload anything.

import sqlite3

con = sqlite3.connect(":memory:", autocommit=False)
try:
    with con:
        con.execute("CREATE TABLE stock (item TEXT PRIMARY KEY, quantity INTEGER CHECK(quantity >= 0))")
    try:
        with con:
            con.execute("INSERT INTO stock VALUES (?, ?)", ("Notebook", 5))
            con.execute("INSERT INTO stock VALUES (?, ?)", ("Pencil", -1))
    except sqlite3.IntegrityError:
        print("Batch rolled back")
    print("Rows:", con.execute("SELECT COUNT(*) FROM stock").fetchone()[0])
finally:
    con.close()

Expected output

Batch rolled back
Rows: 0

Try a change

Change the negative quantity to 1. Both inserts should commit and the row count should become 2.

Practice SQL in the browser or use the Python sqlite3 documentation for API details.

Next: Import CSV with validation →