7.4 Python Database Transactions & Error Handling

7.4 Python Database Transactions & Error Handling

Database applications often perform multiple operations that must succeed together. A transaction groups related database operations into a logical unit so that changes can be committed when successful or rolled back when an error occurs.

Python database programs also need proper exception handling to deal with connection failures, invalid SQL, constraint violations, unavailable databases, and other runtime problems.

1. Learning Objectives

By the end of this lesson, you will be able to:

• Understand database transactions.
• Explain atomicity and data consistency.
• Use commit() correctly.
• Use rollback() after failures.
• Handle MySQL exceptions in Python.
• Design multi-step database transactions.
• Validate transaction results.
• Handle constraint violations.
• Manage database connections safely.
• Use transactions in inventory workflows.
• Separate expected and unexpected errors.
• Build reusable transaction functions.
• Implement reliable database automation.

2. What Is a Database Transaction?

A transaction is a logical sequence of database operations treated as one unit of work.

For example, transferring money between two accounts may involve two database updates:

Account A
    ↓
Subtract amount
    ↓
Account B
    ↓
Add amount

If the first operation succeeds but the second fails, the database must not be left with money removed from one account without being added to the other.

3. Transaction Concept

Start Transaction
       |
       v
Operation 1
       |
       v
Operation 2
       |
       v
Operation 3
       |
       v
All Successful?
    /       \
  Yes        No
   |          |
   v          v
 COMMIT    ROLLBACK

4. Why Transactions Matter

Without transaction management, a failure occurring halfway through a multi-step operation can leave inconsistent data.

5. Transaction Properties — ACID

Relational database transactions are commonly discussed using the ACID properties.

6. Basic Transaction Example

try:

    cursor.execute(
        """
        INSERT INTO products
        (name, price)
        VALUES (%s, %s)
        """,
        (
            "Laptop",
            850.00
        )
    )

    connection.commit()

except Exception:

    connection.rollback()

If the operation succeeds, the changes are committed. If an exception occurs before the commit, the transaction can be rolled back.

7. The Role of commit()

connection.commit()

commit() confirms the pending transaction changes and makes them persistent according to the database's transaction semantics.

It is particularly important after data-changing statements such as:

• INSERT
• UPDATE
• DELETE

8. The Role of rollback()

connection.rollback()

rollback() discards uncommitted changes in the current transaction.

try:

    cursor.execute(
        """
        UPDATE products
        SET price = %s
        WHERE id = %s
        """,
        (
            900.00,
            1
        )
    )

    connection.commit()

except Exception:

    connection.rollback()

    raise

9. One Operation vs Multiple Operations

The importance of transactions becomes especially clear when several operations belong to one business process.

Operation 1
    ↓
Operation 2
    ↓
Operation 3
    ↓
Commit

If Operation 3 fails, the application can roll back the preceding uncommitted changes.

10. Multi-Step Transaction

try:

    cursor.execute(
        """
        UPDATE accounts
        SET balance = balance - %s
        WHERE id = %s
        """,
        (
            500,
            1
        )
    )

    cursor.execute(
        """
        UPDATE accounts
        SET balance = balance + %s
        WHERE id = %s
        """,
        (
            500,
            2
        )
    )

    connection.commit()

except Exception:

    connection.rollback()

    raise

Both account updates are treated as part of the same logical operation.

11. Error Handling with MySQL Connector

import mysql.connector


try:

    connection = (
        mysql.connector.connect(
            host="localhost",
            user="app_user",
            password="your_password",
            database="inventory_db"
        )
    )

except mysql.connector.Error as error:

    print(
        "Connection failed:",
        error
    )

mysql.connector.Error can be used to catch errors raised by MySQL Connector/Python.

12. Handling Query Errors

try:

    cursor.execute(
        """
        SELECT *
        FROM products
        """
    )

    rows = cursor.fetchall()

except mysql.connector.Error as error:

    print(
        "Query failed:",
        error
    )

13. Handling INSERT Errors

try:

    cursor.execute(
        """
        INSERT INTO products
        (name, price)
        VALUES (%s, %s)
        """,
        (
            "Monitor",
            220.00
        )
    )

    connection.commit()

