skip to content

In Python's DB-API, how do cursor fetchone(), fetchmany() and fetchall() differ?

level: juniorimportance: must knowfreq 62%

answer

  1. A cursor remembers where it is
  2. Three ways to drain one result set
  3. One returns a row, two return lists
  4. Exhausted means None, or an empty list
  5. The no-argument batch size lives on the cursor

basics

~20 s

All three read from one cursor's open result set and advance the same position. fetchone() returns the next row or None when exhausted, fetchmany(n) returns up to n rows in a list, and fetchall() returns every remaining row.

solid answer

~40 s

After `execute()` runs a query, the cursor holds a result set and a position in it. `fetchone()` returns the next row as a sequence, or `None` once the rows run out — it does not raise. `fetchmany(size)` returns a list of at most `size` rows, defaulting to the cursor's `arraysize`, and returns an empty list at the end. `fetchall()` returns a list of everything still unread. They all consume from the same cursor, so a `fetchone()` followed by a `fetchall()` returns the remainder, not the whole set, and a second `fetchall()` gives `[]`. The choice is a memory decision: `fetchall()` materialises the whole result in the process, while a `fetchmany()` loop — or iterating the cursor, which most drivers support — keeps a bounded amount of it resident.

code

python · 18 lines
python
import sqlite3

con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("CREATE TABLE charge(id INTEGER PRIMARY KEY, cents INTEGER)")
cur.executemany(
    "INSERT INTO charge(cents) VALUES (?)",
    [(n,) for n in (100, 200, 300, 400, 500)],
)
con.commit()

cur.execute("SELECT id, cents FROM charge ORDER BY id")
print(cur.fetchone())    # (1, 100)
print(cur.fetchmany(2))  # [(2, 200), (3, 300)]
print(cur.fetchall())    # [(4, 400), (5, 500)]
print(cur.fetchone())    # None -- exhausted, not an error
print(cur.arraysize)     # 1 -- the default batch size for fetchmany()
con.close()

go deeper

for a junior

Recall the three return shapes and the end-of-data signal: a row or None, a list of at most n, a list of the rest. Be able to write the while loop that drains a cursor with fetchone().

for a middle

Explain that all three share one cursor position, that fetchmany()'s default comes from arraysize, and why fetchall() on a large query is the memory risk. Know that rowcount is for DML, not SELECT.

for a senior

Show judgement about batch size and driver mode: whether the driver buffers client-side or streams server-side, what arraysize does to round trips, and how you bound memory in a long export rather than hoping fetchmany() does it for you.

for a principal

Own the standard: decide whether teams touch raw cursors at all or go through a data-access layer that enforces streaming, batch size and cursor lifetime, and be able to justify that boundary against the cost of a hand-written fetchall() escaping into a job.

