Theory
The receptionist has your answer. Now collect it.
Last lesson ended on a cliffhanger: cur.execute("SELECT roll, score FROM marks") ran fine, but no rows appeared anywhere.
That is by design. execute() only prepares the results at the cursor; nothing crosses into Python until you fetch.
Two collection styles exist, and choosing between them is the lesson: take rows one at a time (fetchone) or take everything at once (fetchall).
Theory
The token counter window
The results wait at a counter window, in order, like printed tokens.
fetchone() takes the next single token: call it again, you get the one after: the pile shrinks with every call. When the pile is empty, the clerk hands you None: nothing left.
fetchall() sweeps the whole remaining pile into a box (a Python list) in one motion. Sweep again immediately? The box comes back empty: the pile was already taken.
Theory
The two calls, formally
After cur.execute("SELECT ..."):
- cur.fetchone() returns the next row as a tuple, or None when the result set is exhausted. Every call advances the cursor.
- cur.fetchall() returns all remaining rows as a list of tuples (an empty list
[]if nothing remains). - cur.fetchmany(n) returns up to n rows as a list: the middle ground for big results.
Columns come by position: row[0] is the first column of your SELECT, row[1] the second.
Practical
Both styles on the marks table
import sqlite3
conn = sqlite3.connect('college.db')
cur = conn.cursor()
cur.execute("SELECT roll, score FROM marks WHERE subject = 'DBMS'")
# Style 1: one at a time
row = cur.fetchone()
print(row) # (101, 78) a TUPLE, even for one row
print(row[1]) # 78 columns by index
# Style 2: everything remaining
rest = cur.fetchall()
print(rest) # [(102, 55), (103, 91)] list of tuples
print(len(rest)) # 2
# the pile is now empty:
print(cur.fetchone()) # None
print(cur.fetchall()) # []
# Style 3: the cursor is iterable (fresh execute first!)
cur.execute("SELECT roll, score FROM marks WHERE subject = 'DBMS'")
for roll, score in cur: # tuple unpacking per row
print(roll, score)
conn.close()
Think first
Predict all four prints
The DBMS result set has exactly 3 rows. After execute, the code runs:
print(cur.fetchone())
print(len(cur.fetchall()))
print(cur.fetchall())
print(cur.fetchone())
Work out all four outputs before tapping.
Show the answer
1. (101, 78): the first row, as a tuple.
2. 2: fetchall sweeps the REMAINING rows (row 1 was already consumed).
3. []: the pile is empty; a second fetchall finds nothing.
4. None: fetchone at exhaustion.
The engine behind all four: fetch calls consume a one-way stream. Nothing you fetched ever reappears without a fresh execute().
Quiz
A program needs the single top scorer: cur.execute('SELECT name FROM ... ORDER BY score DESC LIMIT 1') then row = cur.fetchone(); print(row[0]). One day it crashes: TypeError: 'NoneType' object is not subscriptable. What happened?
- The query matched no rows, fetchone returned None, and None[0] is illegal: check for None before indexing
- fetchone returns a string, and strings cannot be indexed
- LIMIT 1 is incompatible with fetchone
- The database file was locked by another program
Show the answer
The query matched no rows, fetchone returned None, and None[0] is illegal: check for None before indexing
On an empty result (say, the marks table had no rows that day), fetchone() returns None, and indexing None explodes exactly like this. The professional pattern: row = cur.fetchone() then if row is not None: print(row[0]). Option B is backwards (rows are tuples, which index fine); LIMIT and fetchone actually pair perfectly; and a locked file raises a different, clearly-named error.
Watch out
The three fetch traps
Double fetchall: the second call returns [] and beginners hunt a 'lost data' bug for an hour. Fetch once, keep the list in a variable.
Indexing None: always possible whenever a query can be empty: test if row: first.
Forgetting the comma reality: a one-column row is the tuple ('Riya',), not the bare string: extract with row[0]. Exams show print(row) output and ask why the brackets and comma appear.
Theory
Choosing between the two
fetchall() is comfortable and right for classroom-sized data: one list, loop it freely. fetchone() (or iterating the cursor) shines when results are huge: one row in memory at a time, the same reason Unit 2 backups stream instead of loading everything. ResultDesk's report screens will fetchall; its CSV re-import checker will iterate. Next lesson closes the unit: writing changes back, and the commit() promise finally kept.
Summary
Key takeaways
- execute() prepares results; nothing reaches Python until you fetch.
- fetchone() → next row as a tuple, or None when exhausted; every call advances the cursor.
- fetchall() → all REMAINING rows as a list of tuples; [] when nothing remains.
- Fetched rows are consumed: a second fetchall returns [], never a repeat.
- Guard fetchone results: if row is None, do not index.
- Columns come by position: row[0], row[1], in the SELECT's order; single-column rows are still tuples.
- Memory hook: tokens at a counter window, taken one or swept all.