Fetching Multiple Results with fetchmany()
What you'll learn: How to retrieve database query results in controllable batches to balance memory usage and performance.
When working with medium-sized datasets, fetching all results at once with fetchall() can overwhelm your memory, while fetching one row at a time with fetchone() creates too much overhead. That's where fetchmany() shines—it lets you retrieve results in manageable chunks.
How fetchmany() Works
After executing a query with a cursor object, you call fetchmany(size) to retrieve a specified number of rows as a list. If you don't specify a size, it uses the cursor's default arraysize (typically 1, but you can change it).
Think of it like loading groceries: Instead of carrying everything in one trip (risking dropping items) or making 50 separate trips for individual items (exhausting), you make a few trips with reasonably-sized loads.
import sqlite3
conn = sqlite3.connect('sales.db')
cursor = conn.execute("SELECT * FROM orders")
# Fetch 100 rows at a time
batch = cursor.fetchmany(100)
while batch:
for row in batch:
# Process each row
print(row)
batch = cursor.fetchmany(100) # Get next batch
conn.close()
Choosing Your Batch Size
Smaller batches (10-100 rows) minimize memory but increase round-trips. Larger batches (1000-10000 rows) reduce overhead but consume more memory. The sweet spot depends on your row size and available memory.
You can also set the cursor's default batch size:
cursor.arraysize = 500
batch = cursor.fetchmany() # Fetches 500 rows
Key Takeaway: Use fetchmany() to retrieve query results in controlled batches, optimizing the trade-off between memory consumption and database round-trip overhead for medium-sized datasets.