Managing Transactions, Commits, Rollbacks, and Connections in Python

Course: Python Training

Topic: Managing Transactions, Commits, Rollbacks, and Connections

Course Link: Python Training Course at JustAcademy

Demo Link: Register for a Course Demo

JustAcademy Website: Visit JustAcademy

1. Introduction to Database Transaction Management

Database transaction management is an essential part of Python database programming. It helps applications execute database operations reliably, save successful changes, reverse unsuccessful changes, and manage database connections efficiently.

When a Python application inserts, updates, or deletes information, the changes may remain part of an uncommitted transaction until they are committed. If an error occurs before the transaction is completed, a rollback can undo its uncommitted changes.

For example, consider a banking application that transfers money from one account to another. The application must deduct money from the sender and add the same amount to the receiver. If only one operation succeeds, the account balances may become inconsistent. A transaction allows both operations to be handled as one logical unit.

Learning Objectives

• Understand database transactions and their purpose.
• Learn the ACID properties of transactions.
• Understand database connections and cursors.
• Use the commit() method to save changes.
• Use the rollback() method to undo uncommitted changes.
• Manage connections and cursors safely.
• Use context managers for transaction handling.
• Handle database errors using exception handling.
• Understand autocommit behavior and transaction boundaries.
• Build practical Python applications that use transactions correctly.

2. What Is a Database Transaction?

A database transaction is a group of database operations treated as one logical unit of work. A transaction is commonly used when several operations must succeed together to maintain data consistency.

For example, an online shopping application may need to:

1. Create a customer order.
2. Insert the products associated with the order.
3. Reduce the available inventory.
4. Record the payment status.

If a critical operation fails, the application may need to roll back the related database changes so that incomplete information is not saved.

Example of a Transaction

import sqlite3

connection = sqlite3.connect("shop.db")

try:
    connection.execute(
        "INSERT INTO orders (customer_id, total_amount) VALUES (?, ?)",
        (101, 1500.00)
    )

    connection.execute(
        "UPDATE inventory SET quantity = quantity - ? WHERE product_id = ?",
        (2, 10)
    )

    connection.commit()
    print("Transaction completed successfully")

except sqlite3.Error as error:
    connection.rollback()
    print("Transaction failed:", error)

finally:
    connection.close()

This example assumes that the orders and inventory tables already exist and that the supplied values are valid. The transaction commits after both statements execute successfully. If a SQLite database error occurs, the application rolls back the uncommitted changes.

3. Understanding the ACID Properties

Reliable relational database transactions are commonly described using the ACID properties. These properties help maintain consistency when operations succeed, fail, or run concurrently.

Why ACID Matters

• It helps prevent incomplete updates.
• It supports reliable financial and business operations.
• It helps protect relationships between database records.
• It provides a framework for handling failures and concurrent activity.

4. Understanding Database Connections

A database connection is an object that allows a Python program to communicate with a database. Through a connection, the application can execute SQL statements, manage transactions, and access query results.

In Python, database connections are commonly created using a database-specific library such as sqlite3 for SQLite or a compatible driver for MySQL and PostgreSQL.

Creating a SQLite Connection

import sqlite3

connection = sqlite3.connect("college.db")

print("Database connected successfully")

connection.close()

The sqlite3.connect() function opens the database file or creates it when appropriate. The close() method releases the connection's resources.

Using an In-Memory Database

import sqlite3

connection = sqlite3.connect(":memory:")

connection.execute("""
    CREATE TABLE students (
        student_id INTEGER PRIMARY KEY,
        name TEXT NOT NULL
    )
""")

print("Temporary database created")

connection.close()

The :memory: database is useful for experiments and testing. Its data is temporary and disappears when the connection is closed.

5. Understanding Cursors

A cursor is an object used to execute SQL statements and retrieve query results. With SQLite, statements can be executed directly on the connection or through a cursor created by connection.cursor().

Creating and Using a Cursor

import sqlite3

connection = sqlite3.connect("college.db")
cursor = connection.cursor()

cursor.execute("""
    CREATE TABLE IF NOT EXISTS students (
        student_id INTEGER PRIMARY KEY,
        name TEXT NOT NULL
    )
""")

