skip to content

Querying Mapped Classes

ORM queries in the 2.0 style are select() statements over mapped classes, run through the Session. Interviewers ask how results come back, how loading is tuned and how bulk writes skip objects.

on this pageshow

explore

questions

17

In SQLAlchemy 2.1, what SQL does relationship()'s default lazy='select' strategy emit when code first reads Order.customer and Order.lines?

level: juniorimportance: must knowfreq 70%

answer

  1. first attribute access, not query time
  2. one SELECT per object per attribute
  3. many-to-one checks the identity map first
  4. WHERE ? = lines.order_id

basics

~20 s

Nothing at query time. On first access each Order fires its own SELECT for its lines, and Order.customer fires a SELECT by primary key unless that Customer is already in the Session, in which case no SQL runs.

solid answer

~40 s

`relationship()` defaults to `lazy="select"`, so `select(Order)` loads only the `orders` columns. The first read of `order.lines` emits `SELECT ... FROM lines WHERE ? = lines.order_id` for that one order, inside the Session's current transaction, and the result stays on the object. A simple many-to-one such as `order.customer` first looks up the Customer by primary key in the Session's identity map and emits `SELECT ... FROM customers WHERE customers.id = ?` only on a miss. So a loop over 50 orders that touches both attributes, with 5 distinct customers, runs 1 + 5 + 50 = 56 statements. The fix is a per-query loader option such as `selectinload()` or `joinedload()`, or a different `lazy=` value on the mapping.

code

python · 46 lines
python
from sqlalchemy import ForeignKey, create_engine, event, select
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column, relationship


class Base(DeclarativeBase):
    pass


class Customer(Base):
    __tablename__ = "customers"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str]


class Order(Base):
    __tablename__ = "orders"
    id: Mapped[int] = mapped_column(primary_key=True)
    customer_id: Mapped[int] = mapped_column(ForeignKey("customers.id"))
    customer: Mapped[Customer] = relationship()  # lazy="select" by default
    lines: Mapped[list["Line"]] = relationship()


class Line(Base):
    __tablename__ = "lines"
    id: Mapped[int] = mapped_column(primary_key=True)
    order_id: Mapped[int] = mapped_column(ForeignKey("orders.id"))
    qty: Mapped[int]


engine = create_engine("sqlite://")
Base.metadata.create_all(engine)
with Session(engine) as s, s.begin():
    customers = [Customer(name=f"c{i}") for i in range(5)]
    for i in range(50):
        s.add(Order(customer=customers[i % 5], lines=[Line(qty=1), Line(qty=2)]))

statements = []
event.listen(engine, "before_cursor_execute",
             lambda conn, cur, stmt, params, ctx, many: statements.append(stmt))

with Session(engine) as s:
    orders = s.scalars(select(Order).limit(50)).all()  # 1 SELECT
    for order in orders:
        order.customer.name  # SELECT only when the customer is not yet in the Session
        len(order.lines)     # one SELECT per order, every time
    print(len(statements))   # 56 = 1 + 5 customers + 50 collections

go deeper

for a junior

Recall that relationship() defaults to lazy='select' and that the related rows load on first attribute access, one SELECT per object.

for a middle

Explain the two SQL shapes, the collection SELECT keyed by the parent id and the many-to-one lookup that skips SQL on an identity-map hit, and how lazyload() overrides a mapping.

for a senior

Read an engine log, count the repeated child SELECTs on a list page, and say which accesses are identity-map hits and which loader option removes the rest.

for a principal

Lazy-by-default keeps simple scripts cheap but lets list pages degrade silently; be ready to say how a team spots that before production.

