Commit & RollBack Operation in Python

Database transactions in Python require careful management of commit and rollback operations. These operations ensure data integrity by allowing you to either save changes permanently or undo them completely.

Understanding Transactions

A transaction is a sequence of database operations that must be completed as a single unit. If any operation fails, the entire transaction can be rolled back to maintain data consistency.

COMMIT Operation

The commit() method finalizes all changes made during the current transaction. Once committed, changes become permanent and cannot be reverted.

Example

import sqlite3

# Connect to database
conn = sqlite3.connect(':memory:')
cursor = conn.cursor()

# Create table
cursor.execute('''CREATE TABLE students 
                 (id INTEGER, name TEXT, age INTEGER)''')

# Insert data
cursor.execute("INSERT INTO students VALUES (1, 'Alice', 20)")
cursor.execute("INSERT INTO students VALUES (2, 'Bob', 22)")

# Commit changes to make them permanent
conn.commit()

# Verify data exists
cursor.execute("SELECT * FROM students")
print("After commit:", cursor.fetchall())

conn.close()

After commit: [(1, 'Alice', 20), (2, 'Bob', 22)]

ROLLBACK Operation

The rollback() method undoes all changes made since the last commit. This is useful when an error occurs or when you want to cancel pending changes.

Example

import sqlite3

# Connect to database
conn = sqlite3.connect(':memory:')
cursor = conn.cursor()

# Create and populate table
cursor.execute('''CREATE TABLE products 
                 (id INTEGER, name TEXT, price REAL)''')
cursor.execute("INSERT INTO products VALUES (1, 'Laptop', 999.99)")
conn.commit()

print("Original data:")
cursor.execute("SELECT * FROM products")
print(cursor.fetchall())

# Make changes but don't commit
cursor.execute("INSERT INTO products VALUES (2, 'Mouse', 25.00)")
cursor.execute("UPDATE products SET price = 1200 WHERE id = 1")

print("\nAfter changes (before rollback):")
cursor.execute("SELECT * FROM products")
print(cursor.fetchall())

# Rollback changes
conn.rollback()

print("\nAfter rollback:")
cursor.execute("SELECT * FROM products")
print(cursor.fetchall())

conn.close()

Original data:
[(1, 'Laptop', 999.99)]

After changes (before rollback):
[(1, 'Laptop', 1200.0), (2, 'Mouse', 25.0)]

After rollback:
[(1, 'Laptop', 999.99)]

Best Practices with Try-Except

Use try-except blocks to handle errors and ensure proper transaction management ?

import sqlite3

conn = sqlite3.connect(':memory:')
cursor = conn.cursor()

cursor.execute('''CREATE TABLE accounts 
                 (id INTEGER, balance REAL)''')
cursor.execute("INSERT INTO accounts VALUES (1, 1000)")
cursor.execute("INSERT INTO accounts VALUES (2, 500)")
conn.commit()

try:
    # Start transaction
    cursor.execute("UPDATE accounts SET balance = balance - 200 WHERE id = 1")
    cursor.execute("UPDATE accounts SET balance = balance + 200 WHERE id = 2")
    
    # Check if transaction is valid
    cursor.execute("SELECT balance FROM accounts WHERE id = 1")
    balance = cursor.fetchone()[0]
    
    if balance < 0:
        raise ValueError("Insufficient funds")
    
    # Commit if everything is okay
    conn.commit()
    print("Transaction successful!")
    
except Exception as e:
    # Rollback on error
    conn.rollback()
    print(f"Transaction failed: {e}")

# Check final balances
cursor.execute("SELECT * FROM accounts")
print("Final balances:", cursor.fetchall())

conn.close()

Transaction successful!
Final balances: [(1, 800.0), (2, 700.0)]

Conclusion

Use commit() to make changes permanent and rollback() to undo changes. Always use try-except blocks for robust transaction management in production applications.