except mysql.connector.Error as error:

    connection.rollback()

    print(
        "Insert failed:",
        error
    )

14. Handling UPDATE Errors

try:

    cursor.execute(
        """
        UPDATE products
        SET price = %s
        WHERE id = %s
        """,
        (
            250.00,
            1
        )
    )

    connection.commit()

except mysql.connector.Error as error:

    connection.rollback()

    print(
        "Update failed:",
        error
    )

15. Handling DELETE Errors

try:

    cursor.execute(
        """
        DELETE FROM products
        WHERE id = %s
        """,
        (5,)
    )

    connection.commit()

except mysql.connector.Error as error:

    connection.rollback()

    print(
        "Delete failed:",
        error
    )

16. Using try-except-finally

connection = None
cursor = None

try:

    connection = (
        mysql.connector.connect(
            host="localhost",
            user="app_user",
            password="your_password",
            database="inventory_db"
        )
    )

    cursor = (
        connection.cursor()
    )

    cursor.execute(
        "SELECT * FROM products"
    )

    rows = cursor.fetchall()

    for row in rows:

        print(row)

except mysql.connector.Error as error:

    print(
        "Database error:",
        error
    )

finally:

    if cursor is not None:

        cursor.close()

    if connection is not None:

        connection.close()

The finally block is useful for cleanup operations that should be attempted regardless of whether an exception occurred.

17. Separate Connection Errors from Query Errors

try:

    connection = (
        mysql.connector.connect(
            host="localhost",
            user="app_user",
            password="your_password",
            database="inventory_db"
        )
    )

except mysql.connector.Error as error:

    print(
        "Could not connect:",
        error
    )

else:

    try:

        cursor = (
            connection.cursor()
        )

        cursor.execute(
            "SELECT * FROM products"
        )

        rows = cursor.fetchall()

        for row in rows:

            print(row)

    except mysql.connector.Error as error:

        print(
            "Query failed:",
            error
        )

    finally:

        connection.close()

18. Expected vs Unexpected Errors

19. Constraint Violation Example

Suppose a table contains a unique email address.

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(150) UNIQUE
)

Attempting to insert an existing email may cause a database error.

try:

    cursor.execute(
        """
        INSERT INTO users
        (email)
        VALUES (%s)
        """,
        (
            "user@example.com",
        )
    )

    connection.commit()

except mysql.connector.Error as error:

    connection.rollback()

    print(
        "Could not create user:",
        error
    )

20. Validate Before the Transaction

Application-level validation should happen before executing a transaction whenever possible.

quantity = 10

if quantity <= 0:

    raise ValueError(
        "Quantity must be positive"
    )

This avoids unnecessary database operations for obviously invalid input.

21. Transaction with Validation

def add_product(
    connection,
    name,
    price,
    quantity
):

    if not name.strip():

        raise ValueError(
            "Product name is required"
        )

    if price < 0:

        raise ValueError(
            "Price cannot be negative"
        )

    if quantity < 0:

        raise ValueError(
            "Quantity cannot be negative"
        )


    cursor = connection.cursor()

    try:

        cursor.execute(
            """
            INSERT INTO products
            (name, price, quantity)
            VALUES (%s, %s, %s)
            """,
            (
                name,
                price,
                quantity
            )
        )

        connection.commit()

        return cursor.lastrowid

    except mysql.connector.Error:

        connection.rollback()

        raise

    finally:

        cursor.close()

22. Transaction Helper Function

Reusable transaction helpers can reduce repeated error-handling code in larger applications.

def execute_transaction(
    connection,
    operation
):

    try:

        result = operation(
            connection
        )

        connection.commit()

        return result

    except Exception:

        connection.rollback()

        raise

23. Using a Transaction Helper

def create_order(
    connection
):

    cursor = (
        connection.cursor()
    )

    try:

        cursor.execute(
            """
            INSERT INTO orders
            (customer_id)
            VALUES (%s)
            """,
            (10,)
        )

        order_id = (
            cursor.lastrowid
        )

        return order_id

    finally:

        cursor.close()


order_id = execute_transaction(
    connection,
    create_order
)

24. Inventory Transaction

