If your Python SQLite loop visits its first row and then ends after an inner query, check whether both queries use the same cursor. In our disposable example, the outer query has three parent rows. Reusing its cursor for a count query leaves only one parent processed. A second variable pointing at that cursor does not protect the outer result.
This guide uses synchronous Python sqlite3, one connection and read-only nested queries. It does not diagnose asynchronous drivers, connection pooling, concurrent threads or missing commits. Start with a small fixture before changing a production job that writes data.
Reproduce the missing rows in memory
Run this standalone example. It creates no database file and closes its connection even if an assertion fails.
import sqlite3
con = sqlite3.connect(":memory:")
try:
con.executescript("""
CREATE TABLE parent(id INTEGER PRIMARY KEY);
CREATE TABLE child(parent_id INTEGER, name TEXT);
INSERT INTO parent VALUES(1), (2), (3);
INSERT INTO child VALUES(1, 'a'), (1, 'b'), (3, 'c');
""")
cur = con.cursor()
result = cur.execute("SELECT id FROM parent ORDER BY id")
assert result is cur
visited = []
for (parent_id,) in result:
count = cur.execute(
"SELECT count(*) FROM child WHERE parent_id=?",
(parent_id,),
).fetchone()[0]
visited.append((parent_id, count))
print(visited)
assert visited == [(1, 2)]
finally:
con.close()The printed list contains parent 1 only. Our inner count query returns one row, and fetchone() consumes it. The next outer-loop step has no remaining row from that inner query. This exact output depends on the fixture: another inner SELECT could expose a different row shape or unexpected values instead of simply ending the loop.
Python documents cursors as result iterators and Connection.execute() as creating a new cursor. Combined with the identity assertion and observed output above, that explains why renaming result did not isolate the first query. The experiment checks result ownership; it makes no throughput claim.
Keep the outer reader separate from the lookup
Replace only the cursor-and-loop section inside the example's try block with the following. Keep the table setup and finally cleanup.
reader = con.cursor()
lookup = con.cursor()
visited = []
for (parent_id,) in reader.execute(
"SELECT id FROM parent ORDER BY id"
):
count = lookup.execute(
"SELECT count(*) FROM child WHERE parent_id=?",
(parent_id,),
).fetchone()[0]
visited.append((parent_id, count))
assert visited == [(1, 2), (2, 0), (3, 1)]Here the inner query has its own result state. Our fixture now processes every parent, including the parent with no child. Keep the parameter tuple's comma: (parent_id,) is a one-item tuple. Avoid constructing a SQL value by string interpolation.
The shorter form uses con.execute(...) for both queries. Each call returns a new cursor, so the outer iterator and inner lookup are separate. Do not turn that into cur.execute(...) during a cleanup refactor without rerunning a fixture that checks all expected parent IDs.
Two cursor objects still belong to one connection. They do not give you independent transactions or permission to move that connection between threads. Connection lifetime is a separate concern; see why a SQLite connection context does not close it.
For this count task, remove the nested queries
Our fixture can produce the same result with one query:
SELECT p.id, count(c.parent_id)
FROM parent AS p
LEFT JOIN child AS c ON c.parent_id = p.id
GROUP BY p.id
ORDER BY p.id;We verified [(1, 2), (2, 0), (3, 1)] for this query as well. The LEFT JOIN preserves parent 2. Counting the child join key avoids counting its unmatched placeholder row as a child. This is a fixture-specific rewrite: choose columns and grouping for your actual schema, and check any additional joins that might multiply rows.
The separate-cursor approach issues one outer query plus one lookup per parent in this example. A single query removes those repeated calls, but we did not benchmark it. Compare actual query plans and representative data before promising a speed improvement.
Buffering works only when the buffer covers the remaining work
Fetching all outer rows before the inner loop also passed our fixture, because the Python list no longer relies on a live result. That can be reasonable for a known small set. Avoid making it the default fix for a large export: the complete result must fit in memory alongside the rest of the process.
fetchmany(2) protects the two already-fetched rows only. In our additional check, executing another query on the same cursor then consuming its count row left the next fetchmany(2) empty. Use a separate lookup cursor if subsequent batches still come from the outer reader. A copied batch does not preserve that reader's future result state.
Do not assume a second cursor freezes a table you modify
The examples above read both tables without changing them during traversal. SQLite documents that changes on the same connection while a SELECT is still running can have undefined visibility to that SELECT. Separate cursors solve result replacement; they do not create a stable snapshot of a table being changed.
For a write job, design the target set before executing side effects. A bounded list of IDs, a deliberately staged work set or a suitable set-based statement may fit the application. Define transaction ownership and failure recovery separately, then test them on disposable data. Do not rely on one observed traversal order to establish a guarantee about modifications during a live scan.
Check the whole result before rerunning your job
Our reusable fixture compares the broken loop, separate cursors, connection shortcuts, a buffered list and the LEFT JOIN. It also checks the batch-fetch trap. Local runs cover Python 3.11, 3.13 and 3.14 with their installed SQLite libraries; they do not validate every Python distribution or your production data.
For your own regression check, include zero, one and several outer rows, an empty child set, and a parent with several children. Assert both IDs and counts. A loop that exits without an exception can still have skipped most of its work.