PYTHON / DATABASES WITH PYTHON
Transactions, commits, and rollbacks
Group related SQL writes into all-or-nothing transactions in Python, committing on success, rolling back on failure, and undoing parts with savepoints.
What you will learn
- Wrap related writes in one transaction so a failure leaves no half-finished state
- Use `with con:` to commit on success and roll back on any exception
- Know that sqlite3 opens a transaction implicitly before INSERT/UPDATE/DELETE
- Roll back to a SAVEPOINT to undo part of a transaction without discarding all of it
Understanding Transactions, commits, and rollbacks
A transaction is a boundary you draw around several statements so the database treats them as one indivisible change. Moving money between two rows needs two UPDATEs, and a crash between them would destroy or invent money, so the database keeps your changes in a pending state until you say what to do with them. con.commit() makes every change since the transaction began permanent and visible to other connections; con.rollback() throws all of them away and returns the rows to exactly the values they had when the transaction started.
In sqlite3 you rarely type BEGIN, because with the default isolation_level="" the module opens a transaction for you right before the first INSERT, UPDATE, DELETE or REPLACE it sees, and leaves it open until you commit or roll back. That is why a script that inserts rows, prints them back with SELECT, and then exits without committing can look completely successful and still leave an empty table: closing the connection discards an open transaction. con.in_transaction tells you whether one is currently open, which is the fastest way to check whether you still owe a commit.
Because a rollback must be able to restore old values, the database keeps the pre-change version of every touched row for the life of the transaction, and it holds locks on what you wrote. Long transactions therefore block other writers and grow the journal, while committing after every single row gives up atomicity entirely, since each commit is a point of no return. The right size for a transaction is one unit of work that is meaningful to your application, not one statement and not one whole program run.
import sqlite3
con = sqlite3.connect(":memory:")
con.execute(
"CREATE TABLE account("
"name TEXT PRIMARY KEY, "
"balance INTEGER NOT NULL CHECK (balance >= 0))"
)
con.executemany("INSERT INTO account VALUES (?, ?)",
[("alice", 100), ("bob", 50)])
con.commit()
def balances():
return dict(con.execute("SELECT name, balance FROM account ORDER BY name"))
def transfer(src, dst, amount):
try:
con.execute("UPDATE account SET balance = balance - ? WHERE name = ?",
(amount, src))
con.execute("UPDATE account SET balance = balance + ? WHERE name = ?",
(amount, dst))
con.commit()
print("committed transfer of", amount)
except sqlite3.IntegrityError as exc:
con.rollback()
print("rolled back:", type(exc).__name__)
print("start:", balances())
transfer("alice", "bob", 30)
print("after ok:", balances())
transfer("alice", "bob", 500)
print("after bad:", balances())
con.close()A transaction is the unit of all-or-nothing work: until you commit it or roll it back, your changes exist only in your own connection's view.
Worked examples
The connection as a context manager
Shows that `with con:` commits when the block ends normally and rolls the whole block back when an exception escapes it.
import sqlite3
con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE log(id INTEGER PRIMARY KEY, msg TEXT NOT NULL)")
with con:
con.execute("INSERT INTO log(msg) VALUES ('first')")
try:
with con:
con.execute("INSERT INTO log(msg) VALUES ('second')")
con.execute("INSERT INTO log(msg) VALUES (NULL)")
except sqlite3.IntegrityError:
print("block failed")
print([row[0] for row in con.execute("SELECT msg FROM log")])
print("in transaction:", con.in_transaction)
con.close()Example explained
Line 1The first `with con:` block ends without error, so the INSERT of 'first' is committed.
Line 2In the second block the NULL violates NOT NULL, so the context manager rolls back and 'second' disappears even though that statement itself succeeded.
Line 3The exception still propagates out of the block, which is why the try/except is needed to keep running.
Line 4con.in_transaction is False afterwards, confirming the rollback closed the transaction rather than leaving it open.
Partial undo with SAVEPOINT
Uses isolation_level=None to control transactions by hand and discards only the second half of a transaction.
import sqlite3
con = sqlite3.connect(":memory:", isolation_level=None)
con.execute("CREATE TABLE item(name TEXT)")
con.execute("BEGIN")
con.execute("INSERT INTO item VALUES ('keep')")
con.execute("SAVEPOINT risky")
con.execute("INSERT INTO item VALUES ('maybe')")
print("inside:", [r[0] for r in con.execute("SELECT name FROM item")])
con.execute("ROLLBACK TO risky")
con.execute("COMMIT")
print("committed:", [r[0] for r in con.execute("SELECT name FROM item")])
con.close()Example explained
Line 1isolation_level=None turns off the implicit BEGIN, so every transaction statement you see here is one you wrote.
Line 2The SELECT inside the transaction shows both rows because your own connection always reads its own uncommitted writes.
Line 3ROLLBACK TO risky rewinds only to the savepoint, so 'keep' survives while 'maybe' is discarded.
Line 4COMMIT then makes the surviving part durable and releases the savepoint.
Important notes
With sqlite3's default transaction control, a transaction is opened only before DML, so a CREATE TABLE issued when nothing is pending is committed immediately and con.rollback() will not remove it.
A rollback restores row values but not counters or Python state: AUTOINCREMENT sequences keep advancing, so ids can have gaps after a failed transaction, and any variables you already updated in your program stay updated.
Common mistakes
Letting the script end without commit(): sqlite3 discards the open transaction when the connection is closed or garbage collected, so the table is empty on the next run even though the SELECT during that run printed rows.
Committing inside the loop that inserts rows: after row 200 is committed a failure on row 201 cannot be undone, so rollback() leaves you with a partially applied batch.
Assuming `with sqlite3.connect(path) as con:` closes the file: it only commits or rolls back the transaction, and the connection stays open until you call con.close().
Try it yourself
Change, predict, then run
Build an in-memory table stock(item TEXT, qty INTEGER CHECK (qty >= 0)) with one row ('bolt', 5), then write ship(item, n) that decrements qty inside a `with con:` block. Call it with 3 and then with 10, printing the table after each call to show the second call changes nothing.
Open the Python workspaceCheck your understanding
A script inserts 500 rows and calls con.commit() after each insert. It crashes with an IntegrityError while inserting row 300. What does the table contain afterwards?
- 299 rows, because each commit already made the earlier inserts permanent
- 0 rows, because the crash aborted the transaction that held all the inserts
- 300 rows, because the failing insert is committed with the ones before it
- 299 rows that vanish when the connection closes, since no final commit ran
Show answer
Every commit() ends a transaction, so the first 299 inserts are already durable and the failed one is the only work that is discarded. Option 2 is tempting because a single enclosing transaction would indeed lose everything, but committing per row means no such transaction exists by the time the error happens.