skip to content

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%

answer

  1. build a statement, then execute it
  2. Query did things implicitly
  3. deduplication, LIMIT, subquery count
  4. filter_by() target differs in 2.1

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.

solid answer

~30 s

`session.query(Ticket).filter(...).all()` becomes `session.scalars(select(Ticket).where(...)).all()`; `.one()` becomes `.scalar_one()` on `session.execute()`; `.first()` becomes `session.scalars(stmt.limit(1)).first()`, because `Result.first()` adds no LIMIT while `Query.first()` did; `.count()` becomes `session.scalar(select(func.count()).select_from(Ticket).where(...))`. Several results change, most of them quietly. `Query` deduplicated full-entity results by primary key, so a joined `select(Customer)` now returns a customer once per joined row. A joined eager load of a collection now needs `.unique()`. `Query.count()` counted a subquery of the whole query; the direct form counts rows itself. And in SQLAlchemy 2.1, `Select.filter_by()` searches every FROM entity and raises `AmbiguousColumnError` on a shared name such as `id`, whereas `Query.filter_by()` still uses the last joined entity.

code

python · 53 lines
python
from datetime import datetime

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


class Base(DeclarativeBase):
    pass


class Customer(Base):
    __tablename__ = "customer"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str]
    tickets: Mapped[list["Ticket"]] = relationship(back_populates="customer")


class Ticket(Base):
    __tablename__ = "ticket"
    id: Mapped[int] = mapped_column(primary_key=True)
    subject: Mapped[str]
    status: Mapped[str]
    created_at: Mapped[datetime]
    customer_id: Mapped[int] = mapped_column(ForeignKey("customer.id"))
    customer: Mapped[Customer] = relationship(back_populates="tickets")


engine = create_engine("sqlite://")
Base.metadata.create_all(engine)
with Session(engine) as session, session.begin():
    acme, globex = Customer(name="Acme"), Customer(name="Globex")
    session.add_all(
        [
            Ticket(subject="Login fails", status="open", created_at=datetime(2026, 10, 1), customer=acme),
            Ticket(subject="Invoice wrong", status="closed", created_at=datetime(2026, 10, 3), customer=acme),
            Ticket(subject="Export slow", status="open", created_at=datetime(2026, 10, 2), customer=globex),
        ]
    )
from sqlalchemy import func

with Session(engine) as session:
    # legacy Query: deduplicates full-entity rows by primary key
    legacy = session.query(Customer).join(Customer.tickets).all()
    # 2.0 style: one Customer per joined row, duplicates kept
    modern = session.scalars(select(Customer).join(Customer.tickets)).all()
    print(len(legacy), len(modern), len(set(modern)))

    # legacy count() wraps the query in a subquery; the 2.0 form counts directly
    print(session.query(Ticket).filter(Ticket.status == "open").count())
    print(session.scalar(select(func.count()).select_from(Ticket).where(Ticket.status == "open")))

    # legacy filter_by() after join() targets the last joined entity
    print([t.id for t in session.query(Ticket).join(Ticket.customer).filter_by(id=1)])

go deeper

for a junior

Recall that session.query() is the legacy form and that 2.0 style builds a select() and runs it with scalars() or execute().

for a middle

Map each common Query call to its select() form and explain the four implicit behaviours that disappear: deduplication, LIMIT in first(), the count subquery, and the filter_by() target.

for a senior

Convert legacy queries without changing results: compare emitted SQL, assert on row counts, and replace filter_by() after joins with explicit where() conditions.

for a principal

Weigh which converted queries need regression tests first, favouring those that join collections, page results or count rows for users.

