Database Transactions & Atomic Rollbacks
Transactions ensure all-or-nothing atomicity. Using `conn` as a context manager automatically commits on clean exit or calls `conn.rollback()` if an unhandled exception occurs 🛡️.
import sqlite3
conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE balance (user_id INT, amount INT)")
conn.execute("INSERT INTO balance VALUES (1, 500)")
try:
with conn: # Context manager automatically handles commit & rollback! 🛡️
conn.execute("UPDATE balance SET amount = 400 WHERE user_id = 1")
raise RuntimeError("Network failed mid-transfer! 💥")
except RuntimeError:
print("Transaction aborted and rolled back automatically! 🛡️")
print(conn.execute("SELECT amount FROM balance").fetchone()[0]) # Still 500!
# 19. Database Transactions & Atomic Rollbacks Transactions ensure all-or-nothing atomicity. Using `conn` as a context manager automatically commits on clean exit or calls `conn.rollback()` if an unhandled exception occurs 🛡️. ```python import sqlite3 conn = sqlite3.connect(":memory:") conn.execute("CREATE TABLE balance (user_id INT, amount INT)") conn.execute("INSERT INTO balance VALUES (1, 500)") try: with conn: # Context manager automatically handles commit & rollback! 🛡️ conn.execute("UPDATE balance SET amount = 400 WHERE user_id = 1") raise RuntimeError("Network failed mid-transfer! 💥") except RuntimeError: print("Transaction aborted and rolled back automatically! 🛡️") print(conn.execute("SELECT amount FROM balance").fetchone()[0]) # Still 500! ``` Financial transactions and multi-table updates must always be wrapped in atomic transactions to prevent partial writes that corrupt ledger consistency! 🛡️ --- ### Question What happens when an exception is raised inside a `with conn:` database transaction block? 🧠 - [ ] a. Python automatically rolls back all changes made during the transaction block 🛡️ - [ ] b. The database file is corrupted and deleted - [ ] c. Uncommitted changes are permanently committed anyway - [ ] d. The SQLite driver terminates the Python interpreter process --- ### Challenge Create table `ledger (val INT)` with initial `(10)`. Inside `try / except`, wrap a `with conn:` block that executes `UPDATE ledger SET val = 99` then raises `ValueError()`. Catch error. Print final `val` from `ledger`. 🎯 <details> <summary>Solution</summary> ```python import sqlite3 conn = sqlite3.connect(":memory:") conn.execute("CREATE TABLE ledger (val INT)") conn.execute("INSERT INTO ledger VALUES (10)") conn.commit() try: with conn: conn.execute("UPDATE ledger SET val = 99") raise ValueError("Transfer failure") except ValueError: pass res = conn.execute("SELECT val FROM ledger").fetchone()[0] print(res) conn.close() ``` </details>