Python SQLite3 Cursor Guide: Execute SQL Commands

The cursor() method in Python SQLite3 is a crucial component for executing SQL statements and managing database operations. It serves as an intermediary between your Python code and the SQLite database.

Before diving into cursor operations, make sure you have a proper database connection. Learn more about establishing connections in our Python SQLite3 Database Connection Guide.

Creating a Cursor Object

To create a cursor object, you first need to establish a database connection and then call the cursor() method on the connection object.

import sqlite3

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

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

Basic Cursor Operations

The cursor object provides several methods to execute SQL commands. Here are the most commonly used ones:

1. Creating a Table

# Create a table
cursor.execute('''CREATE TABLE IF NOT EXISTS employees
                 (id INTEGER PRIMARY KEY,
                  name TEXT NOT NULL,
                  salary REAL)''')

# Commit the changes
conn.commit()

2. Inserting Data

You can insert data using the execute() method for single records or executemany() for multiple records.

# Single record insertion
cursor.execute("INSERT INTO employees (name, salary) VALUES (?, ?)", ("John Doe", 50000))

# Multiple records insertion
employees = [
    ("Jane Smith", 60000),
    ("Bob Johnson", 55000)
]
cursor.executemany("INSERT INTO employees (name, salary) VALUES (?, ?)", employees)

conn.commit()

3. Querying Data

The cursor provides multiple ways to fetch data from the database using fetchone(), fetchmany(), and fetchall() methods.

# Select all employees
cursor.execute("SELECT * FROM employees")

# Fetch all records
all_employees = cursor.fetchall()
for employee in all_employees:
    print(f"ID: {employee[0]}, Name: {employee[1]}, Salary: {employee[2]}")

ID: 1, Name: John Doe, Salary: 50000
ID: 2, Name: Jane Smith, Salary: 60000
ID: 3, Name: Bob Johnson, Salary: 55000

Advanced Cursor Features

Error Handling is essential when working with database operations. Always use try-except blocks to handle potential errors gracefully.

try:
    cursor.execute("SELECT * FROM non_existent_table")
except sqlite3.Error as error:
    print("Error occurred:", error)
finally:
    cursor.close()
    conn.close()

Using Row Factory

The row_factory property allows you to customize how rows are returned. Using sqlite3.Row provides dictionary-like access to columns.

conn.row_factory = sqlite3.Row
cursor = conn.cursor()

cursor.execute("SELECT * FROM employees WHERE id = 1")
row = cursor.fetchone()
print(f"Name: {row['name']}, Salary: {row['salary']}")

Best Practices

Here are some important practices to follow when working with SQLite3 cursors:

• Always close both cursor and connection objects after use
• Use parameter substitution to prevent SQL injection
• Commit transactions explicitly after modifying data

# Example of proper resource management
try:
    conn = sqlite3.connect('example.db')
    cursor = conn.cursor()
    
    # Database operations here
    cursor.execute("SELECT * FROM employees")
    
except sqlite3.Error as error:
    print("Error:", error)
finally:
    if cursor:
        cursor.close()
    if conn:
        conn.close()

Cursor Methods Reference

Here are the essential cursor methods you should know:

• execute(): Run a single SQL statement
• executemany(): Execute the same SQL statement with different parameters
• fetchone(): Retrieve the next row of a query result
• fetchall(): Fetch all remaining rows of a query result
• fetchmany(): Fetch the next n rows of a query result

Conclusion

The SQLite3 cursor is a powerful tool for executing SQL commands in Python. Understanding its proper usage is essential for efficient database operations and maintaining clean, reliable code.

Remember to always handle resources properly, use parameter substitution for queries, and implement proper error handling in your database operations.