Course contentsShow
← Data Science
Lesson 1115 of 2,14528. Working with Databases from PythonFree lesson

Handling Transaction Errors and Exceptions

Learn patterns for catching exceptions within transactions and properly rolling back on failure.

Handling Transaction Errors and Exceptions

You'll learn: How to catch and handle errors that occur during database transactions, ensuring your database stays consistent even when things go wrong.

Why This Matters

When executing multiple related database operations inside a transaction, errors can occur at any step—a constraint violation, a network timeout, or an invalid query. Without proper error handling, your database could be left in an inconsistent state, with some operations committed and others not.

The Core Pattern

When working with transactions in Python, you should wrap your database operations in try-except blocks. If an exception occurs, you explicitly roll back the transaction to undo any changes made so far, then handle the error appropriately (log it, retry, or raise it).

Here's the standard pattern:

import psycopg2

conn = psycopg2.connect("dbname=mydb user=myuser")
conn.autocommit = False  # Explicit transactions

try:
    cursor = conn.cursor()
    cursor.execute("INSERT INTO accounts (id, balance) VALUES (%s, %s)", (1, 1000))
    cursor.execute("UPDATE accounts SET balance = balance - 100 WHERE id = %s", (1,))
    conn.commit()
    print("Transaction successful")
except Exception as e:
    conn.rollback()
    print(f"Transaction failed, rolled back: {e}")
finally:
    cursor.close()
    conn.close()

Think of It Like This

Imagine packing a suitcase for a trip. If halfway through you realize a critical item doesn't fit, you unpack everything (rollback) rather than leaving half-packed (inconsistent state). The try-except ensures you always complete the packing properly or start fresh.

Always Include the Finally Block

The finally block ensures that resources (cursors, connections) are cleaned up whether the transaction succeeds or fails.

Key Takeaway: Always wrap transactional code in try-except blocks, explicitly rolling back on any exception to maintain database consistency and prevent partial updates.