Python SQLite3 commit(): Save Database Changes Guide

When working with SQLite databases in Python, understanding how to properly commit changes is crucial. The commit() method ensures that your database modifications are permanently saved.

What is commit() in SQLite3?

The commit() method is used to save all the changes made in the current transaction to the database. Without committing, your changes will be lost when the connection is closed.

Before diving deeper into commit(), it's important to understand how to establish a proper database connection. Learn more about this in our Python SQLite3 Database Connection Guide.

Basic Usage of commit()

import sqlite3

# Create a connection to database
conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# Create a table
cursor.execute('''CREATE TABLE IF NOT EXISTS users
                (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)''')

# Insert data
cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ("John", 25))

# Save the changes
conn.commit()

# Close the connection
conn.close()

Why commit() is Important?

SQLite3 uses transactions to ensure data integrity. When you make changes to the database, they are initially stored in a transaction buffer. These changes become permanent only after calling commit().

Transaction Management Example

import sqlite3

conn = sqlite3.connect('example.db')
cursor = conn.cursor()

try:
    # Start a transaction
    cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ("Alice", 30))
    cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ("Bob", 35))
    
    # Commit changes if everything is successful
    conn.commit()
    print("Changes committed successfully")
    
except sqlite3.Error as e:
    # Roll back changes if there's an error
    conn.rollback()
    print(f"An error occurred: {e}")
    
finally:
    conn.close()

Auto-commit Mode

SQLite3 also supports auto-commit mode. When enabled, each SQL statement is treated as a separate transaction and automatically committed. However, this isn't recommended for multiple operations.

# Enable auto-commit mode
conn = sqlite3.connect('example.db', isolation_level=None)

# No need to call commit() explicitly
cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ("Charlie", 40))

# Close connection
conn.close()

Best Practices for Using commit()

Always commit changes before closing the connection. Failing to do so will result in lost modifications. Use try-except blocks to handle errors properly.

For complex operations, you might want to use the executemany() method along with commit() for better performance.

Multiple Operations Example

import sqlite3

conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# Multiple operations in a single transaction
users = [
    ("David", 28),
    ("Eva", 32),
    ("Frank", 45)
]

try:
    cursor.executemany("INSERT INTO users (name, age) VALUES (?, ?)", users)
    
    # Update existing record
    cursor.execute("UPDATE users SET age = ? WHERE name = ?", (33, "Eva"))
    
    # Commit all changes at once
    conn.commit()
    print("All changes committed successfully")
    
except sqlite3.Error as e:
    conn.rollback()
    print(f"Error occurred: {e}")
    
finally:
    conn.close()

All changes committed successfully

Error Handling and Rolling Back

When an error occurs during database operations, it's important to rollback the transaction to maintain data consistency. The rollback() method reverts all changes made since the last commit.

import sqlite3

conn = sqlite3.connect('example.db')
cursor = conn.cursor()

try:
    # This will raise an error (duplicate primary key)
    cursor.execute("INSERT INTO users (id, name, age) VALUES (1, 'John', 25)")
    conn.commit()
except sqlite3.IntegrityError:
    conn.rollback()
    print("Transaction rolled back due to integrity error")
finally:
    conn.close()

Conclusion

The commit() method is essential for maintaining data persistence in SQLite3 databases. Always remember to commit your changes and handle errors appropriately to ensure data integrity.

For more advanced database operations, you might want to explore our guide on executing SQL commands using the SQLite3 cursor.