Lazy and Eager Loading
26 min · Intermediate
Need help? Ask a tutor on WhatsApp (opens in a new tab)When you load a customer, SQLAlchemy does not load their orders. It waits until you ask. That saves work when you do not need them, and causes trouble when you do. Loading strategies let you choose.
Lazy loading: load when first used
Lazy loading means a relationship is loaded from the database only when your code first reads it, with a separate query for each parent object.
By default a relationship is empty until you touch it. The first access runs a SELECT for that one parent. This is called lazy loading, and it is the default for relationships.
customer = session.scalars(select(Customer)).first()print(sorted(inspect(customer).unloaded))print(len(customer.orders))print(sorted(inspect(customer).unloaded))Example 1 · Lazy loading on first access
from sqlalchemy import create_engine, select, func, inspect, String, ForeignKeyfrom sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship, Session, selectinload, joinedload, raiseload
class Base(DeclarativeBase): pass
class Customer(Base): __tablename__ = "customer" id: Mapped[int] = mapped_column(primary_key=True) name: Mapped[str] = mapped_column(String(50)) orders: Mapped[list["Order"]] = relationship( back_populates="customer", cascade="all, delete-orphan" )
class Order(Base): __tablename__ = "orders" id: Mapped[int] = mapped_column(primary_key=True) customer_id: Mapped[int] = mapped_column(ForeignKey("customer.id")) total: Mapped[int] customer: Mapped["Customer"] = relationship(back_populates="orders")
engine = create_engine("sqlite://")Base.metadata.create_all(engine)
def seed(): with Session(engine) as s: for i, name in enumerate(["Asha", "Ravi", "Meena", "John", "Sara"]): s.add(Customer(name=name, orders=[Order(total=100 * (i + 1)), Order(total=50)])) s.commit()
seed()
with Session(engine) as s: c = s.scalars(select(Customer).order_by(Customer.id)).first() print(sorted(inspect(c).unloaded)) print(len(c.orders)) print(sorted(inspect(c).unloaded))['orders'] 2 []
What this code does
- Right after loading the customer,
ordersis not loaded. - Reading
c.ordersran a query. After that, nothing is unloaded.
inspect(obj).unloaded lists attributes not loaded yet. The orders appear only after the line that touches them runs an extra query.
Eager loading: load together
Eager loading means related rows are loaded together with the parent in the same query step, so reading the relationship later needs no extra query.
If you know you will need the orders, ask for them in the same query step. Add a loader option. The data is ready before you touch the attribute, so the access runs no new query.
stmt = select(Customer).options(selectinload(Customer.orders))customers = session.scalars(stmt).all()
stmt = select(Order).options(joinedload(Order.customer))orders = session.scalars(stmt).all()Example 2 · A closed session cannot load lazily
from sqlalchemy import create_engine, select, func, inspect, String, ForeignKeyfrom sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship, Session, selectinload, joinedload, raiseload
class Base(DeclarativeBase): pass
class Customer(Base): __tablename__ = "customer" id: Mapped[int] = mapped_column(primary_key=True) name: Mapped[str] = mapped_column(String(50)) orders: Mapped[list["Order"]] = relationship( back_populates="customer", cascade="all, delete-orphan" )
class Order(Base): __tablename__ = "orders" id: Mapped[int] = mapped_column(primary_key=True) customer_id: Mapped[int] = mapped_column(ForeignKey("customer.id")) total: Mapped[int] customer: Mapped["Customer"] = relationship(back_populates="orders")
engine = create_engine("sqlite://")Base.metadata.create_all(engine)
def seed(): with Session(engine) as s: for i, name in enumerate(["Asha", "Ravi", "Meena", "John", "Sara"]): s.add(Customer(name=name, orders=[Order(total=100 * (i + 1)), Order(total=50)])) s.commit()
seed()
from sqlalchemy.orm.exc import DetachedInstanceError
with Session(engine) as s: c = s.scalars(select(Customer)).first()try: print(len(c.orders))except DetachedInstanceError: print("cannot load: session closed")
with Session(engine) as s: c = s.scalars(select(Customer).options(selectinload(Customer.orders))).first()print(len(c.orders))cannot load: session closed 2
What this code does
- The first customer was loaded lazily, so after the session closed the orders could not be fetched.
- With
selectinload, the orders were fetched while the session was open, so they are still there.
Example 3 · The SQL of a joined load
from sqlalchemy import create_engine, select, func, inspect, String, ForeignKeyfrom sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship, Session, selectinload, joinedload, raiseload
class Base(DeclarativeBase): pass
class Customer(Base): __tablename__ = "customer" id: Mapped[int] = mapped_column(primary_key=True) name: Mapped[str] = mapped_column(String(50)) orders: Mapped[list["Order"]] = relationship( back_populates="customer", cascade="all, delete-orphan" )
class Order(Base): __tablename__ = "orders" id: Mapped[int] = mapped_column(primary_key=True) customer_id: Mapped[int] = mapped_column(ForeignKey("customer.id")) total: Mapped[int] customer: Mapped["Customer"] = relationship(back_populates="orders")
engine = create_engine("sqlite://")Base.metadata.create_all(engine)
def seed(): with Session(engine) as s: for i, name in enumerate(["Asha", "Ravi", "Meena", "John", "Sara"]): s.add(Customer(name=name, orders=[Order(total=100 * (i + 1)), Order(total=50)])) s.commit()
seed()
stmt = select(Order).options(joinedload(Order.customer))print(stmt)SELECT orders.id, orders.customer_id, orders.total, customer_1.id AS id_1, customer_1.name FROM orders LEFT OUTER JOIN customer AS customer_1 ON customer_1.id = orders.customer_id
What this code does
LEFT OUTER JOIN customer AS customer_1brings the customer in the same query.- The customer columns have extra names such as
id_1, so they do not clash with the order columns.
selectinload runs one extra SELECT for all parents with an IN list. joinedload adds a LEFT OUTER JOIN to the main query.
Choose the loader for the shape of the data
Loader option is a modifier added to select() with .options(), such as selectinload or joinedload, that chooses how a relationship is loaded for that query.
Use selectinload for lists, because a join repeats the parent for every child row. Use joinedload for a single parent per row, such as order.customer. Use raiseload to make a forgotten load fail loudly.
stmt = select(Customer).options(joinedload(Customer.orders))customers = session.scalars(stmt).unique().all()
stmt = select(Customer).options(raiseload(Customer.orders))customer = session.scalars(stmt).first()customer.orders # raises InvalidRequestErrorExample 4 · Eager loading a list and blocking lazy loads
from sqlalchemy import create_engine, select, func, inspect, String, ForeignKeyfrom sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship, Session, selectinload, joinedload, raiseload
class Base(DeclarativeBase): pass
class Customer(Base): __tablename__ = "customer" id: Mapped[int] = mapped_column(primary_key=True) name: Mapped[str] = mapped_column(String(50)) orders: Mapped[list["Order"]] = relationship( back_populates="customer", cascade="all, delete-orphan" )
class Order(Base): __tablename__ = "orders" id: Mapped[int] = mapped_column(primary_key=True) customer_id: Mapped[int] = mapped_column(ForeignKey("customer.id")) total: Mapped[int] customer: Mapped["Customer"] = relationship(back_populates="orders")
engine = create_engine("sqlite://")Base.metadata.create_all(engine)
def seed(): with Session(engine) as s: for i, name in enumerate(["Asha", "Ravi", "Meena", "John", "Sara"]): s.add(Customer(name=name, orders=[Order(total=100 * (i + 1)), Order(total=50)])) s.commit()
seed()
from sqlalchemy.exc import InvalidRequestError
with Session(engine) as s: stmt = select(Customer).options(joinedload(Customer.orders)).order_by(Customer.id) customers = s.scalars(stmt).unique().all() print([(c.name, sum(o.total for o in c.orders)) for c in customers[:2]])
c = s.scalars(select(Customer).options(raiseload(Customer.orders)).where(Customer.id == 3)).first() try: c.orders except InvalidRequestError: print("lazy load blocked")[('Asha', 150), ('Ravi', 250)]What this code does
unique()removes duplicate customers that the join created.raiseloadturns a forgotten eager load into an error.
A joined list needs .unique(), because each customer appears once for every order. After the session closes, a lazy attribute cannot load at all, so load what you need before returning data.
Next step
Check your understanding
11 quick questions