Fetching All Results with fetchall()
What You'll Learn
You'll learn how to retrieve an entire query result set at once using fetchall() and understand when this approach is appropriate—or risky.
The Core Idea
After executing a query with a cursor object, you often want to process all matching rows. The fetchall() method loads every single row from your result set into memory as a list of tuples (or rows). This is convenient and simple, but comes with an important trade-off.
Think of it like this: imagine asking a librarian for all books on a topic. If there are 10 books, you can carry them all at once. If there are 10,000 books, you'd need a truck—and you might run out of space. fetchall() loads everything immediately, which is perfect for small result sets but dangerous for large ones.
Using fetchall()
import sqlite3
conn = sqlite3.connect('example.db')
cursor = conn.cursor()
cursor.execute("SELECT name, age FROM users WHERE age > 25")
all_rows = cursor.fetchall() # Returns ALL results as a list
for row in all_rows:
print(f"Name: {row[0]}, Age: {row[1]}")
conn.close()
The returned value is a list of tuples—one tuple per row. You can iterate over it, check its length with len(all_rows), or access specific rows by index like all_rows[0].
Memory Implications
If your query returns 1 million rows, fetchall() loads all 1 million into RAM at once. For large datasets, this can crash your program or severely slow down your system. In those cases, fetchone() or fetchmany() are safer alternatives that process results incrementally.
Key Takeaway: fetchall() conveniently loads entire result sets into memory as a list, making it ideal for small-to-moderate queries—but always consider memory constraints for large datasets.