## Query is legacy, not removed For most of SQLAlchemy's history the ORM was queried through **`Query`**, made by `session.query(Ticket)`. Since 2.0 the standard is the **2.0 style**: build a `select()` statement and run it with `Session.execute()`, `Session.scalars()` or `Session.scalar()`. `Query` remains in SQLAlchemy 2.1 as a legacy API. It now translates itself into a `select()` when executed, and `Session.query()` itself emits no warning, though a few individual methods such as `Query.get()` emit `LegacyAPIWarning`. Converting is therefore a rewrite of call sites, and the risk sits in behaviours `Query` applied without being asked. ## The call-by-call mapping | Legacy `Query` | 2.0 style | |---|---| | `session.query(Ticket).all()` | `session.scalars(select(Ticket)).all()` | | `.filter(Ticket.status == "open")` | `.where(Ticket.status == "open")` (`filter()` also exists on `Select`) | | `.filter_by(status="open")` | `.filter_by(status="open")`, with the 2.1 rule below | | `.one()` / `.one_or_none()` | `session.execute(stmt).scalar_one()` / `.scalar_one_or_none()` | | `.first()` | `session.scalars(stmt.limit(1)).first()` | | `.count()` | `session.scalar(select(func.count()).select_from(Ticket).where(...))` | | `.join(Ticket.customer)` | `.join(Ticket.customer)` on the `select()` | | `.options(joinedload(...)).all()` | `session.scalars(stmt).unique().all()` | | `.from_statement(text(...))` | `select(Ticket).from_statement(text(...))` | | `.populate_existing()` | `.execution_options(populate_existing=True)` | `Select` gained `filter()`, `filter_by()`, `join()`, `outerjoin()` and `options()` precisely so that this mapping is mostly mechanical. ## Statement and result are now separate objects A `Query` was both the statement and the way to fetch it. Each iteration of the same `Query`, or each call to `.all()`, ran the SQL again, and `.all()` returned a plain Python `list`. In 2.0 style the two roles split: - the **`Select`** is an immutable statement: build it once, derive variants with `.where()` (each call returns a new statement), and execute it as often as you like; - the **`Result`** returned by `session.execute()` or `session.scalars()` is a single-pass iterator over one execution, so keep `.all()` in a variable rather than iterating the result twice. Multi-entity queries already returned `Row` objects from `Query`, so `session.query(Ticket, Customer.name)` converts to `session.execute(select(Ticket, Customer.name))` with the same row shape. Single-entity queries are the ones that need `scalars()`, or the converted code suddenly receives one-element rows instead of objects. ## Results that change on conversion The first three run without error but return different data; the fourth raises only when a name is shared, and the fifth always raises: 1. **Deduplication.** `Query` deduplicated full-entity results by primary key. `session.query(Customer).join(Customer.tickets).all()` returns each customer once; `session.scalars(select(Customer).join(Customer.tickets)).all()` returns a customer once per matching ticket. Add `.unique()` or restructure the query (for example with an EXISTS via `Customer.tickets.any()`). 2. **`first()` and LIMIT.** `Query.first()` applied a LIMIT of one in the SQL. `Result.first()` sends the statement as written, returns the first row and discards the rest, so add `.limit(1)` yourself on large tables. 3. **`count()` SQL.** `Query.count()` emitted `SELECT count(*) FROM (<the whole query>) AS anon_1`. The 2.0 form counts the rows directly. For the same FROM and WHERE the number matches, but neither equals `len(query.all())` when a join repeats parents, because `Query` deduplicated the objects and never the count. 4. **`filter_by()` target.** `Query.filter_by()` looks only at the primary entity or the last entity passed to `join()`. In SQLAlchemy 2.1, `Select.filter_by()` searches **every** FROM entity and raises `AmbiguousColumnError` when the name exists in more than one. So `session.query(Ticket).join(Ticket.customer).filter_by(id=1)` filters `customer.id`, while the converted `select()` raises; replace it with an explicit `where(Customer.id == 1)`. 5. **Joined eager loads of collections.** `Query` uniqued them silently; the 2.0 result raises `InvalidRequestError` until `.unique()` is called. ## Patterns that now fail - **String relationship names**: `session.query(Customer).join("tickets")` raises `ArgumentError`; use `Customer.tickets`. - **`Query.from_self()`** and **`Query.select_entity_from()`** were removed in 2.0: build `.subquery()` and wrap it with `aliased(Ticket, subq)`. - **Implicit ORDER BY columns with `distinct()`**: `Query` added ordering columns to the SELECT for you; with `select()` add them explicitly and use `Result.columns()` to drop them from rows. ## Converting safely - Convert one call site at a time and compare the **emitted SQL** with `echo=True`. - Assert on **row counts**, not only on the first object, so lost deduplication shows up. - Replace `filter_by()` after a join with explicit `where()` conditions naming the entity. - Grep for `.first()` on unbounded queries and add `.limit(1)` where the table is large. - Watch the single-row methods: legacy `Query.one()` on a single-entity query returned the entity, while `session.execute(stmt).one()` returns a one-element `Row`. Use `scalar_one()` (or `session.scalars(stmt).one()`) so callers still receive a `Ticket` and do not fail later with `AttributeError`. - Convert the tests that cover a call site in the same change, so a reviewer sees the old and new expectations side by side.

  • How do you convert a Query that used distinct() and ordered by a joined column?
    `Query` silently added the ORDER BY column to the SELECT. In 2.0 style add it yourself, `select(Customer, Ticket.created_at).join(Customer.tickets).distinct().order_by(Ticket.created_at)`, then call `session.execute(stmt).columns(Customer)` so each row carries only the customer. Without the added column, many databases, PostgreSQL among them, reject ORDER BY on a column missing from a DISTINCT select list.
  • Why not keep filter_by() after a join when converting to select()?
    Because the meaning moved. Legacy `Query.filter_by()` targets the last joined entity, so `filter_by(id=1)` after `join(Ticket.customer)` filtered `customer.id`. In 2.1 `Select.filter_by()` searches every FROM entity and raises `AmbiguousColumnError` for a name both sides have. An explicit `where(Customer.id == 1)` says which column you mean and behaves the same everywhere.

  • Result.first() adds LIMIT 1 to the SQL, exactly as Query.first() did.
  • session.scalars(select(Customer).join(Customer.tickets)) returns each customer once, like Query.
  • Query was removed in SQLAlchemy 2.0, so session.query() fails on 2.1.
  • Select.filter_by() after a join picks the same column as Query.filter_by() in 2.1.
  • session.query(Customer).join('tickets') still works in 2.1 as a shorthand.

Assumes SQLAlchemy 2.1: Query has been legacy since 2.0, and 2.1's Select.filter_by() searches every FROM entity while Query.filter_by() keeps the older rule.