SQLAlchemy Pagination with limit, offset, and Cursors addresses a recurring problem in Python projects: Return predictable pages without repeating or skipping rows. This guide explains the mechanism, provides an executable example, and identifies the boundaries that keep an implementation reliable.
Concept and use case
Offset is simple for small result sets; cursor pagination uses the last ordered key and scales better for large tables.
For the related fundamentals, also read the SQLAlchemy guide. Integration stays simpler when functions receive dependencies and data explicitly instead of relying on global state.
Practical example
stmt = select(Post).where(Post.published.is_(True)).order_by(Post.created_at.desc(), Post.id.desc()).limit(21)
rows = session.scalars(stmt).all()
has_next = len(rows) > 20
items = rows[:20]Every page needs a total and stable order, usually with the primary key as a tie breaker.
Important decisions
The correct choice depends on the public contract, expected volume, and failure behavior.
Consider concurrency, empty inputs, and partial failures. Document every limit that affects consumers and choose names that express intent.
Common mistakes
A minimal example does not replace bounds, error handling, and observability. Without order_by, ordering is undefined. Large offsets scan work, while concurrent writes can shift rows between pages.
Avoid catching exceptions without context or returning partial output as if it were complete. An explicit failure is usually safer than silently incorrect data.
How to validate
Validate behavior, not only the happy path. Test ties, the final page, inserts between requests, and the maximum page size accepted by the API.