A Cursor Solves Where the Next SQLite Page Starts. It Does Not Freeze the Dataset.

An export reads its first page, another process changes the table, and the second page no longer belongs to the same view of the data. Replacing an offset with a cursor helps with navigation, but it does not automatically turn a changing database into a stable report.

Two decisions need separate answers: what ordering uniquely locates the next row, and which database state the complete export should describe. A reliable implementation records both rather than relying on the word “cursor” to imply them.

This tutorial uses established SQLite behavior, checked on 25 October 2026. The SQLite row-value guide describes tuple comparisons and scrolling-window queries. Its isolation documentation explains the separate transaction question. These are useful foundations for small Python exports, not new features of Python 3.15.

Choose an order that distinguishes every row

Suppose records have a time value and an identifier. Two records can share the same time. If the next query asks only for times strictly greater than the last time, it skips remaining records tied at that value.

Use a unique tie-breaker and include it in both ordering and continuation. For this schema, the ascending order is (observed_at, id). The ID is unique, and neither sort component can be null. The continuation compares the same pair.

Do not assume a timestamp alone is unique. Likewise, a collation or null policy that changes between pages can change what the order means.

Resume after the complete key

The first page needs only the ordering and a limit. Later pages add a comparison against the last returned pair. Keep the query direction consistent: ascending order uses the corresponding greater-than comparison here.

SQLite compares row values from left to right. This allows the time component to determine the order when different, and the ID to break ties when the times match. A matching composite index gives the database an appropriate access path; inspect the actual query plan for the deployed query and data.

The parameter bindings below carry values, not SQL fragments. Do not accept an arbitrary table name or ordering expression from an untrusted cursor.

Run a bounded page example

This complete example requires Python with SQLite row-value comparison support. It uses an in-memory database and only standard-library modules. The tiny integer timestamps are invented ordering values.

import sqlite3


def page(connection, after=None, size=2):
    if type(size) is not int or not 1 <= size <= 100:
        raise ValueError("size must be an integer from 1 to 100")
    if after is None:
        return connection.execute(
            "SELECT observed_at, id FROM records "
            "ORDER BY observed_at, id LIMIT ?", (size,)
        ).fetchall()
    if (type(after) is not tuple or len(after) != 2
            or any(type(value) is not int for value in after)):
        raise ValueError("after must be a pair of integers")
    return connection.execute(
        "SELECT observed_at, id FROM records "
        "WHERE (observed_at, id) > (?, ?) "
        "ORDER BY observed_at, id LIMIT ?", (*after, size)
    ).fetchall()


connection = sqlite3.connect(":memory:", isolation_level=None)
try:
    connection.executescript("""
        CREATE TABLE records (
            id INTEGER PRIMARY KEY,
            observed_at INTEGER NOT NULL
        );
        CREATE INDEX records_order ON records(observed_at, id);
    """)
    connection.executemany(
        "INSERT INTO records(observed_at, id) VALUES (?, ?)",
        [(10, 1), (10, 2), (11, 3), (11, 4), (12, 5)]
    )
    connection.execute("BEGIN")
    exported = []
    after = None
    while True:
        batch = page(connection, after=after)
        if not batch:
            break
        exported.extend(batch)
        after = batch[-1]
    connection.execute("COMMIT")
    assert exported == [(10, 1), (10, 2), (11, 3), (11, 4), (12, 5)]
    assert len({identifier for _, identifier in exported}) == 5
    print("Five rows exported once, including both timestamp ties")
finally:
    connection.close()

Decide what membership should mean

Imagine a new row appears with a key earlier than the last returned pair. A live keyset traversal will not return to collect it. A new row after the pair might appear on a later page. Updating an existing row's ordering values can also move it across the continuation boundary.

These outcomes are not contradictions in the comparison. They follow from navigating a population that changes between reads. Decide whether a live feed should accept those semantics or whether an export must describe a fixed state.

A maximum-ID cutoff is not automatically a snapshot. Rows below the cutoff can still change or disappear, and an ID policy might not represent the time a transaction became visible.

Use a transaction for a consistent local export

For the fixed-export contract, perform the page reads within the intended read transaction on one connection. In SQLite's WAL mode, a reader continues seeing its established snapshot while another connection can commit changes. The first read establishes the relevant view after a deferred BEGIN.

The transaction is a local database mechanism, not something encoded by the last row's key. A web request returning a cursor string does not preserve a transaction after its connection has been closed.

The code demonstrates the second contract. It does not implement the third.

Keep long reads proportionate to the job

A snapshot has operational costs. A long reader can affect how database maintenance and storage reclamation progress, especially with WAL files. Plan duration and workload rather than keeping a transaction open indefinitely while a user browses.

For a small report, a promptly completed local export may be sufficient. A large job may need a dedicated snapshot, an immutable source version, or a different data-delivery design. Record the chosen consistency boundary in the export manifest.

Keyset pagination can avoid increasingly large offset scans in suitable indexed queries, but it does not guarantee a speedup for every query. Filtering, joins and index selection still matter.

Test ties and concurrent changes separately

Test page sizes of one, two and a value larger than the table. Verify that all expected IDs appear exactly once. Include repeated times so a missing tie-breaker cannot pass unnoticed.

Then use a file-backed WAL database with separate reader and writer connections. After the reader's first page, commit new rows from the writer. Check that the existing read transaction retains its view, then check that a fresh transaction sees the additions.

That test establishes an exercised consistency behavior. It does not certify a distributed database, a network filesystem, or a cursor service with different connection handling.

Preserve enough context to reproduce the export

Store the source identity, schema or export version, ordering, filter and snapshot policy beside the output. If exporting sensitive records, apply the access checks independently of the continuation token. A position is not permission to read another account's rows.

The practical result is a paginator with an explicit boundary: the pair identifies where to continue, while a separate consistency policy identifies what is being traversed. Both are needed before a page-by-page process can make a defensible completeness claim.

Sources and further reading

• SQLite row values and scrolling-window queries, checked 25 October 2026.
• SQLite isolation and Python sqlite3 reference, checked 25 October 2026.
• Related: SQLite query plans and JSON Lines completion boundaries.