中文
devexamples

Cursor Pagination for Stable Pages

Paginate a query by comparing against the last seen key instead of a numeric offset, so pages stay stable when rows are inserted.

PythonData Fetching

Code

snippet
import sqlite3
from typing import List, Optional, Tuple


def page_by_cursor(
    connection: sqlite3.Connection,
    after_id: Optional[int],
    limit: int,
) -> Tuple[List[Tuple[int, str]], Optional[int]]:
    if limit <= 0 or limit > 100:
        raise ValueError("limit must be between 1 and 100")
    if after_id is None:
        rows = connection.execute(
            "SELECT id, name FROM items ORDER BY id ASC LIMIT ?", (limit,)
        ).fetchall()
    else:
        rows = connection.execute(
            "SELECT id, name FROM items WHERE id > ? ORDER BY id ASC LIMIT ?",
            (after_id, limit),
        ).fetchall()
    next_cursor = rows[-1][0] if len(rows) == limit else None
    return rows, next_cursor

Related Examples