connection.commit()

cursor.close()
connection.close()

Connection vs. Cursor

6. What Is Commit in Python?

The commit() method finalizes the current pending transaction and saves its changes according to the database's transaction rules. It is commonly used after one or more successful insert, update, or delete operations.

Basic Commit Example

import sqlite3

connection = sqlite3.connect("college.db")

connection.execute("""
    CREATE TABLE IF NOT EXISTS employees (
        employee_id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        salary REAL NOT NULL
    )
""")

connection.execute(
    "INSERT INTO employees (name, salary) VALUES (?, ?)",
    ("Amit Sharma", 45000)
)

connection.commit()

print("Employee record saved")

connection.close()

How commit() Works

1. The application connects to the database.
2. The SQL statement inserts or modifies data.
3. The database processes the statement within its transaction rules.
4. The application calls commit() to finalize pending changes.
5. The connection can then be reused or closed.

When Should You Use commit()?

• After adding records that must be saved.
• After updating existing records.
• After deleting records when the deletion should be finalized.
• After several related operations have completed successfully.

Important: Transaction behavior depends on the database driver and its configuration. In SQLite's standard legacy transaction mode, data-modification statements commonly open transactions implicitly. SQLite's newer autocommit configuration also affects whether explicit commits are necessary or effective.

7. What Is Rollback in Python?

The rollback() method reverses changes made in the current uncommitted transaction. It is useful when an operation fails and the application needs to return the database to the state it had before that transaction's changes.

Basic Rollback Example

import sqlite3

connection = sqlite3.connect("college.db")

try:
    connection.execute(
        "INSERT INTO employees (name, salary) VALUES (?, ?)",
        ("Neha Patel", 50000)
    )

    # Simulate an application error before commit.
    raise ValueError("Unexpected application error")

    connection.commit()

except (sqlite3.Error, ValueError) as error:
    connection.rollback()
    print("Changes rolled back:", error)

finally:
    connection.close()

In this example, the artificial error occurs before commit(), so the pending insert is rolled back. In production code, the exception handler should cover the operations that form the transaction, and errors should be handled or re-raised appropriately.

When Should You Use rollback()?

• When a database statement fails during a transaction.
• When a business rule prevents the operation from completing.
• When one of several related operations fails.
• When an application detects invalid transaction state before committing.

A rollback cannot undo changes that have already been committed. If a committed operation must be reversed, the application generally needs a new transaction that performs a compensating operation.

8. Difference Between Commit and Rollback

9. Managing Transactions with try-except-finally

Python's exception-handling mechanism allows a program to respond to database errors and clean up resources. The try block contains the database operations, except handles errors, and finally performs cleanup.

import sqlite3

connection = sqlite3.connect("college.db")

try:
    connection.execute(
        "INSERT INTO employees (name, salary) VALUES (?, ?)",
        ("Rahul Verma", 42000)
    )

    connection.execute(
        "UPDATE employees SET salary = ? WHERE name = ?",
        (46000, "Rahul Verma")
    )

    connection.commit()
    print("All database changes saved")

except sqlite3.Error as error:
    connection.rollback()
    print("Database operation failed:", error)

finally:
    connection.close()
    print("Database connection closed")

Advantages of This Pattern

• It makes transaction success and failure paths explicit.
• It allows pending changes to be rolled back after database errors.
• It ensures the connection is closed even when an exception occurs.
• It helps avoid unnecessary database locks caused by connections that remain open.

10. Using Context Managers for Transaction Management

A context manager provides a structured way to manage resources and operations. Python's SQLite connection supports the with statement for transaction management.

When a connection context manager exits normally, it commits an open transaction. If an uncaught exception occurs, it rolls back an open transaction. The context manager does not automatically close the connection.

Example: Automatic Commit

import sqlite3

connection = sqlite3.connect("college.db")

with connection:
    connection.execute(
        "INSERT INTO employees (name, salary) VALUES (?, ?)",
        ("Priya Shah", 48000)
    )

connection.close()

print("Transaction completed")

Example: Automatic Rollback on an Exception

import sqlite3

