Select Queries

Read SQLite rows with SELECT, then take one row, a limited batch, or every remaining row using fetchone, fetchmany, and fetchall.

Example

import sqlite3

con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute(
    "CREATE TABLE badge (code INTEGER PRIMARY KEY, name TEXT NOT NULL, room TEXT NOT NULL)"
)
cur.execute("INSERT INTO badge (code, name, room) VALUES (?, ?, ?)", (1, "Avery", "North"))
cur.execute("INSERT INTO badge (code, name, room) VALUES (?, ?, ?)", (2, "Jordan", "East"))
cur.execute("INSERT INTO badge (code, name, room) VALUES (?, ?, ?)", (3, "Mina", "West"))
con.commit()
cur.execute("SELECT name FROM badge WHERE code = ?", (2,))
print(cur.fetchone())
cur.execute("SELECT name FROM badge ORDER BY code")
print(cur.fetchmany(2))
cur.execute("SELECT name, room FROM badge ORDER BY code")
print(cur.fetchall())
cur.execute("SELECT name FROM badge WHERE code = ?", (9,))
print(cur.fetchone())
con.close()

fetchone() prints ('Jordan',). fetchmany(2) prints Avery and Jordan, because ORDER BY code makes the order stable and the count stops at 2. fetchall() prints the three names with their rooms. A missing code prints None. The course name for this read is DRL, the data retrieval query, and the statement is SELECT. Each example executes the select again so the fetch starts at the first row. A second fetch on the same result continues where the cursor stopped. If this browser has no sqlite3, the playground says so and still prints these results.

Key takeaways

• SELECT reads rows; it does not change them

• WHERE keeps the rows that match, and ORDER BY makes the order stable

• fetchone() returns one tuple, or None when no row is left

• fetchmany(2) returns up to two rows

• fetchall() returns every remaining row as a list of tuples

• Next: Query Metadata — read the column names from the cursor

• Previous: DML Queries