Python Practice › SQLite Programs › Program 13
Transaction with rollback
Write a Python program that transfers money between accounts and rolls back on error.
Output
done [('Asha', 300), ('Ravi', 300)]
failed, rolled back [('Asha', 300), ('Ravi', 300)]Approach
with conn:commits on success and rolls back on an exception.
Solution
import sqlite3 conn = sqlite3.connect(":memory:")conn.execute("CREATE TABLE acc (name TEXT PRIMARY KEY, balance INTEGER CHECK (balance >= 0))")conn.executemany("INSERT INTO acc VALUES (?, ?)", [("Asha", 500), ("Ravi", 100)])conn.commit() def transfer(src, dst, amount): try: with conn: conn.execute("UPDATE acc SET balance = balance + ? WHERE name = ?", (amount, dst)) conn.execute("UPDATE acc SET balance = balance - ? WHERE name = ?", (amount, src)) return "done" except sqlite3.IntegrityError: return "failed, rolled back" print(transfer("Asha", "Ravi", 200), conn.execute("SELECT * FROM acc").fetchall())print(transfer("Ravi", "Asha", 1000), conn.execute("SELECT * FROM acc").fetchall())Output
done [('Asha', 300), ('Ravi', 300)]
failed, rolled back [('Asha', 300), ('Ravi', 300)]Line by line
Line 4
The CHECK constraint makes a negative balance an error.
Lines 11-12
If the second update fails, the first one is undone too.
Learn the concept
New to this topic? Read the lesson, then come back.