Inventory operations are a practical example of a transaction because stock changes and sales records often need to remain synchronized.

try:

    cursor.execute(
        """
        UPDATE products
        SET quantity = quantity - %s
        WHERE id = %s
        AND quantity >= %s
        """,
        (
            2,
            1,
            2
        )
    )

    if cursor.rowcount != 1:

        raise ValueError(
            "Insufficient stock"
        )

    cursor.execute(
        """
        INSERT INTO sales
        (product_id, quantity)
        VALUES (%s, %s)
        """,
        (
            1,
            2
        )
    )

    connection.commit()

except Exception:

    connection.rollback()

    raise

Here, the stock reduction and sales record are part of the same logical transaction.

25. Why rowcount Matters

An SQL statement can execute successfully but still affect zero rows.

cursor.execute(
    """
    UPDATE products
    SET quantity = quantity - %s
    WHERE id = %s
    AND quantity >= %s
    """,
    (
        5,
        100,
        5
    )
)

print(
    "Rows affected:",
    cursor.rowcount
)

Application logic can inspect the affected-row count to determine whether an expected operation actually occurred.

26. Rollback After Business Logic Failure

Rollback is not limited to SQL exceptions. An application can also explicitly raise an exception when a business rule is violated.

try:

    cursor.execute(
        """
        UPDATE accounts
        SET balance = balance - %s
        WHERE id = %s
        AND balance >= %s
        """,
        (
            500,
            1,
            500
        )
    )

    if cursor.rowcount != 1:

        raise ValueError(
            "Insufficient account balance"
        )

    connection.commit()

except Exception:

    connection.rollback()

    raise

27. Transaction Boundaries

A transaction should represent a meaningful unit of business work.

Too Small

INSERT A
COMMIT

INSERT B
COMMIT

INSERT C
COMMIT


Logical Unit

INSERT A
UPDATE B
INSERT C
COMMIT

The correct transaction boundary depends on the application's business requirements.

28. Transaction and Business Logic

User Request
     ↓
Validate Input
     ↓
Begin Logical Operation
     ↓
Database Operation 1
     ↓
Database Operation 2
     ↓
Business Rule Check
     ↓
Success?
  /       \
Yes       No
 |         |
Commit   Rollback
 |         |
Return   Report Error

29. Database Error Information

try:

    cursor.execute(
        "SELECT * FROM unknown_table"
    )

except mysql.connector.Error as error:

    print(
        "Error:",
        error
    )

During development, useful database error information can help diagnose problems. Production applications should avoid exposing sensitive internal database details directly to end users.

30. Logging Database Errors

import logging


logging.basicConfig(
    level=logging.ERROR
)


try:

    cursor.execute(
        "SELECT * FROM products"
    )

except mysql.connector.Error:

    logging.exception(
        "Database query failed"
    )

Logging is useful in scheduled automation, background processes, and production applications where an operator needs diagnostic information.

31. Retry Strategy

Some failures, such as temporary network or service interruptions, may be recoverable. A retry strategy should be used carefully.

import time


for attempt in range(3):

    try:

        connection = (
            mysql.connector.connect(
                host="localhost",
                user="app_user",
                password="your_password",
                database="inventory_db"
            )
        )

        break

    except mysql.connector.Error:

        if attempt == 2:

            raise

        time.sleep(2)

32. Idempotency

An operation is idempotent when repeating it produces the same intended final state rather than repeatedly applying an unintended side effect.

For example, setting a product price to a specific value is generally easier to retry safely than repeatedly adding an amount to the price.

UPDATE products
SET price = 500
WHERE id = 1;

Repeating the statement still aims for the same final price.

33. Savepoints

A savepoint provides a point within a transaction to which changes can potentially be rolled back without discarding the entire transaction.

cursor.execute(
    "SAVEPOINT before_discount"
)

MySQL supports SQL transaction statements such as SAVEPOINT and ROLLBACK TO SAVEPOINT.

cursor.execute(
    "ROLLBACK TO SAVEPOINT before_discount"
)

Savepoints are useful in more advanced transaction workflows where partial rollback is required.

34. Commit After a Savepoint