connection = sqlite3.connect("college.db")

try:
    with connection:
        connection.execute(
            "INSERT INTO employees (name, salary) VALUES (?, ?)",
            ("Karan Mehta", 52000)
        )

        raise ValueError("Cancel this transaction")

except ValueError as error:
    print("Transaction cancelled:", error)

finally:
    connection.close()

Because the exception leaves the with connection block, the connection context manager rolls back the open transaction. The exception still propagates to the surrounding handler.

Important Difference: Transaction Context vs. Connection Cleanup

The SQLite connection context manager controls transaction completion; it does not close the connection. If you want a closing context manager, you can use contextlib.closing() together with the connection's transaction context.

import sqlite3
from contextlib import closing

with closing(sqlite3.connect("college.db")) as connection:
    with connection:
        connection.execute(
            "INSERT INTO employees (name, salary) VALUES (?, ?)",
            ("Meera Joshi", 44000)
        )

print("Transaction completed and connection closed")

This pattern commits the transaction when the inner block exits successfully, rolls it back if an uncaught exception occurs, and closes the connection when the outer block exits.

11. Understanding Autocommit Mode

Autocommit describes transaction behavior in which database statements may be committed automatically instead of being grouped into a manually managed transaction. The exact meaning and configuration differ among database drivers.

Python's sqlite3 module supports multiple transaction-control modes. In modern Python versions, the autocommit parameter and attribute provide explicit transaction-control options. The legacy mode uses isolation_level for transaction behavior.

Why Autocommit Matters

• It affects when changes become permanent.
• It influences whether commit() and rollback() have an effect.
• It changes how transaction boundaries should be designed.
• It must be understood before relying on rollback behavior.

Example: Explicit Transaction Control in SQLite

In Python 3.12 and later, the autocommit parameter can be used to select transaction behavior. The following example uses autocommit=False, which follows DB-API-style transaction control and requires explicit commits or rollbacks.

import sqlite3

connection = sqlite3.connect(
    "college.db",
    autocommit=False
)

try:
    connection.execute(
        "INSERT INTO employees (name, salary) VALUES (?, ?)",
        ("Sana Khan", 47000)
    )

    connection.commit()
    print("Changes committed")

except sqlite3.Error:
    connection.rollback()
    raise

finally:
    connection.close()

This specific autocommit parameter requires Python 3.12 or later. For older Python versions, transaction behavior can be managed using the supported legacy settings or the database driver's documented interface.

12. Understanding Transaction Boundaries

A transaction boundary marks the point where a logical unit of work begins and ends. Correct transaction boundaries help ensure that related operations are committed together or rolled back together.

Example: Transaction with Two Inserts

import sqlite3

connection = sqlite3.connect("college.db")

try:
    connection.execute(
        "INSERT INTO employees (name, salary) VALUES (?, ?)",
        ("Aarav Sharma", 41000)
    )

    connection.execute(
        "INSERT INTO employees (name, salary) VALUES (?, ?)",
        ("Isha Gupta", 43000)
    )

    connection.commit()
    print("Both employee records saved")

except sqlite3.Error as error:
    connection.rollback()
    print("Transaction failed:", error)

finally:
    connection.close()

The two inserts belong to the same logical unit of work. If the second operation fails before the commit, the first uncommitted insert can also be rolled back.

13. Practical Example: Bank Transfer Using Transactions

A bank transfer is a classic example of a transaction. Money must be deducted from one account and added to another account. The transaction should not leave only one side of the transfer completed.

Step 1: Create the Accounts Table

import sqlite3

with sqlite3.connect("bank.db") as connection:
    connection.execute("""
        CREATE TABLE IF NOT EXISTS accounts (
            account_id INTEGER PRIMARY KEY,
            account_holder TEXT NOT NULL,
            balance REAL NOT NULL CHECK (balance >= 0)
        )
    """)

    connection.execute(
        "INSERT OR IGNORE INTO accounts VALUES (?, ?, ?)",
        (1, "Aarav", 10000.00)
    )

    connection.execute(
        "INSERT OR IGNORE INTO accounts VALUES (?, ?, ?)",
        (2, "Priya", 5000.00)
    )

