How to use the SQLite3 cursor object for database operations in Python

When you’re working with databases in Python, the cursor object is your primary interface for executing SQL commands. Think of it as your database’s remote control. You send commands through it, and it sends back results.

To create a cursor, you first need a connection object, usually from a library like sqlite3 or psycopg2. Here’s a quick example using SQLite:

import sqlite3

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

Once you have your cursor, executing SQL is simpler. You call execute() with your SQL query as a string. If your query requires parameters, never concatenate strings directly—always use parameterized queries to avoid SQL injection.

# Creating a table
cur.execute('''
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY,
        username TEXT NOT NULL,
        email TEXT NOT NULL UNIQUE
    )
''')

# Inserting data safely
username = "alice"
email = "[email protected]"
cur.execute('INSERT INTO users (username, email) VALUES (?, ?)', (username, email))

conn.commit()

Notice the placeholders ? in the SQL string. Different database adapters may use different placeholder styles—like %s in psycopg2—but the principle remains the same. The parameters are passed as a tuple or list in the second argument of execute().

For queries that don’t change data, like SELECT statements, you also use execute():

cur.execute('SELECT id, username FROM users WHERE email = ?', (email,))

That runs the query and prepares the cursor to fetch results. You can reuse the same cursor to run multiple queries, but it’s often cleaner to create a new one if you’re doing complex operations in parallel.

When you want to execute multiple similar INSERTs efficiently, there’s executemany(). It takes the query and a list of tuples with parameters:

users = [
    ('bob', '[email protected]'),
    ('carol', '[email protected]'),
    ('dave', '[email protected]'),
]

cur.executemany('INSERT INTO users (username, email) VALUES (?, ?)', users)
conn.commit()

This method is much faster than looping and calling execute() for each row because it reduces the round-trips to the database engine.

One subtlety: always commit your transactions after inserts, updates, or deletes, or else changes won’t be saved. For selects, commit is not necessary.

In the sphere of SQL, the cursor is your gateway to both sending commands and retrieving data. Mastering its use is essential to writing clean, efficient database code without resorting to raw string concatenation or risking SQL injection.

Here’s a quick recap of the pattern:

cur = conn.cursor()
cur.execute('YOUR SQL HERE', parameters)
conn.commit()  # if it modifies data
results = cur.fetchall()  # if you expect results
cur.close()

Keep in mind, closing the cursor is good practice once you’re done with it, especially in long-running applications. But if you just want to fetch the data immediately after the query, you can call fetchone(), fetchmany(), or fetchall() right after execute().

That leads us directly into how to fetch and process results efficiently…

Fetching and processing query results efficiently

When fetching results, you have several methods at your disposal, each suited for different scenarios. The simplest is fetchall(), which returns all rows of a query result as a list of tuples. It’s convenient but can be a memory hog if your result set is huge.

cur.execute('SELECT id, username, email FROM users')
rows = cur.fetchall()
for row in rows:
    print(row)

If you’re dealing with large datasets, fetchone() is your friend. It retrieves one row at a time, letting you process data incrementally without loading everything into memory:

cur.execute('SELECT id, username, email FROM users')
while True:
    row = cur.fetchone()
    if row is None:
        break
    print(row)

For a compromise, fetchmany(size) fetches a fixed number of rows per call. That’s great when you want to process data in manageable chunks:

cur.execute('SELECT id, username, email FROM users')
batch_size = 10
while True:
    rows = cur.fetchmany(batch_size)
    if not rows:
        break
    for row in rows:
        print(row)

Using fetchmany() helps reduce memory consumption and can improve performance when dealing with large query results.

Another option, if your database adapter supports it, is to iterate directly over the cursor, which fetches rows lazily:

cur.execute('SELECT id, username, email FROM users')
for row in cur:
    print(row)

This approach is clean and efficient, especially for simple processing loops, since it avoids loading all rows simultaneously and lets you write idiomatic Python code.

When processing query results, it’s often useful to unpack tuples directly into variables for readability:

cur.execute('SELECT id, username, email FROM users')
for user_id, username, email in cur:
    print(f"User {username} with ID {user_id} has email {email}")

Some database adapters support returning rows as dictionaries or named tuples, which can make code clearer by accessing columns by name instead of position. For example, with SQLite you can set the row factory:

conn.row_factory = sqlite3.Row
cur = conn.cursor()
cur.execute('SELECT id, username, email FROM users')
for row in cur:
    print(row['username'], row['email'])

This is especially helpful when working with many columns or when the order of columns isn’t guaranteed.

Finally, always remember to handle exceptions and close cursors properly to avoid resource leaks. Using with statements can automate this:

with conn.cursor() as cur:
    cur.execute('SELECT id, username FROM users')
    for row in cur:
        print(row)

Note that not all DB-API implementations support cursor context managers, so check your adapter’s documentation.