Python sqlite3 cursor has no attribute commit

Learn python sqlite3 cursor has no attribute commit with practical examples, diagrams, and best practices. Covers python, sqlite development techniques with visual explanations.
Tags:
Categories:

Understanding and Resolving 'sqlite3.Cursor' object has no attribute 'commit'

Hero image for Python sqlite3 cursor has no attribute commit

Learn why you encounter the 'sqlite3.Cursor' object has no attribute 'commit' error in Python and how to correctly commit transactions to your SQLite database.

When working with SQLite databases in Python using the sqlite3 module, a common pitfall for newcomers is attempting to call the commit() method directly on a cursor object. This action invariably leads to an AttributeError: 'sqlite3.Cursor' object has no attribute 'commit'. This article will clarify why this error occurs and, more importantly, demonstrate the correct way to commit your database transactions, ensuring your changes are permanently saved.

The Role of Connection vs. Cursor

To understand why cursor.commit() fails, it's crucial to differentiate between the Connection object and the Cursor object in sqlite3. The Connection object represents the actual link to your SQLite database file. It's responsible for managing transactions, including committing or rolling back changes. The Cursor object, on the other hand, is used to execute SQL queries and fetch results. It acts as an intermediary, allowing you to interact with the database through the established connection.

flowchart TD
    A[Python Application] --> B["sqlite3.connect('database.db')"];
    B --> C[Connection Object];
    C --> D["connection.cursor() - Creates Cursor"];
    D --> E[Cursor Object];
    E --> F["cursor.execute(SQL Query)"];
    F --> G["Database Operations (INSERT, UPDATE, DELETE)"];
    G --> H["connection.commit() - Saves Changes"];
    H --> I[Database File];
    G -- "No commit" --> J["Changes not saved (Rollback on close)"];

Flow of interaction between Python, Connection, Cursor, and Database

As the diagram illustrates, the Cursor object is for executing queries, while the Connection object holds the responsibility for transaction management. When you execute an INSERT, UPDATE, or DELETE statement, these changes are initially staged within the transaction. They are not permanently written to the database file until the commit() method is called on the Connection object.

Correctly Committing Transactions

The solution to the AttributeError is straightforward: always call commit() on the Connection object, not the Cursor object. It's also best practice to use a try...finally block or a with statement to ensure that your connection is properly closed, even if errors occur.

import sqlite3

# Establish a connection to the database
conn = sqlite3.connect('example.db')

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

try:
    # Execute an SQL command
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY,
            name TEXT NOT NULL,
            email TEXT NOT NULL UNIQUE
        )
    ''')
    
    cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ('Alice', 'alice@example.com'))
    cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ('Bob', 'bob@example.com'))
    
    # Commit the changes to the database (on the connection object!)
    conn.commit()
    print("Data inserted and committed successfully.")

except sqlite3.Error as e:
    print(f"An error occurred: {e}")
    # Rollback changes if an error occurs
    conn.rollback()
    print("Transaction rolled back.")
finally:
    # Close the connection
    conn.close()
    print("Connection closed.")

import sqlite3

# Using 'with' statement for automatic connection management
try:
    with sqlite3.connect('example.db') as conn:
        cursor = conn.cursor()
        
        cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ('Charlie', 'charlie@example.com'))
        cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ('David', 'david@example.com'))
        
        # No explicit conn.commit() needed here, 'with' handles it on successful exit
        print("Data inserted successfully using 'with' statement.")

except sqlite3.Error as e:
    print(f"An error occurred: {e}")
    # 'with' statement also handles rollback on exception
    print("Transaction rolled back by 'with' statement.")

# Verify data
with sqlite3.connect('example.db') as conn:
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM users")
    rows = cursor.fetchall()
    print("\nUsers in database:")
    for row in rows:
        print(row)

When is commit() not needed?

By default, sqlite3 operates in DEFERRED transaction mode. This means changes are not written to disk until commit() is called. However, you can change this behavior. If you set isolation_level=None when connecting, sqlite3 will operate in autocommit mode. In this mode, every statement is automatically committed, and you won't need to call conn.commit() explicitly. While convenient for simple scripts, it can be less efficient for multiple operations and doesn't allow for easy transaction rollback.

import sqlite3

# Connect with autocommit mode
conn_autocommit = sqlite3.connect('example_autocommit.db', isolation_level=None)
cursor_autocommit = conn_autocommit.cursor()

try:
    cursor_autocommit.execute('''
        CREATE TABLE IF NOT EXISTS products (
            id INTEGER PRIMARY KEY,
            name TEXT NOT NULL,
            price REAL
        )
    ''')
    
    cursor_autocommit.execute("INSERT INTO products (name, price) VALUES (?, ?)", ('Laptop', 1200.00))
    # Changes are automatically committed after each execute in autocommit mode
    print("Product inserted in autocommit mode.")

    cursor_autocommit.execute("INSERT INTO products (name, price) VALUES (?, ?)", ('Mouse', 25.50))
    print("Another product inserted in autocommit mode.")

except sqlite3.Error as e:
    print(f"An error occurred in autocommit mode: {e}")
finally:
    conn_autocommit.close()
    print("Autocommit connection closed.")

# Verify data in autocommit database
with sqlite3.connect('example_autocommit.db') as conn:
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM products")
    rows = cursor.fetchall()
    print("\nProducts in autocommit database:")
    for row in rows:
        print(row)