Iterating Over Results
What you'll learn: How to loop through database query results one row at a time without overwhelming your computer's memory.
Why Iteration Matters
When you execute a query that returns thousands or millions of rows, calling fetchall() loads everything into memory at once. That's like trying to carry all your groceries in one trip when you have 50 bags—it works until it doesn't.
Instead, you can iterate directly over the cursor object itself. The cursor acts like a smart conveyor belt, delivering one row at a time as you're ready to process it.
How It Works
After executing a query, treat your cursor like any Python iterable:
import sqlite3
conn = sqlite3.connect('sales.db')
cursor = conn.execute("SELECT customer_id, total FROM orders")
for row in cursor:
customer_id, total = row
print(f"Customer {customer_id} spent ${total}")
conn.close()
Each iteration automatically fetches the next row. When there are no more rows, the loop ends naturally.
The Memory Advantage
This approach is memory-efficient because only one row (or a small batch internally) is held in memory at any moment. You can process a billion-row result set on a laptop with 8GB of RAM—something impossible with fetchall().
Think of it like streaming a movie versus downloading the entire file first. You get the content you need, exactly when you need it.
When to Use This Pattern
- Processing large result sets
- Transforming data row-by-row
- Writing results to a file or another database
- When you don't need random access to rows
Key Takeaway: Iterating directly over a cursor object processes query results row-by-row with minimal memory overhead, making it ideal for handling large datasets efficiently.