Complete worked example
python
from sqlalchemy import create_engine, event, ForeignKey, select
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship, Session
class Base(DeclarativeBase):
pass
class Employer(Base):
__tablename__ = "employers"
id: Mapped[int] = mapped_column(primary_key=True)
roles: Mapped[list["Role"]] = relationship(back_populates="employer")
class Role(Base):
__tablename__ = "roles"
id: Mapped[int] = mapped_column(primary_key=True)
employer_id: Mapped[int] = mapped_column(ForeignKey("employers.id", ondelete="RESTRICT"))
employer: Mapped[Employer] = relationship(back_populates="roles")
from sqlalchemy.orm import selectinload, raiseload
def inspect_example(counts):
engine = create_engine("sqlite+pysqlite://", connect_args={"autocommit": False})
try:
Base.metadata.create_all(engine)
with Session(engine) as session, session.begin():
for i, count in enumerate(counts, 1):
session.add(Employer(id=i, roles=[Role() for _ in range(count)]))
selects = [0]
@event.listens_for(engine, "before_cursor_execute")
def count_selects(connection, cursor, statement, parameters, context, many):
if statement.lstrip().upper().startswith("SELECT"):
selects[0] += 1
with Session(engine) as session:
statement = (select(Employer).order_by(Employer.id)
.options(selectinload(Employer.roles), raiseload("*")))
employers = session.scalars(statement).all()
result = [[employer.id, len(employer.roles)] for employer in employers]
return {"rows": result, "selects": selects[0]}
finally:
engine.dispose()- Reasoning
- The worked statement applies selectinload to Employer.roles and raiseload to other unplanned relationships. For the small nonempty fixture, one SELECT loads employers and a second loads their role collections. With no employers, only the first SELECT runs. Counts [2, 0, 1] remain [[1, 2], [2, 0], [3, 1]]. selectinload does not mean every possible production graph always needs exactly two queries: larger key sets may be batched and nested paths add work. Here the explicit query count belongs to the bounded fixture. It is useful evidence because it measures real execution instead of inferring efficiency from the source looking short.