Cursors — execute, fetchone, fetchall

~12 min · python, cursor, fetch

Level 0Scout

0 XP0/80 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

The execution unit

Every SQL statement runs through a cursor. conn.execute(sql, params) returns one (a fresh cursor each call). You read rows from it via:

cursor.fetchone() — next row as a tuple, or None.
cursor.fetchmany(n) — up to n rows as a list of tuples.
cursor.fetchall() — all remaining rows as a list (caution: can be huge).
Iteration — for row in cursor: streams rows lazily; idiomatic for large result sets.

Tip: Default to iteration. fetchall() on a million-row query allocates a million tuples in memory; the iterator streams them one at a time.

Code

Three ways to read·python

import sqlite3

conn = sqlite3.connect('demo.db')

# fetchone — single row
row = conn.execute('SELECT * FROM users WHERE id = ?', (1,)).fetchone()
print(row)  # tuple or None

# fetchmany — bounded batch
batch = conn.execute('SELECT * FROM users LIMIT 100').fetchmany(20)
print(len(batch), 'rows')

# Iterate — streaming, idiomatic for large results
for user_id, email in conn.execute('SELECT id, email FROM users'):
    print(user_id, email)

Cursor metadata·python

cur = conn.execute('SELECT id, email, username FROM users LIMIT 1')
print(cur.description)
# (('id', None, ...), ('email', None, ...), ('username', None, ...))

print([d[0] for d in cur.description])
# ['id', 'email', 'username']

# After execute, lastrowid is set for INSERTs
cur = conn.execute('INSERT INTO users(email) VALUES (?)', ('z@x.com',))
print(cur.lastrowid)
# 42

External links

sqlite3 — Cursor objects

Exercise

Build a generator function that yields one row at a time from a query, using cursor iteration. Use it to process a 100k-row table and confirm peak memory stays low (use tracemalloc or the OS process monitor). Then re-do it with fetchall() and observe the difference.

Progress

Progress is local-only — sign in to sync across devices.

← PreviousGetting Started — import sqlite3 Next →Parameterized Queries — Stop SQL Injection

Spotted a bug or have feedback on this page?Report an Issue

Comments 0

🔔 Reply notifications (sign in)

No comments yet — be the first.