try:

    cursor.execute(
        """
        UPDATE products
        SET price = price * 0.90
        WHERE category = %s
        """,
        ("Accessories",)
    )

    cursor.execute(
        "SAVEPOINT discount_applied"
    )

    cursor.execute(
        """
        UPDATE products
        SET quantity = quantity + 10
        WHERE category = %s
        """,
        ("Accessories",)
    )

    connection.commit()

except Exception:

    connection.rollback()

    raise

35. Transaction Isolation

Isolation controls how concurrent transactions interact with each other.

MySQL supports multiple transaction isolation levels, including:

• READ UNCOMMITTED
• READ COMMITTED
• REPEATABLE READ
• SERIALIZABLE

The appropriate isolation level depends on application requirements, concurrency, correctness, and performance.

36. Concurrency Example

Imagine two processes attempting to modify the same inventory item simultaneously.

Process A
    ↓
Read Stock
    ↓
Modify Stock
    ↓
Commit


Process B
    ↓
Read Stock
    ↓
Modify Stock
    ↓
Commit

Without suitable concurrency controls, applications can encounter race conditions or incorrect results.

37. Atomic Stock Update

cursor.execute(
    """
    UPDATE products
    SET quantity = quantity - %s
    WHERE id = %s
    AND quantity >= %s
    """,
    (
        3,
        1,
        3
    )
)

if cursor.rowcount != 1:

    raise ValueError(
        "Stock unavailable"
    )

connection.commit()

Putting the stock condition into the SQL statement helps make the check and update part of one database operation.

38. Connection State

if connection.is_connected():

    print(
        "Database connection is active"
    )

Applications that run for long periods should account for connection failures and appropriate reconnection strategies.

39. Safe Connection Cleanup

connection = None
cursor = None

try:

    connection = (
        mysql.connector.connect(
            host="localhost",
            user="app_user",
            password="your_password",
            database="inventory_db"
        )
    )

    cursor = (
        connection.cursor()
    )

    cursor.execute(
        "SELECT * FROM products"
    )

    rows = cursor.fetchall()

except mysql.connector.Error as error:

    print(
        "Database error:",
        error
    )

finally:

    if cursor is not None:

        cursor.close()

    if connection is not None:

        connection.close()

40. Complete Transaction Function

def update_stock(
    connection,
    product_id,
    quantity
):

    cursor = (
        connection.cursor()
    )

    try:

        cursor.execute(
            """
            UPDATE products
            SET quantity = quantity + %s
            WHERE id = %s
            """,
            (
                quantity,
                product_id
            )
        )

        if cursor.rowcount != 1:

            raise ValueError(
                "Product not found"
            )

        connection.commit()

    except Exception:

        connection.rollback()

        raise

    finally:

        cursor.close()

41. Transaction Function with Business Rules

def sell_product(
    connection,
    product_id,
    quantity
):

    if quantity <= 0:

        raise ValueError(
            "Quantity must be positive"
        )

    cursor = (
        connection.cursor()
    )

    try:

        cursor.execute(
            """
            UPDATE products
            SET quantity = quantity - %s
            WHERE id = %s
            AND quantity >= %s
            """,
            (
                quantity,
                product_id,
                quantity
            )
        )

        if cursor.rowcount != 1:

            raise ValueError(
                "Insufficient stock or product not found"
            )

        cursor.execute(
            """
            INSERT INTO sales
            (product_id, quantity)
            VALUES (%s, %s)
            """,
            (
                product_id,
                quantity
            )
        )

        connection.commit()

    except Exception:

        connection.rollback()

        raise

    finally:

        cursor.close()

42. Error Handling Strategy

Validate
   ↓
Try
   ↓
Execute SQL
   ↓
Check Result
   ↓
Commit
   ↓
Return Success

Exception
   ↓
Rollback
   ↓
Log
   ↓
Return Safe Error

43. User-Friendly Error Messages

Technical database exceptions should normally be translated into meaningful application-level messages.

try:

    create_user(
        connection,
        email
    )

except mysql.connector.Error:

    print(
        "The account could not be created. "
        "Please verify the information."
    )

Detailed technical information can be logged separately for administrators or developers.

44. Do Not Hide Exceptions

Avoid silently swallowing exceptions.

