skip to content

Entity Selects & Results

Session.execute and Session.scalars run select() over mapped classes and return entities or rows. Interviewers ask when a result needs unique() and how a legacy Query maps to 2.0 style.

on this pageshow

explore

questions

6

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%

answer

  1. rows versus objects
  2. one-element Row per entity
  3. a shortcut for Result.scalars()
  4. only the first column survives

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.

solid answer

~40 s

Running an ORM `select()` through `Session.execute()` gives a `Result` of `Row` objects, even when only one entity was selected: each row is a one-element, tuple-like `Row`, so the ticket sits at `row[0]` or `row.Ticket`. `Session.scalars(stmt)` is the shortcut for `session.execute(stmt).scalars()`. It returns a `ScalarResult` that yields the first column of each row, which for `select(Ticket)` is the `Ticket` object. So the open-tickets list uses `session.scalars(stmt).all()`, and a lookup by id uses `session.execute(stmt).scalar_one()`. Use `execute()` when each row carries more than one value, such as `select(Ticket, Customer.name)`, because `scalars()` there quietly keeps only the first column. Run ORM statements through the `Session`: a `Connection` returns plain column values, not objects.

code

python · 50 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),
        ]
    )

with Session(engine) as session:
    stmt = select(Ticket).where(Ticket.status == "open").order_by(Ticket.id)

    rows = session.execute(stmt).all()  # Row objects, one element each
    print(type(rows[0]).__name__, rows[0].Ticket.subject)

    tickets = session.scalars(stmt).all()  # the Ticket objects themselves
    print(type(tickets[0]).__name__, [t.subject for t in tickets])

    ticket = session.execute(select(Ticket).where(Ticket.id == 3)).scalar_one()
    print(ticket.subject)

go deeper

for a junior

Recall that execute() gives rows and scalars() gives objects, and that selecting one entity still produces one-element rows under execute().

for a middle

Explain that scalars() is execute() followed by Result.scalars(), that it keeps the first column of each row, and when scalar_one() fits better than a list.

for a senior

Catch the silent data loss of scalars() on a multi-column select in review, and keep ORM statements on the Session so objects, not bare columns, come back.

for a principal

Set a house convention: scalars() for single-entity reads, execute() with unpacking for mixed rows, so result shapes stay predictable for reviewers and type checkers.

