---

SQLite with Python#

Handling Transactions#

Transactions are a critical aspect of database management, ensuring data integrity and consistency. In SQLite, a transaction is a sequence of operations performed as a single logical unit of work. Transactions ensure that either all operations are completed successfully, or none of them are applied, maintaining the database's consistency. This section will guide you through the process of handling transactions in SQLite using Python's sqlite3 module.

Understanding Transactions#

A transaction has four key properties, commonly known as ACID properties: - Atomicity: Ensures that all operations within a transaction are completed; if one operation fails, the entire transaction fails. - Consistency: Ensures that the database remains in a consistent state before and after the transaction. - Isolation: Ensures that the execution of one transaction is isolated from other transactions. - Durability: Ensures that once a transaction is committed, the changes are permanent, even in the event of a system failure.

Starting a Transaction#

In SQLite, a transaction begins implicitly with the first SQL statement that modifies data (INSERT, UPDATE, DELETE). However, you can also start a transaction explicitly using the BEGIN statement.

Example:

import sqlite3

# Connect to the SQLite database
conn = sqlite3.connect('example.db')

# Create a cursor object
cursor = conn.cursor()

# Begin a transaction
cursor.execute('BEGIN')

In this example, we explicitly start a transaction using the BEGIN statement.

Committing a Transaction#

Committing a transaction means making all changes made during the transaction permanent. This is done using the COMMIT statement.

Example:

import sqlite3

try:
    # Connect to the SQLite database
    conn = sqlite3.connect('example.db')

    # Create a cursor object
    cursor = conn.cursor()

    # Begin a transaction
    cursor.execute('BEGIN')

    # Insert data into the 'customers' table
    cursor.execute('''
    INSERT INTO customers (CustomerName, ContactName, Country)
    VALUES ('New Customer', 'Jane Doe', 'USA')
    ''')

    # Commit the transaction
    conn.commit()

except sqlite3.Error as e:
    print('SQLite error occurred:', e)

finally:
    # Close the cursor and connection
    cursor.close()
    conn.close()

In this example, we begin a transaction, insert a new record into the customers table, and commit the transaction using conn.commit(), making the changes permanent.

Rolling Back a Transaction#

Rolling back a transaction means undoing all changes made during the transaction. This is done using the ROLLBACK statement. Rolling back is useful when an error occurs, and you need to revert the database to its previous state.

Example:

import sqlite3

try:
    # Connect to the SQLite database
    conn = sqlite3.connect('example.db')

    # Create a cursor object
    cursor = conn.cursor()

    # Begin a transaction
    cursor.execute('BEGIN')

    # Insert data into the 'customers' table
    cursor.execute('''
    INSERT INTO customers (CustomerName, ContactName, Country)
    VALUES ('New Customer', 'Jane Doe', 'USA')
    ''')

    # Simulate an error
    raise Exception('Simulated error')

    # Commit the transaction
    conn.commit()

except Exception as e:
    print('Error occurred:', e)
    # Rollback the transaction
    conn.rollback()

finally:
    # Close the cursor and connection
    cursor.close()
    conn.close()

In this example, we begin a transaction, insert a new record, and then simulate an error. The conn.rollback() statement reverts the database to its state before the transaction began.

Using Context Managers for Transactions#

Python's sqlite3 module supports using context managers (with statements) to handle transactions. This approach simplifies transaction management by automatically committing or rolling back transactions.

Example:

import sqlite3

# Connect to the SQLite database
conn = sqlite3.connect('example.db')

try:
    with conn:
        # Create a cursor object
        cursor = conn.cursor()

        # Insert data into the 'customers' table
        cursor.execute('''
        INSERT INTO customers (CustomerName, ContactName, Country)
        VALUES ('New Customer', 'Jane Doe', 'USA')
        ''')

except sqlite3.Error as e:
    print('SQLite error occurred:', e)

finally:
    # Close the connection
    conn.close()

In this example, using with conn: ensures that if an error occurs within the block, the transaction is automatically rolled back. If no error occurs, the transaction is committed when the block is exited.

Savepoints#

Savepoints allow you to set intermediate points within a transaction that you can roll back to without affecting the entire transaction. This is useful for complex transactions where you want to maintain finer control over what gets rolled back.

Example:

import sqlite3

try:
    # Connect to the SQLite database
    conn = sqlite3.connect('example.db')

    # Create a cursor object
    cursor = conn.cursor()

    # Begin a transaction
    cursor.execute('BEGIN')

    # Insert data into the 'customers' table
    cursor.execute('''
    INSERT INTO customers (CustomerName, ContactName, Country)
    VALUES ('Customer One', 'John Doe', 'USA')
    ''')

    # Set a savepoint
    cursor.execute('SAVEPOINT sp1')

    # Insert another record
    cursor.execute('''
    INSERT INTO customers (CustomerName, ContactName, Country)
    VALUES ('Customer Two', 'Alice Smith', 'UK')
    ''')

    # Rollback to the savepoint
    cursor.execute('ROLLBACK TO sp1')

    # Commit the transaction
    conn.commit()

except sqlite3.Error as e:
    print('SQLite error occurred:', e)

finally:
    # Close the cursor and connection
    cursor.close()
    conn.close()

In this example, we begin a transaction, insert a record, set a savepoint, insert another record, and then roll back to the savepoint. This action undoes the second insert while retaining the first insert.

By mastering transaction handling in SQLite using Python, you can ensure the integrity and consistency of your database operations, making your applications more robust and reliable.