Interview frequency: …
Short answer
Execute a group of changes as an atomic unit: commit when all of it succeeds and rollback if any part fails, and the easiest way is to use the connection as a context manager with conn.
Explanation
Using with conn: does not close the connection, it only commits or rolls back the transaction; you must close the connection yourself or through contextlib.closing. Since Python 3.12 the autocommit attribute gives clearer control that conforms to PEP 249. Keep transactions short because SQLite locks the database for writing during them. Note that a transaction does not protect you from the request being repeated over the network; that requires idempotency.
Code example
import sqlite3
from contextlib import closing
with closing(sqlite3.connect("bank.db")) as conn:
with conn: # BEGIN ... COMMIT Or ROLLBACK
conn.execute("UPDATE accounts SET balance = balance - ? WHERE id = ?", (100, 1))
conn.execute("UPDATE accounts SET balance = balance + ? WHERE id = ?", (100, 2))Common mistakes
- Assuming with conn closes the connection.
- Performing a money transfer in two separate steps without a single transaction.