Querying with select() and scalars()

This is the 2.0-style query API that replaced session.query(), and every read in an ORM app goes through it.

In 2.x the same select() construct serves Core and ORM. select(User) selects entire User objects; select(User.name, User.id) selects individual columns. Model attributes like User.name act like table.c.name, so select(User).where(User.name == "ada") reads naturally.

Run it through the session:

session.execute(stmt) returns a Result of Rows. For select(User) each row is a one-element tuple holding a User.
session.scalars(stmt) unwraps the first column, so you iterate over User objects directly. This is what you want for single-entity queries.
session.scalar(stmt) returns the first value or None.

The result offers methods that state your expectation:

.all() gives a list.
.first() gives the first item or None (add .limit(1) to the statement so the database doesn't do extra work).
.one() requires exactly one row and raises NoResultFound or MultipleResultsFound otherwise.
.one_or_none() allows zero.

Prefer one() when a missing row is a bug: it fails loudly instead of returning None.

The legacy session.query(User).filter(...) still works but is considered legacy; new code should use select(). Statements are immutable and generative, so stmt.where(...) returns a new statement, which makes it easy to build queries step by step.

Example

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

class Base(DeclarativeBase):
    pass

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

engine = create_engine("sqlite://")
Base.metadata.create_all(engine)
with Session(engine) as s:
    s.add_all([User(name="ada"), User(name="bob"), User(name="cy")])
    s.commit()

    users = s.scalars(select(User).order_by(User.name)).all()
    print([u.name for u in users])                     # ['ada', 'bob', 'cy']
    print(s.execute(select(User)).first())             # (<__main__.User ...>,)
    print(s.execute(select(User.id, User.name)).all()[0])  # (1, 'ada')
    bob = s.scalars(select(User).where(User.name == "bob")).one()
    print(bob.id)                                      # 2

Remember

select(User) for objects, select(User.a, User.b) for columns
session.scalars() for entity queries; execute() returns Rows
.one() / .one_or_none() / .first() / .all() state how many rows you expect
Statements are immutable: .where() returns a new one

Watch out for

Using session.execute(select(User)) and getting 1-tuples instead of User objects.

Check yourself

Which call returns a list of User objects for stmt = select(User)?

Official docs: docs.sqlalchemy.org/en/20/orm/queryguide/select.html

Example

Remember

Watch out for

Check yourself

Builds on

Unlocks