APPLYDJINN
← SQLAlchemy and Alembic: reliable database workProgress & review ↗

Typorithm · easy

Filtered and deterministic bounded pages

pythonsqlalchemyalembicdatabasesindependent-practice
Saved on this device
Full lesson · 4 learning steps Open any exercise for focused practice
1

Step 1 of 4 · intro card

Filtered and deterministic bounded pages

0%

Concept and contract

Combine a precise population predicate with a total order and a validated result limit.

A useful page contract specifies the admitted population, ordering and size together. A LIMIT without a deterministic ORDER BY does not define which equal-ranked records come first. Sorting by a nonunique score alone leaves ties unresolved, so a stable unique key is needed as a final ordering term.

The owned fixture admits scores at or above the minimum, ranks higher scores first, then chooses the smaller ID for ties. Its deliberately tiny limit must be an actual integer from one to three. Rejecting bool matters because Python otherwise treats it as an integer subtype.

Mechanism and worked example

The statement builds its WHERE from a column expression, orders by score descending and ID ascending, and applies the validated limit.

The statement builds its WHERE from a column expression, orders by score descending and ID ascending, and applies the validated limit. For [3, 9, 9, 1], minimum 3 and limit 2, the expected IDs are [2, 3]. The exact tied order is part of the declared result.

Input values become bound parameters through SQLAlchemy’s expression system. The code does not splice a caller-controlled SQL fragment into ORDER BY. It creates only the rows needed for this owned example; a production page must additionally define how concurrent changes affect later requests and which cursor or snapshot semantics it promises.

Complete worked example

python

from sqlalchemy import create_engine, select, text
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session

class Base(DeclarativeBase):
    pass

class Role(Base):
    __tablename__ = "roles"
    id: Mapped[int] = mapped_column(primary_key=True)
    score: Mapped[int]

def inspect_example(scores, minimum, limit):
    if type(limit) is not int or not 1 <= limit <= 3:
        raise ValueError("limit must be an integer between one and three")
    engine = create_engine("sqlite+pysqlite://", connect_args={"autocommit": False})
    try:
        Base.metadata.create_all(engine)
        with Session(engine) as session, session.begin():
            session.add_all(Role(id=i, score=value) for i, value in enumerate(scores, 1))
        with Session(engine) as session:
            statement = (select(Role.id).where(Role.score >= minimum)
                         .order_by(Role.score.desc(), Role.id).limit(limit))
            return list(session.scalars(statement))
    finally:
        engine.dispose()
Reasoning
The statement builds its WHERE from a column expression, orders by score descending and ID ascending, and applies the validated limit. For [3, 9, 9, 1], minimum 3 and limit 2, the expected IDs are [2, 3]. The exact tied order is part of the declared result. Input values become bound parameters through SQLAlchemy’s expression system. The code does not splice a caller-controlled SQL fragment into ORDER BY. It creates only the rows needed for this owned example; a production page must additionally define how concurrent changes affect later requests and which cursor or snapshot semantics it promises.
Download the SQLAlchemy and Alembic computer lab ↗

Focused decision: context is in the complete example

python

statement = (select(Role.id).where(Role.score >= minimum)
             .order_by(Role.score.desc(), Role.id).limit(limit))
Database decision
The final ID term makes ties deliberate. The task changes only that term; the filter, result bound and database-side ordering remain part of the contract.
Download the SQLAlchemy and Alembic computer lab ↗

Counterexamples and common mistakes

The controlled mistake orders by ID alone.

The controlled mistake orders by ID alone. It returns a bounded list of real IDs, but the highest scores are no longer selected first. A length-only test would pass despite choosing the wrong records. Removing the ID tie-breaker would introduce another defect: equal scores would no longer have a promised total order.

Do not accept zero, an unbounded arbitrary limit or True merely because a database accepts the resulting statement. Validation belongs to this API contract. Likewise, OFFSET pagination does not automatically stay stable while rows are inserted or scores change; a unique tie-breaker is necessary but is not a universal consistency guarantee.

Controlled bug: observe the rejected database contract

python

from sqlalchemy import create_engine, select, text
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session

class Base(DeclarativeBase):
    pass

class Role(Base):
    __tablename__ = "roles"
    id: Mapped[int] = mapped_column(primary_key=True)
    score: Mapped[int]

def inspect_example(scores, minimum, limit):
    if type(limit) is not int or not 1 <= limit <= 3:
        raise ValueError("limit must be an integer between one and three")
    engine = create_engine("sqlite+pysqlite://", connect_args={"autocommit": False})
    try:
        Base.metadata.create_all(engine)
        with Session(engine) as session, session.begin():
            session.add_all(Role(id=i, score=value) for i, value in enumerate(scores, 1))
        with Session(engine) as session:
            statement = (select(Role.id).where(Role.score >= minimum)
                         .order_by(Role.id).limit(limit))
            return list(session.scalars(statement))
    finally:
        engine.dispose()
Reasoning
The controlled mistake orders by ID alone. It returns a bounded list of real IDs, but the highest scores are no longer selected first. A length-only test would pass despite choosing the wrong records. Removing the ID tie-breaker would introduce another defect: equal scores would no longer have a promised total order. Do not accept zero, an unbounded arbitrary limit or True merely because a database accepts the resulting statement. Validation belongs to this API contract. Likewise, OFFSET pagination does not automatically stay stable while rows are inserted or scores change; a unique tie-breaker is necessary but is not a universal consistency guarantee.
Download the SQLAlchemy and Alembic computer lab ↗