## What the Session hands back from an ORM select In SQLAlchemy's 2.0-style ORM, a query is a `select()` construct built over mapped classes, and the `Session` executes it. The support dashboard in this example has two mapped classes: `Ticket` (id, subject, status, created_at, customer_id) and `Customer`, joined by a `Ticket.customer` relationship. Listing the open tickets starts with: ```python stmt = select(Ticket).where(Ticket.status == "open").order_by(Ticket.id) ``` The same statement can be run two ways, and they return different shapes: - `session.execute(stmt)` returns a **`Result`**, an iterator of **`Row`** objects. - `session.scalars(stmt)` returns a **`ScalarResult`**, an iterator of single values. Both send exactly the same SQL to the database; the difference is only in how the result object hands the data back. ## Session.execute(): rows, even for one entity A `Row` is SQLAlchemy's tuple-like result row. When you select an entity, the ORM turns the entity's columns into one object and puts that object into the row as a single element. So `select(Ticket)` run through `execute()` yields rows like `(Ticket(...),)`, not tickets: - `row[0]` and `row.Ticket` both give the `Ticket` object; an entity is keyed by its class name. - `row.subject` does **not** work: the `Row` has no `subject` attribute and raises `AttributeError`. - `session.execute(stmt).all()` is therefore a list of one-element rows. This shape exists because `execute()` is general: the same `Result` type carries rows of several entities and columns, such as `select(Ticket, Customer.name)`. ## Session.scalars(): the objects themselves When a statement selects one entity, the row wrapper is noise. `Session.scalars(stmt)` is documented as equivalent to calling `session.execute(stmt)` and then `Result.scalars()`. The `ScalarResult` it returns yields the **first column** of each row, which for `select(Ticket)` is the `Ticket` object: - `session.scalars(stmt).all()` gives a list of `Ticket` objects. - `session.scalars(stmt).first()` gives the first `Ticket` or `None`. - `session.execute(stmt).scalar_one()` gives exactly one `Ticket` and raises `NoResultFound` or `MultipleResultsFound` otherwise; `scalar_one_or_none()` returns `None` for no row. - `session.scalar(stmt)` returns the first column of the first row, or `None`. ## Choosing a call | You need | Call | Each item is | |---|---|---| | a list of one entity | `session.scalars(stmt).all()` | a `Ticket` | | exactly one entity by key | `session.execute(stmt).scalar_one()` | a `Ticket` | | an entity plus columns | `session.execute(stmt).all()` | a `Row` such as `(Ticket, str)` | | several entities per row | `session.execute(stmt)` and unpack | a `Row` such as `(Ticket, Customer)` | A useful rule of thumb: if the `select()` names one entity, use `scalars()`; if it names more than one thing, use `execute()` and unpack each row (`for ticket, name in result:`). ## The silent mistake: scalars() on a multi-column select `scalars()` does not check how many columns a row has. On `select(Ticket, Customer.name)` it returns only the `Ticket` objects and drops the names, with no error and no warning. Code that later expects the names finds them missing. When you really want another column, `Result.scalars()` takes an index or a name, as in `session.execute(stmt).scalars(1)`. ## A result is read once Both `Result` and `ScalarResult` are **single-pass iterators** over the rows the database sent back, not lists: - `for ticket in session.scalars(stmt):` consumes the result as it goes, which is fine for one loop. - `.all()` drains whatever is left into a list. Calling `.all()` a second time on the same result object returns an empty list, with no error. - Keep the list (`tickets = session.scalars(stmt).all()`) when a template or a function needs to walk the tickets more than once, and pass the list around rather than the result object. The objects themselves outlive the result: the `Ticket` instances stay attached to the `Session` that loaded them, and a second execution of the same statement in that `Session` hands back the same objects for the same primary keys. ## A Session, not a Connection The `select(Ticket)` construct can also be executed on a `Connection` from `engine.connect()`, and it runs. But a `Connection` returns column-level rows such as `(1, 'Login fails', 'open', ...)`; building `Ticket` instances, tracking them in the identity map and applying loader options are things the `Session` does. Keep ORM statements on the `Session` whenever you want objects back. In short: 1. `execute()` always returns rows; a single entity becomes a one-element row. 2. `scalars()` unwraps the first column of each row, which is the entity for a single-entity select. 3. Pick the call from the statement's shape, and never use `scalars()` to read a row that carries more than one value.

  • What does session.scalars() do with select(Ticket, Customer.name)?
    It keeps only the first column of each row, so you get `Ticket` objects and the customer names are dropped with no error or warning. To read both, use `session.execute(stmt)` and unpack each row; to pick a different single column, pass its index or name to `Result.scalars()`, as in `session.execute(stmt).scalars(1)`.
  • Does session.scalars() send different SQL from session.execute()?
    No. Both execute the same compiled `SELECT`; with echo on, the second run is even logged as cached. `scalars()` changes only how the returned `Result` hands rows back, by taking the first column of each, so choosing between them has no cost in the database.

execute() hands you each ticket sealed in its own envelope, ready to hold other papers beside it; scalars() opens every envelope and hands you only the first sheet inside.

  • session.execute(select(Ticket)).all() returns a plain list of Ticket objects.
  • A Row from select(Ticket) proxies the ticket, so row.subject works.
  • session.scalars() runs a cheaper SQL statement than session.execute().
  • scalars() on a two-column select raises an error instead of dropping a column.
  • Running select(Ticket) on engine.connect() returns Ticket objects just like a Session.
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

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 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 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