Introduction

After executing a query with a Python DB-API cursor, you still have to decide how to consume the result set. fetchone, fetchmany, and fetchall all read from the same cursor state, but they do so with different memory and iteration tradeoffs. The right choice depends on how many rows you expect and how quickly you need them in the application.

fetchone: One Row at a Time

fetchone() returns the next row from the result set or None when nothing is left.

1import sqlite3
2
3connection = sqlite3.connect(":memory:")
4cursor = connection.cursor()
5
6cursor.execute("CREATE TABLE items (id INTEGER, name TEXT)")
7cursor.executemany(
8    "INSERT INTO items VALUES (?, ?)",
9    [(1, "apple"), (2, "banana"), (3, "cherry")],
10)
11
12cursor.execute("SELECT id, name FROM items ORDER BY id")
13
14print(cursor.fetchone())
15print(cursor.fetchone())
16print(cursor.fetchone())
17print(cursor.fetchone())

This is useful when you expect only one row or want to stream rows one by one through application logic.

fetchmany: Batch the Result Set

fetchmany(size) returns up to size rows as a list. It is a middle ground between one-row iteration and loading everything at once.

1cursor.execute("SELECT id, name FROM items ORDER BY id")
2
3rows = cursor.fetchmany(2)
4print(rows)
5
6rows = cursor.fetchmany(2)
7print(rows)

This is often a good choice for large result sets because it limits peak memory use while still reducing the per-call overhead of fetchone().

Many drivers also use cursor.arraysize as the default batch size when fetchmany() is called without an argument.

fetchall: Read Everything Remaining

fetchall() returns all remaining rows from the current result set.

cursor.execute("SELECT id, name FROM items ORDER BY id")
rows = cursor.fetchall()
print(rows)

This is convenient for small and moderate queries where having the full result in memory is fine. It is also common in scripts and tests where simplicity matters more than streaming behavior.

The main risk is obvious: if the query returns a huge number of rows, fetchall() can consume a lot of memory very quickly.

The Cursor Advances as You Fetch

These methods all consume from the same current cursor position. That means mixing them changes what remains.

1cursor.execute("SELECT id, name FROM items ORDER BY id")
2print(cursor.fetchone())
3print(cursor.fetchmany(2))
4print(cursor.fetchall())

The first row is consumed by fetchone(), the next two by fetchmany(2), and fetchall() returns only whatever is still left after that.

This matters because many bugs around DB-API fetching are really cursor-state bugs, not SQL bugs.

Choose Based on Expected Volume

A useful rule of thumb is:

• use fetchone() when you expect one row or want explicit row-by-row handling
• use fetchmany() for large result sets when you want bounded memory usage
• use fetchall() when the result set is comfortably small

The right answer is not only about syntax. It is about data size and access pattern.

Iterating Directly Over the Cursor Is Also an Option

Many DB-API cursors are iterable, which gives a row-by-row style similar to repeated fetchone() calls.

1cursor.execute("SELECT id, name FROM items ORDER BY id")
2
3for row in cursor:
4    print(row)

This is often the cleanest choice when you want a simple streaming loop and do not need manual batch control.

Common Pitfalls

• Using fetchall() on large result sets and consuming far more memory than expected.
• Forgetting that fetch methods advance the cursor position.
• Assuming fetchone() returns an empty tuple when the result is exhausted, when it usually returns None.
• Mixing fetch methods without realizing that earlier calls already consumed rows.
• Reaching for fetchall() by habit when fetchmany() would be safer for production-scale queries.

Summary

• 'fetchone() returns the next single row or None when the result set is exhausted.'
• 'fetchmany() returns a batch of rows and is a strong middle ground for larger queries.'
• 'fetchall() returns every remaining row and is best for smaller result sets.'
• All three methods consume from the same cursor state.
• The correct choice depends mostly on result-set size and how you want to process the rows.