print("Accounts table initialized")

Step 2: Implement the Transfer Function

import sqlite3


def transfer_money(sender_id, receiver_id, amount):
    if amount <= 0:
        raise ValueError("Transfer amount must be positive")

    if sender_id == receiver_id:
        raise ValueError("Sender and receiver must be different")

    connection = sqlite3.connect("bank.db")

    try:
        connection.execute("BEGIN")

        sender = connection.execute(
            "SELECT balance FROM accounts WHERE account_id = ?",
            (sender_id,)
        ).fetchone()

        receiver = connection.execute(
            "SELECT balance FROM accounts WHERE account_id = ?",
            (receiver_id,)
        ).fetchone()

        if sender is None or receiver is None:
            raise ValueError("Sender or receiver account does not exist")

        if sender[0] < amount:
            raise ValueError("Insufficient balance")

        debit = connection.execute(
            """
            UPDATE accounts
            SET balance = balance - ?
            WHERE account_id = ? AND balance >= ?
            """,
            (amount, sender_id, amount)
        )

        if debit.rowcount != 1:
            raise ValueError("Could not debit sender account")

        credit = connection.execute(
            """
            UPDATE accounts
            SET balance = balance + ?
            WHERE account_id = ?
            """,
            (amount, receiver_id)
        )

        if credit.rowcount != 1:
            raise ValueError("Could not credit receiver account")

        connection.commit()
        print("Transfer completed successfully")

    except Exception:
        connection.rollback()
        raise

    finally:
        connection.close()


try:
    transfer_money(1, 2, 1000.00)
except (ValueError, sqlite3.Error) as error:
    print("Transfer failed:", error)

How This Transaction Works

1. The function validates the amount and account identifiers.
2. It opens a database connection and starts a transaction.
3. It verifies that both accounts exist.
4. It checks that the sender has enough funds.
5. It deducts the amount from the sender's account.
6. It adds the amount to the receiver's account.
7. It commits both changes if all operations succeed.
8. It rolls back uncommitted changes if an exception occurs.
9. It closes the connection in the finally block.

Production note: This example demonstrates transaction fundamentals, not a complete banking system. Financial applications also require suitable currency representations, concurrency controls, authorization, idempotency, audit logging, and robust handling of database failures.

14. Managing Database Connection Errors

Database connections can fail for several reasons, including invalid file paths, insufficient permissions, unavailable database servers, incorrect credentials, or network issues.

Example: Handling SQLite Errors

import sqlite3

try:
    connection = sqlite3.connect("college.db")

    try:
        connection.execute(
            "SELECT * FROM table_that_does_not_exist"
        ).fetchall()

    finally:
        connection.close()

except sqlite3.Error as error:
    print("Database operation failed:", error)

The exception handler catches errors raised by SQLite. The inner finally block ensures the connection is closed after it has been created, including when the query fails.

Common Database Exceptions

15. Why Connections Must Be Closed

Closing a connection releases resources used by the database driver and database engine. Leaving unnecessary connections open can waste resources and may contribute to database locking or operational problems.

Example: Closing a Connection Safely

import sqlite3

connection = None

try:
    connection = sqlite3.connect("college.db")

    connection.execute(
        "SELECT student_id, name FROM students"
    )

finally:
    if connection is not None:
        connection.close()

print("Connection cleanup completed")

The connection is closed even if the query raises an exception. In applications that perform many operations, use a consistent connection-management pattern.

Best Practices for Connection Management

• Open a connection when database work is required.
• Close connections when they are no longer needed.
• Do not assume that a connection context manager automatically closes the connection.
• Use context managers to manage transactions and resource cleanup separately where necessary.
• Avoid holding transactions open longer than necessary.
• For server-based databases, consider a connection pool when supported and appropriate.
• Do not share connections across threads unless the driver and application design explicitly support it.

16. Understanding Savepoints

A savepoint marks a point within a transaction to which the application can roll back without necessarily discarding every operation performed earlier in the transaction.

Savepoints are useful when an application has several stages and needs to undo only a particular stage while preserving earlier work.