## What the default strategy is Every `relationship()` in SQLAlchemy 2.1 has a **loader strategy**: the rule for when and how the related rows are fetched. Unless the mapping or the statement says otherwise, the strategy is `lazy="select"`, usually called **lazy loading**. The attribute starts out unloaded, and the first time Python code reads it, SQLAlchemy emits a SELECT to fill it. The name "select" comes from that SELECT. Take an order page with three classes: `Order`, its many-to-one `Order.customer`, and its one-to-many collection `Order.lines`. The statement `session.scalars(select(Order).limit(50)).all()` emits exactly one SELECT, against `orders` only. Neither relationship is touched yet. ## The SQL each attribute emits What happens on first access depends on the kind of relationship: - **A collection** (`Order.lines`): SQLAlchemy emits `SELECT lines.id, lines.order_id, lines.qty FROM lines WHERE ? = lines.order_id`, bound to that one order's primary key. Each order runs its own copy, because each collection belongs to one parent. - **A simple many-to-one** (`Order.customer`): the foreign key already on the order row (`orders.customer_id`) names the target's primary key. SQLAlchemy first looks for that Customer in the Session's **identity map**, the per-Session table of objects keyed by primary key. A hit returns the object with **no SQL at all**; a miss emits `SELECT customers.id, customers.name FROM customers WHERE customers.id = ?`. - **After the first load**, the attribute holds its value; reading it again emits nothing until the object is expired (a commit does that by default) or refreshed. The identity-map shortcut only applies to a "simple" many-to-one, where the join condition is the target's primary key compared to a plain foreign key, with no extra criteria. That is why a page of 50 orders that belong to 5 customers emits 5 customer SELECTs, not 50, while the 50 `lines` collections always cost 50. ## Reading it in the SQL log Turn on `create_engine(..., echo=True)` or the `sqlalchemy.engine` logger and the pattern is unmistakable: one SELECT against the parent table, then the same SELECT against the child table repeated with a different bound parameter each time. Each repeat is marked `[cached since ...]` because the compiled statement is reused, so the log shows the same SQL text over and over with only the parameter changing. Counting those repeats is how the extra statements are found; the general method of counting statements per request belongs to the ORM concepts material on N+1 detection. | Access | Strategy `select` emits | When it emits nothing | |---|---|---| | `order.lines` (collection) | `SELECT ... FROM lines WHERE ? = lines.order_id` | once loaded, until expired | | `order.customer` (simple many-to-one) | `SELECT ... FROM customers WHERE customers.id = ?` | the Customer is already in the identity map | ## Where the lazy load runs and when it fails A lazy load always runs through the Session the object belongs to, in that Session's current transaction. Two consequences follow: 1. If the object is detached (its Session was closed), the lazy load cannot run and SQLAlchemy raises `DetachedInstanceError`; detachment itself is covered with the Session's flush, identity and expiry rules. 2. Under the asyncio extension, implicit IO on attribute access is not available, so lazy loading fails there; that failure belongs to the asyncio material. ## Changing the strategy There are two places to change it: - **Per query**, with a loader option on the statement: `select(Order).options(selectinload(Order.lines))` loads every collection in one extra SELECT. `lazyload(Order.lines)` does the reverse and forces lazy loading for a relationship whose mapping says otherwise. - **On the mapping**, with `relationship(lazy="selectin")`, `lazy="joined"` or `lazy="raise"`. This becomes the default for every query that does not state an option. A lazy relationship can still carry instructions for what happens when it does load. `select(Order).options(lazyload(Order.lines).joinedload(Line.product))` returns orders without their lines, but each lazy load of `order.lines` then joins `products` into that SELECT, so the lines arrive with their products. Those sub-options stay attached to the loaded `Order` objects while they remain in memory, so a reload of `order.lines` after expiry joins `products` again. ## Quick checks before blaming lazy loading 1. Count the repeated statements in the log and note which attribute each one fills. 2. Separate collections, which always cost one SELECT per parent, from simple many-to-ones, which may be identity-map hits. 3. Check whether the loop runs over a page of objects or a single one; one object's lazy loads are rarely worth an option. Lazy loading is not wrong in itself. It is cheap when only a few objects are touched, and the many-to-one identity-map shortcut can make it nearly free for a small set of targets. It becomes expensive when a loop touches the same collection on many parents, which is exactly what a list page does.

  • Why does the loop above emit 5 customer SELECTs but 50 line SELECTs?
    Order.customer is a simple many-to-one, so the lazy loader first checks the identity map for that customer's primary key; after the first order of each customer, every later access is a hit. A collection has no such key to look up, so each order's lines need their own SELECT.
  • How do you force lazy loading in one query for a relationship mapped with lazy='selectin'?
    Add the lazyload() option for that attribute, for example select(Order).options(lazyload(Order.lines)). A per-query option overrides the mapping's lazy setting for that statement only; every other query keeps the mapped default.

  • select(Order) loads the related lines and customer up front by default
  • Every lazy many-to-one access always emits its own SELECT
  • A lazy-loaded attribute re-queries the database on every read
  • Lazy loads run outside the Session's transaction
  • The default value of relationship(lazy=...) is 'selectin'