Independent practice

First reconstruct the inclusive filter and the two ordering terms.

First reconstruct the inclusive filter and the two ordering terms. For independent practice keep score descending, but put the larger ID first when scores tie. Change only that tie-breaker and preserve the maximum size, minimum predicate and invalid-limit behavior.

Use at least two equal high scores so the new rule can be observed. An input with all distinct scores would not distinguish the old and new tie policy. Keep an empty population case and a rejected zero limit to show that the task has not silently relaxed the original boundary conditions.

Step 1: Keep score descending but put the larger ID first when scores are tied. Step 2: Retain the inclusive filter and the limit validation.

Practice starter: solve before reading the solution

python

from sqlalchemy import create_engine, select, text
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session

class Base(DeclarativeBase):
    pass

class Role(Base):
    __tablename__ = "roles"
    id: Mapped[int] = mapped_column(primary_key=True)
    score: Mapped[int]

def inspect_example(scores, minimum, limit):
    if type(limit) is not int or not 1 <= limit <= 3:
        raise ValueError("limit must be an integer between one and three")
    engine = create_engine("sqlite+pysqlite://", connect_args={"autocommit": False})
    try:
        Base.metadata.create_all(engine)
        with Session(engine) as session, session.begin():
            session.add_all(Role(id=i, score=value) for i, value in enumerate(scores, 1))
        with Session(engine) as session:
            statement = (select(Role.id).where(Role.score >= minimum)
                         .order_by(Role.score.desc(), Role.id).limit(limit))
            return list(session.scalars(statement))
    finally:
        engine.dispose()

# Task: Keep score descending but put the larger ID first when scores are tied.
Reasoning
Keep score descending but put the larger ID first when scores are tied.
Download the SQLAlchemy and Alembic computer lab ↗
  • Hint 1: Change the ordering direction of Role.id, not of score.
  • Hint 2: Use a tie at the last admitted position in the page.

Solution, tests and trade-offs

The solution orders by score descending and ID descending.

The solution orders by score descending and ID descending. For the first fixture its result becomes [3, 2], while a single allowed tie returns the larger ID. The acceptance suite checks complete ordered IDs rather than sorting the result after execution, because that would hide a database ordering error.

The tests also keep the caller’s scores unchanged. This bounded page is an intentional teaching contract, not a production tuning recommendation of three records. Query plans, indexes and service-specific limits should be measured against the actual database and workload instead of copied from a tiny lesson fixture.

Practice solution and acceptance checks

python

from sqlalchemy import create_engine, select, text
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session

class Base(DeclarativeBase):
    pass

class Role(Base):
    __tablename__ = "roles"
    id: Mapped[int] = mapped_column(primary_key=True)
    score: Mapped[int]

def inspect_example(scores, minimum, limit):
    if type(limit) is not int or not 1 <= limit <= 3:
        raise ValueError("limit must be an integer between one and three")
    engine = create_engine("sqlite+pysqlite://", connect_args={"autocommit": False})
    try:
        Base.metadata.create_all(engine)
        with Session(engine) as session, session.begin():
            session.add_all(Role(id=i, score=value) for i, value in enumerate(scores, 1))
        with Session(engine) as session:
            statement = (select(Role.id).where(Role.score >= minimum)
                         .order_by(Role.score.desc(), Role.id.desc()).limit(limit))
            return list(session.scalars(statement))
    finally:
        engine.dispose()
Reasoning
The solution orders by score descending and ID descending. For the first fixture its result becomes [3, 2], while a single allowed tie returns the larger ID. The acceptance suite checks complete ordered IDs rather than sorting the result after execution, because that would hide a database ordering error. The tests also keep the caller’s scores unchanged. This bounded page is an intentional teaching contract, not a production tuning recommendation of three records. Query plans, indexes and service-specific limits should be measured against the actual database and workload instead of copied from a tiny lesson fixture.
Download the SQLAlchemy and Alembic computer lab ↗

Interview reasoning and next step

Explain how WHERE, ORDER BY and LIMIT combine into one observable promise.

Explain how WHERE, ORDER BY and LIMIT combine into one observable promise. Give a pair of records that distinguishes the two tie rules and a test input that would fail to reveal the change. Describe why a total order still does not freeze a changing database between separate requests.

Next, represent ownership with a foreign key and a relationship. Pagination over joined rows introduces additional population questions, so first understand which table’s identities are being paged and whether one parent can multiply into several SQL rows. Those distinctions will matter in the loading and aggregate lessons.

Optional stretch — after the core task works: Design a cursor containing both score and ID, then reason about a changed score between requests.

Computer practice: https://applydjinn.com/typorithm/sqlalchemy-lab-20261107.zip. Install the pinned requirements in an isolated Python 3.13 environment and run verify.py. Edit the working exercise baseline, then run --practice sa-pagination. The lab uses disposable SQLite databases and actual SQLAlchemy/Alembic calls; it does not connect to application accounts, production databases or paid services. The app includes prepared results and explained questions without an editor or Python runtime.

Combine a precise population predicate with a total order and a validated result limit.