lesson 150 Packaging & Databases · advanced

Transactions: commit or roll back together

Changes to a database are kept only when they are committed. A transaction groups related changes so they are all kept or all undone, which is what stops half an order from being saved.

You'll be able to

Explain what commit and rollback do, and when other connections see a change
Use with conn: to commit on success and roll back on an exception
Decide which writes must succeed or fail together

The idea

Placing an order means several writes: insert the order, insert each line, take each line's quantity off the stock. If the program fails after the second write, the database holds half an order: stock taken for an order that doesn't exist.

A transaction groups writes. With the sqlite3 module's default settings, the first INSERT, UPDATE or DELETE starts one. Until you call conn.commit(), the changes are visible only through your own connection; commit() makes them permanent and visible to everyone. conn.rollback() undoes every change since the last commit. Closing a connection without committing throws the changes away. execute returns a cursor whose rowcount says how many rows an UPDATE or DELETE changed.

Run each example to see what it prints. Running examples doesn't count toward progress.

example-1.py · Who sees a change, and when

import sqlite3

writer = sqlite3.connect("shop.db")
writer.execute("CREATE TABLE stock (sku TEXT PRIMARY KEY, units INTEGER)")
writer.execute("INSERT INTO stock VALUES ('SPO-11681', 12)")
writer.commit()

reader = sqlite3.connect("shop.db")
changed = writer.execute("UPDATE stock SET units = units - 5 WHERE sku = 'SPO-11681'").rowcount
print("rows changed:", changed)
print("reader before commit:", reader.execute("SELECT units FROM stock").fetchone())
writer.commit()
print("reader after commit:", reader.execute("SELECT units FROM stock").fetchone())

writer.execute("UPDATE stock SET units = 0 WHERE sku = 'SPO-11681'")
writer.rollback()
print("after rollback:", writer.execute("SELECT units FROM stock").fetchone())

Writing commit() and rollback() around every group of writes is easy to get wrong. A connection is also a context manager: `with conn:` commits when the block finishes normally and rolls back when an exception leaves it (the exception still propagates, and the connection stays open). A CHECK constraint in the table lets the database itself refuse a bad value with sqlite3.IntegrityError.

example-2.py · An order is saved whole or not at all

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE stock (sku TEXT PRIMARY KEY, units INTEGER CHECK (units >= 0))")
conn.execute("CREATE TABLE orders (order_id TEXT PRIMARY KEY)")
conn.execute("CREATE TABLE order_lines (order_id TEXT, sku TEXT, quantity INTEGER)")
conn.executemany("INSERT INTO stock VALUES (?, ?)", [("SPO-11681", 12), ("TOY-54564", 2)])
conn.commit()

def place_order(conn, order_id, lines):
    with conn:
        conn.execute("INSERT INTO orders VALUES (?)", (order_id,))
        for sku, quantity in lines:
            conn.execute("INSERT INTO order_lines VALUES (?, ?, ?)", (order_id, sku, quantity))
            conn.execute("UPDATE stock SET units = units - ? WHERE sku = ?", (quantity, sku))

place_order(conn, "O200001", [("SPO-11681", 3), ("TOY-54564", 1)])
try:
    place_order(conn, "O200002", [("SPO-11681", 4), ("TOY-54564", 5)])
except sqlite3.IntegrityError as err:
    print("refused:", err)
print(conn.execute("SELECT * FROM stock ORDER BY sku").fetchall())
print(conn.execute("SELECT COUNT(*) FROM order_lines").fetchone()[0], "order lines saved")

When to use it: whenever several writes only make sense together (an order and its stock, a payment and the order's paid amount, a transfer between two accounts). Keep a transaction short: do the slow work, such as reading files or calling an API, before it starts. Mistakes to avoid: forgetting to commit (another connection, or the next run, never sees the change); committing inside a loop of writes that belong together; and catching an exception inside with conn: and carrying on, which commits a half-finished change.

Review

Changes are kept only after commit(); rollback() (or closing without a commit) undoes everything since the last one.
with conn: commits a block that finishes and rolls back one that raises.
Put writes that only make sense together in one transaction; commit independent batches separately.
Next (see HTTP & API Clients): getting data from web APIs over HTTP.

Practise this