open as a page

With a SQLAlchemy Session, what does session.execute(select(Ticket)) return compared with session.scalars(select(Ticket)), and when do you use each?

level: juniorimportance: must knowfreq 70%

basics

~20 s

session.execute(select(Ticket)) returns a Result of Row objects, each a one-element row holding a Ticket; session.scalars() returns a ScalarResult that yields the Ticket objects themselves. Use scalars() for one entity, execute() when rows carry several values.

open as a page

In SQLAlchemy 2.1, how do selectinload() and joinedload() differ in the SQL they emit, and which suits a collection versus a many-to-one?

level: middleimportance: must knowfreq 75%

basics

~20 s

selectinload() runs a second SELECT whose IN list holds the parents' keys; joinedload() adds a LEFT OUTER JOIN to the main SELECT. The guide recommends selectinload() for collections and joinedload() for many-to-ones, and a joined collection needs unique().

open as a page

With a SQLAlchemy 2.1 Session, how do you list open tickets with each customer's name in one query, and what does each result row contain?

level: middleimportance: must knowfreq 58%

basics

~10 s

Select both and join along the relationship: select(Ticket, Customer.name).join(Ticket.customer). Session.execute() then yields Row objects pairing a Ticket object with a string, read by unpacking or as row.Ticket and row.name.

open as a page

With a SQLAlchemy 2.1 Session, how do you update or delete every Product row that matches a condition without loading the objects?

level: juniorimportance: should knowfreq 35%

basics

~10 s

Build update(Product).where(...).values(...) or delete(Product).where(...) and pass it to Session.execute(). SQLAlchemy sends one UPDATE or DELETE for all matching rows, loads no objects, and commits with the session's transaction.

open as a page

In SQLAlchemy 2.1, why is Session.add_all() slower than an ORM bulk insert for 200,000 Product rows?

level: middleimportance: should knowfreq 32%

basics

~20 s

add_all() builds and tracks an instrumented Product per row; flush then sorts the unit of work, fires per-object events and reads every generated key back, with RETURNING where supported. An ORM bulk insert sends plain dicts in executemany batches and skips all of that.

open as a page

In SQLAlchemy 2.1, what does session.execute(insert(Product), list_of_dicts) do, and what does it skip compared with adding Product objects?

level: middleimportance: should knowfreq 30%

basics

~20 s

Passing insert(Product) and a separate list of dicts to Session.execute() selects ORM bulk INSERT mode: rows go out as executemany batches keyed by mapped attribute names, with no Product objects, relationship cascades or mapper events.

open as a page

In SQLAlchemy 2.1, how do load_only(), defer() and undefer() decide which columns load for a page listing 10,000 Event rows?

level: middleimportance: should knowfreq 40%

basics

~20 s

load_only() names the columns to fetch, plus the primary key; defer() drops one column; undefer() fetches a column the mapping defers. Reading an unloaded column runs one SELECT per object unless raiseload=True makes it raise.

open as a page