Example: Using a Savepoint in SQLite

import sqlite3

connection = sqlite3.connect("college.db")

try:
    connection.execute("SAVEPOINT add_employee")

    connection.execute(
        "INSERT INTO employees (name, salary) VALUES (?, ?)",
        ("Riya Kapoor", 49000)
    )

    # Undo the work performed after this savepoint.
    connection.execute("ROLLBACK TO SAVEPOINT add_employee")

    # Release the savepoint after rollback.
    connection.execute("RELEASE SAVEPOINT add_employee")

    connection.commit()
    print("Transaction completed")

except sqlite3.Error as error:
    connection.rollback()
    print("Transaction failed:", error)

finally:
    connection.close()

In this example, the insert is rolled back to the savepoint, so the employee record is not retained. The outer transaction is then committed without that insert.

Savepoint Commands

17. Common Transaction Management Mistakes

18. Best Practices for Transaction Management

• Group related database operations into a single transaction.
• Commit only after all required operations succeed.
• Roll back pending changes when a transaction fails.
• Use database constraints to enforce important integrity rules.
• Use parameterized queries instead of concatenating user input into SQL statements.
• Keep transaction boundaries short and clearly defined.
• Close connections and explicitly created cursors when no longer needed.
• Use context managers to simplify transaction handling.
• Understand the database driver's autocommit configuration.
• Do not expose passwords or sensitive database details in error messages shown to end users.
• Test failure cases, not only successful operations.
• For important systems, plan backups, recovery, monitoring, and audit logging.

19. Mini Project: Expense Tracker with Transaction Handling

This mini project demonstrates how to save expense records and handle transactions using Python and SQLite.

Project Features

• Create an expenses table.
• Add expense records.
• Retrieve all expenses.
• Calculate total expenses.
• Commit successful changes.
• Roll back failed changes.

Complete Example

import sqlite3

DATABASE = "expenses.db"


def initialize_database():
    with sqlite3.connect(DATABASE) as connection:
        connection.execute("""
            CREATE TABLE IF NOT EXISTS expenses (
                expense_id INTEGER PRIMARY KEY AUTOINCREMENT,
                title TEXT NOT NULL,
                amount REAL NOT NULL CHECK (amount > 0),
                category TEXT NOT NULL
            )
        """)


def add_expense(title, amount, category):
    if not title.strip():
        raise ValueError("Expense title cannot be empty")

    if amount <= 0:
        raise ValueError("Amount must be positive")

    if not category.strip():
        raise ValueError("Category cannot be empty")

    connection = sqlite3.connect(DATABASE)

    try:
        connection.execute(
            """
            INSERT INTO expenses (title, amount, category)
            VALUES (?, ?, ?)
            """,
            (title, amount, category)
        )

        connection.commit()
        print("Expense saved successfully")

    except sqlite3.Error:
        connection.rollback()
        raise

    finally:
        connection.close()


def view_expenses():
    with sqlite3.connect(DATABASE) as connection:
        rows = connection.execute("""
            SELECT expense_id, title, amount, category
            FROM expenses
            ORDER BY expense_id DESC
        """).fetchall()

    for row in rows:
        print(row)


def total_expenses():
    with sqlite3.connect(DATABASE) as connection:
        result = connection.execute(
            "SELECT COALESCE(SUM(amount), 0) FROM expenses"
        ).fetchone()

    return result[0]


if __name__ == "__main__":
    initialize_database()

    try:
        add_expense("Internet Bill", 999.00, "Utilities")
        add_expense("Office Supplies", 650.00, "Work")

        print("\\nExpense Records:")
        view_expenses()

        print("\\nTotal Expenses:", total_expenses())

    except (ValueError, sqlite3.Error) as error:
        print("Expense operation failed:", error)

Project Explanation

1. initialize_database() creates the expense table.
2. add_expense() validates the input and inserts a record.
3. commit() finalizes a successful insert.
4. rollback() reverses pending changes when a SQLite error occurs.
5. finally ensures the connection is closed.
6. view_expenses() retrieves expense records.
7. total_expenses() calculates the sum of recorded expenses.