## The cursor is a position, not a container PEP 249, the Python Database API 2.0 specification, gives every conforming driver the same shape: you get a connection, ask it for a cursor, call `execute()` with a statement, and then pull rows off the cursor. The crucial mental model is that a cursor is **stateful**. It does not hold your rows the way a list does; it holds a *result set* plus a *cursor position* within it. Every fetch call reads from the current position and advances it. That single fact explains almost every surprise people hit with the three fetch methods. ## The three methods **`fetchone()`** returns the next single row, or `None` when the result set is exhausted. The choice of `None` rather than an exception matters: a `while` loop reading `row = cur.fetchone()` and stopping when it is falsy is the classic DB-API idiom. It does not raise `StopIteration`, and it does not raise on an empty result — a query that matched nothing simply returns `None` on the first call. **`fetchmany(size=cursor.arraysize)`** returns a list of *at most* `size` rows. Fewer rows than requested is not an error; it means you have reached the end. When you have read past the end it returns an empty list, which is the natural loop terminator. Called with no argument it uses the cursor's `arraysize` attribute, which PEP 249 says defaults to 1 — so a bare `fetchmany()` is usually a slow way to spell `fetchone()` wrapped in a list. If you want batches, either pass the size explicitly or set `arraysize` on the cursor first. **`fetchall()`** returns a list of all *remaining* rows. The word remaining is the whole subtlety: fetches share one position, so if you called `fetchone()` first, `fetchall()` gives you everything except that first row. Call it twice and the second call returns `[]`. A row is a sequence of column values in the order of the `SELECT` list — a plain tuple for most drivers, though drivers offer their own hooks to return something else. The column metadata lives on `cursor.description`, a sequence of 7-item descriptions whose first element is the column name; that is how generic code maps positions to names without hard-coding them. ## What this costs The interview point behind the question is memory. `fetchall()` builds one Python list holding one tuple per row and one object per column value. On a billing extract of a few dozen rows that is irrelevant; on a multi-million-row export it is the difference between a job that runs and a container that is killed for exceeding its memory limit. A `fetchmany(500)` loop, or iterating the cursor directly, keeps only a batch resident at a time. One honest caveat separates a mid-level answer from a junior one: whether that actually bounds memory depends on the driver. Some drivers use a **client-side** cursor and buffer the entire result set into the client process as soon as `execute()` returns, in which case `fetchmany()` bounds only how much *Python-level* object churn you do at once, not how much the driver already holds. Others support a **server-side** (named or streaming) cursor, where rows really are pulled from the server in batches. If bounded memory is a requirement, you check the driver's documentation rather than assuming. ## Other cursor state worth knowing `cursor.rowcount` reports the number of rows the last operation affected — meaningful and useful after an `INSERT`, `UPDATE` or `DELETE`, and specified as `-1` when the driver cannot determine it, which for a `SELECT` is common until the rows have actually been read. Do not use it to decide whether a query matched. Calling a fetch method when there is no result set — before any `execute()`, or after a statement that returns no rows — is a `ProgrammingError` (or the driver's equivalent) rather than an empty list, so branch on the *statement kind* you issued, not on a `try`/`except`. Finally, cursors are cheap but not free, and they hold driver-side resources. Creating one per unit of work and closing it (a `with` block over `contextlib.closing`, or an explicit `close()`) is the tidy pattern; recycling one cursor across unrelated queries is legal but throws away the result set of the previous query the moment you call `execute()` again — a real source of "my rows disappeared" bug reports.

  • What batch size does fetchmany() use when you call it with no argument?
    The cursor's `arraysize` attribute, which PEP 249 specifies as defaulting to 1 — so a bare `fetchmany()` returns a one-row list. If you want real batching you either pass the size explicitly, `cur.fetchmany(500)`, or set `cur.arraysize = 500` once and let subsequent calls inherit it. Some drivers also use `arraysize` as a hint for how many rows to prefetch from the server per round trip, so raising it can cut round trips as well as loop iterations.
  • After a SELECT, can you trust cursor.rowcount to tell you how many rows matched?
    No. PEP 249 says `rowcount` is `-1` when the driver cannot determine the count, and for a `SELECT` many drivers cannot until the rows have actually been fetched — some report `-1` throughout, others report a running total. It is dependable for `INSERT`, `UPDATE` and `DELETE`, where it is the number of rows the statement affected. To know whether a query matched, fetch and check, or ask the database with a `COUNT`.
  • Does a fetchmany() loop guarantee that your process never holds the whole result set?
    Only if the driver streams. Drivers using a client-side cursor buffer the entire result into the client the moment `execute()` returns, so `fetchmany()` bounds Python-level object creation but not the driver's own buffer. Drivers that support server-side or named cursors genuinely pull batches over the wire. If bounded memory is a hard requirement, confirm which mode your driver is in rather than assuming the loop is enough.

A cursor is a bookmark in a printed report, not a photocopy of it: each fetch reads forward from the bookmark and moves it, so nothing you have already read comes back.

saying these in an interview costs you the question

  • Says fetchone() raises StopIteration when the rows run out
  • Thinks calling fetchall() twice returns the rows a second time
  • Believes fetchall() after fetchone() still returns the first row
  • Assumes fetchmany() with no argument fetches a large default batch
  • Reaches for fetchall() on a multi-million-row export
  • Trusts cursor.rowcount to report how many rows a SELECT matched

context

resolving links…