In SQLAlchemy, how do you return each customer's newest ticket as Ticket objects, and why must the second reference to Ticket be built with aliased()?

level: middleimportance: should knowfreq 36%

basics

~20 s

aliased(Ticket) creates a second, separately named reference to the ticket table, so one select() can compare each ticket with its customer's newer tickets. Without it, joining Ticket to itself raises InvalidRequestError, because SQLAlchemy cannot tell the two sides apart.

open as a page

In SQLAlchemy 2.1, what does each common legacy session.query() call become in 2.0-style select(), and which results change when you convert it?

level: middleimportance: should knowfreq 46%

basics

~20 s

Build select(Ticket) with where(), join() and options(), then run it with session.scalars() or session.execute(). Watch what Query did implicitly: deduplicating entities, adding LIMIT in first(), wrapping count() in a subquery, and aiming filter_by() at the last joined entity.

open as a page

A SQLAlchemy pricing job runs update(Product).where(...).values(...) with synchronize_session=False, and a Product loaded earlier still shows its old price. Why, and what should it use?

level: seniorimportance: should knowfreq 28%

basics

~20 s

Loaded objects change only when synchronize_session tells the Session which ones an UPDATE hit; False skips that, so the Product keeps its old price until expired or refreshed. The default 'auto' evaluates the WHERE in Python, using 'fetch' when it cannot.

open as a page

In a SQLAlchemy 2.1 Session, why can a query's loader options seem ignored for objects already loaded, and what makes them apply?

level: seniorimportance: should knowfreq 30%

basics

~10 s

A row matching an object already in the Session is not re-applied, so loaded attributes stay and loader options attached earlier stay too. execution_options(populate_existing=True) overwrites those objects and applies the new options.

open as a page

A SQLAlchemy query select(Customer).join(Customer.tickets).where(Ticket.status == 'open').options(contains_eager(Customer.tickets)) demands unique(), and once fixed some customers still show closed tickets; why?

level: seniorimportance: should knowfreq 30%

basics

~20 s

contains_eager() fills Customer.tickets from your own join, so each customer repeats once per ticket row and the ORM refuses to return them until unique() is called. Customers already loaded in the Session keep their old, unfiltered collection unless populate_existing=True is set.

open as a page

As a lead, should a SQLAlchemy 2.1 codebase make lazy loads fail by default with lazy='raise' or raiseload('*'), and what does that rule cost?

level: principalimportance: should knowfreq 28%

basics

~20 s

Often yes for request-serving reads, but choose the scope: lazy='raise' on mappings forces every query to name its loaders, raiseload('*') guards selected statements. The cost is more verbose queries and InvalidRequestError in untested code paths.

open as a page

How does SQLAlchemy 2.1's ORM bulk UPDATE by primary key work when Session.execute() gets update(Product) and a list of dicts?

level: middleimportance: nice to knowfreq 18%

basics

~20 s

Session.execute(update(Product), [dicts]) runs UPDATE ... WHERE product.id = ? as an executemany, one parameter set per dict. Each dict must hold the full primary key; there is no RETURNING, and the 'fetch' sync strategy is refused.

open as a page

How does SQLAlchemy's yield_per execution option change an ORM select(Product) over 200,000 rows, and what will it not combine with?

level: middleimportance: nice to knowfreq 22%

basics

~20 s

By default the ORM fetches every row and builds every Product before returning. With execution_options(yield_per=1000) it streams, using a server-side cursor where the driver supports one, building 1,000 objects at a time; unique() and joined-eager collections raise.

open as a page

As the lead of a SQLAlchemy 2.1 codebase with hundreds of legacy session.query() calls, how do you decide whether, when and how to move them to select()?

level: principalimportance: nice to knowfreq 24%

basics

~20 s

Query still runs in SQLAlchemy 2.1 without warnings, so migrating is a cost decision, not a deadline. Ban new session.query() use, convert code when touched or moved to AsyncSession, which has no query(), and test conversions for changed results.

open as a page