Practice extension: Add an expense deletion feature, date-based filtering, category summaries, and a menu-driven interface. For financial calculations, consider storing amounts as integer minor currency units or using an appropriate decimal strategy rather than relying on binary floating-point arithmetic.

20. Interview Questions and Answers

Q1. What is a database transaction?

Answer: A transaction is a group of database operations treated as one logical unit of work.

Q2. What does commit() do in Python?

Answer: It finalizes the current pending transaction and saves its changes according to the database driver's transaction rules.

Q3. What does rollback() do?

Answer: It reverses the changes made in the current uncommitted transaction.

Q4. What are the ACID properties?

Answer: ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties describe key transaction guarantees.

Q5. What is the difference between commit and rollback?

Answer: Commit finalizes pending changes, whereas rollback undoes the applicable uncommitted changes.

Q6. What is a database connection?

Answer: A connection is an object that allows a Python program to communicate with a database and manage database operations.

Q7. What is a cursor?

Answer: A cursor executes SQL statements and provides access to query results.

Q8. Does the SQLite connection context manager close the connection?

Answer: No. It commits or rolls back an open transaction as appropriate, but the connection should still be closed separately.

Q9. Can rollback undo a committed transaction?

Answer: No. Rollback reverses applicable uncommitted changes. Reversing a committed operation usually requires a new compensating transaction.

Q10. What is autocommit?

Answer: It refers to transaction behavior in which statements may be committed automatically instead of waiting for a manually controlled transaction to be committed.

Q11. Why should database connections be closed?

Answer: Closing connections releases resources and helps prevent unnecessary open sessions and prolonged locks.

Q12. What is a savepoint?

Answer: A savepoint marks a position within a transaction to which the application can roll back without necessarily discarding all earlier operations in the transaction.

Q13. How can Python handle database errors?

Answer: Use try-except blocks to catch appropriate database exceptions, roll back pending changes when necessary, and report or re-raise the error.

Q14. Why are transactions important in banking applications?

Answer: They help ensure that related account changes, such as debiting one account and crediting another, are handled consistently.

Q15. What happens if an SQLite connection is closed with pending changes?

Answer: Pending changes are not automatically committed merely because the connection is closed. Under normal transaction control, uncommitted changes are rolled back when the connection closes.

21. Practical Exercises

1. Create a SQLite database and a table named accounts.
2. Insert two account records and commit the transaction.
3. Update one account balance and verify the result.
4. Insert a record and intentionally raise an exception before committing it; confirm that the insert is rolled back.
5. Write a transaction containing two related updates.
6. Use a connection context manager to commit successful work.
7. Use try-except-finally to handle database errors and close the connection.
8. Create a savepoint and roll back only the operations performed after it.
9. Build a bank-transfer example with validation and transaction handling.
10. Extend the expense tracker with delete and category-summary functions.

22. Quick Revision Checklist

• Understand the meaning and purpose of a database transaction.
• Know the ACID properties.
• Understand database connections and cursors.
• Use commit() to finalize pending changes.
• Use rollback() to reverse uncommitted changes.
• Understand transaction boundaries and autocommit settings.
• Use context managers for transaction handling.
• Close connections reliably.
• Handle database exceptions with appropriate cleanup.
• Understand savepoints and partial rollback.
• Use parameterized queries and database constraints.
• Practice transactions through a bank-transfer or expense-tracking project.

23. Additional Learning Resources

• Explore the Python Training Course at JustAcademy
• Register for a Course Demo at JustAcademy
• Visit the JustAcademy Homepage
• Official Python sqlite3 Documentation
• Official SQLite Transaction Documentation
• Python Database API Specification (DB-API 2.0)

Conclusion

Managing transactions, commits, rollbacks, and connections is essential for building reliable Python database applications. Transactions group related operations, commits finalize successful changes, rollbacks undo pending changes, and proper connection management ensures that database resources are released efficiently.

By practising these concepts with SQLite and then applying them to server-based databases such as MySQL or PostgreSQL, developers can build more dependable applications for banking, inventory management, e-commerce, expense tracking, and other data-driven systems.

Continue learning through the Python Training Course at JustAcademy and register for a course demo.