Cursor Objects in Python: Fetch Data

A cursor in Python is the bridge between your code and database results. When you execute a query, results don't immediately come back to Python—they remain on the database server until you fetch them. A cursor object manages this: it holds the query state, tracks which rows have been fetched, and provides methods to retrieve them in different ways. Understanding cursors is essential because the wrong fetching strategy wastes memory or causes your application to hang waiting for results.

In this tutorial, you'll learn every method for retrieving data, when to use each, and how to handle large result sets without crashing.

Creating a Cursor and Executing Queries

A cursor is created from a connection and executes queries:

import sqlite3

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

# Execute a query (results are not yet in Python)
cursor.execute('SELECT id, name, email FROM users WHERE created_at > date("now", "-7 days")')

# Now fetch the results
all_users = cursor.fetchall()

for user_id, name, email in all_users:
    print(f"{user_id}: {name} ({email})")

conn.close()

Until you call a fetch method, the query results sit on the server. This is by design—it lets you choose how to consume results (all at once, or one at a time, or in batches).

The Three Fetching Methods

fetchall() — Get Everything at Once

fetchall() returns a list of all remaining rows:

import sqlite3

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

cursor.execute('SELECT id, name, email FROM users')
all_rows = cursor.fetchall()

print(f"Total users: {len(all_rows)}")

for row in all_rows:
    print(f"ID {row[0]}: {row[1]} ({row[2]})")

conn.close()

Each row is a tuple. If your query returns 100 rows, fetchall() returns a list of 100 tuples. This is simple but consumes memory proportional to the result set size. For a table with 10 million rows, fetchall() loads all 10 million rows into memory—likely crashing your program.

fetchone() — Get One Row at a Time

fetchone() returns a single row and advances the cursor:

import sqlite3

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

cursor.execute('SELECT id, name, email FROM users ORDER BY created_at DESC')

# Fetch rows one at a time
while True:
    row = cursor.fetchone()
    if row is None:  # No more rows
        break
    print(f"ID {row[0]}: {row[1]} ({row[2]})")

conn.close()

Useful for processing rows sequentially without loading them all into memory. Each call fetches the next row from the server.

fetchmany(n) — Get n Rows at a Time

fetchmany(n) is a middle ground—fetch batches of rows:

import sqlite3

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

cursor.execute('SELECT id, name, email FROM users')

batch_size = 1000
while True:
    rows = cursor.fetchmany(batch_size)
    if not rows:  # No more rows
        break
    
    # Process this batch
    for row in rows:
        print(f"ID {row[0]}: {row[1]} ({row[2]})")

conn.close()

This is ideal for large datasets—you process 1000 rows, do something with them, then fetch the next 1000. Memory usage stays bounded.

Direct Iteration Over Cursors

Many database adapters support iterating directly over the cursor:

import sqlite3

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

cursor.execute('SELECT id, name, email FROM users')

# Iterate directly (more Pythonic than fetchone())
for user_id, name, email in cursor:
    print(f"{user_id}: {name} ({email})")

conn.close()

Behind the scenes, this uses fetchone() or similar, but it's cleaner. For PostgreSQL with psycopg, iteration is even more efficient:

import psycopg

conn = psycopg.connect('postgresql://user:pass@localhost/myapp')
cursor = conn.cursor()

cursor.execute('SELECT id, name, email FROM users')

# Psycopg streams rows from the server, minimizing memory use
for user_id, name, email in cursor:
    print(f"{user_id}: {name} ({email})")

conn.close()

Named Columns: Row Factories

By default, rows are tuples—you access columns by index (row[0], row[1]). For complex schemas with many columns, this is error-prone. Use a row factory to get named access:

import sqlite3

conn = sqlite3.connect('app.db')

# Set the row_factory to return dictionaries instead of tuples
conn.row_factory = sqlite3.Row

cursor = conn.cursor()
cursor.execute('SELECT id, name, email FROM users LIMIT 1')
row = cursor.fetchone()

# Access columns by name
print(f"ID: {row['id']}")
print(f"Name: {row['name']}")
print(f"Email: {row['email']}")

conn.close()

sqlite3.Row acts like a dictionary—you can iterate over keys, access columns by name, and avoid off-by-one indexing errors. For PostgreSQL with psycopg, use the RealDictCursor:

import psycopg
from psycopg.rows import dict_row

conn = psycopg.connect('postgresql://user:pass@localhost/myapp')

