Course contentsShow
← Data Science
Lesson 1096 of 2,14528. Working with Databases from PythonFree lesson

Fetching Multiple Results with fetchmany()

Control batch size when retrieving results and manage memory efficiently for medium-sized datasets.

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.