skip to content

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.