# Use dict_row factory for named access
with conn.cursor(row_factory=dict_row) as cursor:
    cursor.execute('SELECT id, name, email FROM users LIMIT 1')
    row = cursor.fetchone()
    
    print(f"ID: {row['id']}")
    print(f"Name: {row['name']}")

conn.close()

Cursor Properties: rowcount and description

The cursor object provides metadata about the last query:

import sqlite3

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

# Execute an UPDATE
cursor.execute('UPDATE users SET active = 1 WHERE last_login > date("now", "-30 days")')
conn.commit()

# How many rows were affected?
print(f"Updated {cursor.rowcount} users")

# Check column information
cursor.execute('SELECT id, name, email FROM users LIMIT 1')
print(f"Columns: {[desc[0] for desc in cursor.description]}")
# Output: Columns: ['id', 'name', 'email']

conn.close()

cursor.rowcount returns the number of rows affected by the last INSERT, UPDATE, or DELETE. For SELECT, it may return -1 (some databases don't report this). cursor.description is a list of tuples describing each column: (name, type_code, display_size, internal_size, precision, scale, null_ok). Most useful is the first element (column name).

Comparing Fetching Strategies

Method	Use Case	Memory Usage	Example
fetchall()	Small result sets (< 10k rows)	O(n), all rows in memory	Simple one-off queries
fetchone()	Large datasets, streaming	O(1), one row at a time	Log processing, cleanup scripts
fetchmany(n)	Very large datasets, batch processing	O(n), n rows in memory	Data migration, bulk uploads
Direct iteration	Most readable, good balance	Depends on implementation	Production applications

For production applications, direct iteration or fetchmany() is preferred. fetchall() is acceptable for small datasets but dangerous for anything that grows over time.

Common Cursor Mistakes

Mistake 1: Forgetting to commit after INSERT/UPDATE/DELETE.

# WRONG: Changes are lost
cursor.execute('INSERT INTO users (name) VALUES (?)', ('Alice',))
# Forgot conn.commit() — data is not persisted

# RIGHT
cursor.execute('INSERT INTO users (name) VALUES (?)', ('Alice',))
conn.commit()

Mistake 2: Reusing a cursor after closing the connection.

conn = sqlite3.connect('app.db')
cursor = conn.cursor()
conn.close()

cursor.execute('SELECT * FROM users')  # Error: connection is closed

Mistake 3: Calling fetchall() on a very large result set.

# Dangerous for 10 million rows
cursor.execute('SELECT * FROM large_table')
rows = cursor.fetchall()  # Loads all 10 million into memory — likely crashes

Key Takeaways

• A cursor executes queries and retrieves results from the database server.
• Use fetchall() for small result sets, fetchone() for streaming, or direct iteration for balance.
• Use fetchmany(n) for batch processing of large datasets.
• Set row_factory (or use row_factory=dict_row with psycopg) to access columns by name instead of index.
• Check cursor.rowcount to see how many rows an INSERT, UPDATE, or DELETE affected.
• Always call conn.commit() before closing to persist changes.

Frequently Asked Questions

What is the difference between fetchone() and fetchmany(1)?

Functionally, they're similar: both return one row. However, fetchone() returns a tuple (or dict, depending on row_factory), while fetchmany(1) returns a list with one tuple inside. Use fetchone() for clarity.

Why does cursor.rowcount return -1 for a SELECT query?

Not all databases report the total rows matched by a SELECT. If you need the count, include it in your query: SELECT COUNT(*) as total, id, name FROM users.

Can I use a cursor for multiple queries?

Yes, reuse the same cursor:

cursor.execute('SELECT COUNT(*) FROM users')
count = cursor.fetchone()[0]

cursor.execute('SELECT * FROM users')
users = cursor.fetchall()

Each execute() clears previous results, so results from the first query are lost after the second execute.

Is iterating over a cursor more efficient than fetchall()?

Yes, especially for large result sets. Iteration typically fetches rows in batches on demand, keeping memory usage low. fetchall() loads everything at once.

How do I convert a row tuple to a dictionary?

If you didn't set a row_factory, convert manually:

cursor.execute('SELECT id, name, email FROM users')
columns = [desc[0] for desc in cursor.description]
rows = cursor.fetchall()

for row_tuple in rows:
    row_dict = dict(zip(columns, row_tuple))
    print(row_dict)  # {'id': 1, 'name': 'Alice', 'email': '[email protected]'}

Further Reading

• PEP 249 – Python Database API Specification
• SQLite3 Cursor Documentation
• Psycopg Cursor Documentation
• Handling Large Result Sets in Python