try:

    cursor.execute(
        "UPDATE products SET price = 100"
    )

except Exception:

    pass

This makes failures difficult to detect and troubleshoot.

A better approach is to log, handle, or re-raise the exception.

try:

    cursor.execute(
        """
        UPDATE products
        SET price = %s
        WHERE id = %s
        """,
        (
            100,
            1
        )
    )

    connection.commit()

except Exception:

    connection.rollback()

    raise

45. Logging with Context

import logging


logger = logging.getLogger(
    __name__
)


try:

    cursor.execute(
        """
        UPDATE products
        SET price = %s
        WHERE id = %s
        """,
        (
            100,
            1
        )
    )

    connection.commit()

except mysql.connector.Error:

    connection.rollback()

    logger.exception(
        "Failed to update product"
    )

    raise

46. Transaction Pattern for Automation

Scheduled Python automation frequently performs multiple database operations.

Read External Data
        ↓
Validate Data
        ↓
Start Database Work
        ↓
Insert / Update
        ↓
Verify
        ↓
Commit
        ↓
Generate Report

If the database update fails, the automation should not report the operation as successfully completed.

47. Transaction + Data Pipeline

CSV / API
   ↓
Python
   ↓
Validation
   ↓
MySQL Transaction
   ↓
Commit
   ↓
pandas
   ↓
Analysis
   ↓
Report

48. Common Transaction Mistakes

49. Transaction Best Practices

1. Keep transactions focused on one logical business operation.
2. Validate input before starting expensive database work.
3. Use parameterized SQL.
4. Commit only after all required operations succeed.
5. Roll back when a transaction fails.
6. Check affected rows when business logic depends on the result.
7. Close cursors and connections reliably.
8. Log important failures.
9. Avoid exposing sensitive database details to users.
10. Design retry logic carefully.
11. Consider concurrency requirements.

50. Mini Project — Order Processing Transaction

Build a transaction-based order processing system.

Tables

products
-------------------------
id
name
price
quantity


orders
-------------------------
id
customer_id
order_date
total_amount


order_items
-------------------------
id
order_id
product_id
quantity
unit_price

Workflow

Receive Order
     ↓
Validate Items
     ↓
Check Stock
     ↓
Create Order
     ↓
Create Order Items
     ↓
Reduce Stock
     ↓
Calculate Total
     ↓
Commit
     ↓
Return Order ID

If any required step fails, the transaction should be rolled back.

51. Order Processing Example

try:

    cursor.execute(
        """
        INSERT INTO orders
        (customer_id, total_amount)
        VALUES (%s, %s)
        """,
        (
            101,
            0
        )
    )

    order_id = (
        cursor.lastrowid
    )


    cursor.execute(
        """
        UPDATE products
        SET quantity = quantity - %s
        WHERE id = %s
        AND quantity >= %s
        """,
        (
            2,
            10,
            2
        )
    )


    if cursor.rowcount != 1:

        raise ValueError(
            "Product unavailable"
        )


    cursor.execute(
        """
        INSERT INTO order_items
        (order_id, product_id, quantity, unit_price)
        VALUES (%s, %s, %s, %s)
        """,
        (
            order_id,
            10,
            2,
            250
        )
    )


    connection.commit()

except Exception:

    connection.rollback()

    raise

52. Transaction Testing

A transaction implementation should be tested not only when everything succeeds, but also when individual steps fail.

53. Practical Examination Tasks

54. Interview Questions

55. Examination MCQs

56. Coding Challenge — Reliable Order Transaction

57. Self-Assessment Checklist

You should now be able to:

• ☐ Explain database transactions.
• ☐ Explain the ACID properties.
• ☐ Use commit().
• ☐ Use rollback().
• ☐ Handle MySQL exceptions.
• ☐ Use try-except-finally.
• ☐ Validate input before database operations.
• ☐ Check affected rows.
• ☐ Implement multi-step transactions.
• ☐ Handle inventory transactions.
• ☐ Understand transaction boundaries.
• ☐ Understand savepoints.
• ☐ Recognize concurrency considerations.
• ☐ Design safe retry strategies.
• ☐ Log database failures.
• ☐ Build reliable database automation.

58. Key Takeaway