SQLAlchemy Result-Fetching Methods¶

Overview¶

In SQLAlchemy 2.x, session.execute() always returns a CursorResult of Row namedtuples — even when you query a single ORM model. The fetch methods below control how you pull data out of that result.

session.execute(select(User))
    └── CursorResult
            ├── .all()            → list[Row]
            ├── .scalars()        → ScalarResult
            │       ├── .all()    → list[Model]
            │       ├── .first()  → Model | None
            │       ├── .one()    → Model  (or raises)
            │       └── .one_or_none() → Model | None
            ├── .scalar()         → Any | None
            └── .first()          → Row | None

Methods¶

all()¶

Returns every matching row as a list. An empty query returns [] — never raises.

from sqlalchemy import select

users = session.execute(
    select(User).where(User.active == True)
).scalars().all()

# Legacy query API (still valid in 2.x)
users = session.query(User).all()

Pros

• Predictable — always a list, even when empty
• Easy to iterate, slice, and measure with len()

Cons

• Silent performance killer on unbounded queries
• No protection against accidentally fetching millions of rows

first()¶

Returns the first row, or None if no rows match. Applies LIMIT 1 automatically at the DB level.

user = session.execute(
    select(User).order_by(User.created_at)
).scalars().first()

if user:
    print(user.name)

Pros

• Never raises on an empty result
• Efficient — database stops at row 1

Cons

• Hides duplicates silently
• No guarantee of uniqueness

one()¶

Expects exactly one row. Raises if zero rows are found (NoResultFound) or if more than one row matches (MultipleResultsFound). Use when your query has a strict contract.

from sqlalchemy.exc import NoResultFound, MultipleResultsFound

try:
    user = session.execute(
        select(User).where(User.id == uid)
    ).scalars().one()
except NoResultFound:
    raise HTTPException(status_code=404, detail="User not found")
except MultipleResultsFound:
    raise HTTPException(status_code=500, detail="Data integrity error")

Pros

• Enforces data integrity at the query level
• Makes your assumptions explicit and auditable

Cons

• Must always be wrapped in try/except
• Both exception types require different handling

one_or_none()¶

Returns one row or None. Raises MultipleResultsFound if more than one row matches. The sweet spot for "find by unique key" lookups.

user = session.execute(
    select(User).where(User.email == email)
).scalars().one_or_none()

if user is None:
    return register_new_user(email)

# Here: guaranteed exactly one result
send_welcome_email(user)

Pros

• Safe None check with no boilerplate
• Raises on dirty data — surfaces bugs early

Cons

• Still raises if duplicates exist (intentional, but must be handled)
• Slightly less efficient than first() — fetches 2 rows internally

scalar()¶

Returns the first column of the first row, or None. Raises MultipleResultsFound on 2+ rows. Designed for aggregate queries and single-column projections.

# Aggregate
count = session.execute(
    select(func.count()).select_from(User)
).scalar()
# → 42  (int, not a Row tuple)

# Single-column projection
name = session.execute(
    select(User.name).where(User.id == 1)
).scalar()
# → "Alice"

Pros

• Clean, no tuple unpacking for aggregate results
• Works perfectly with COUNT, MAX, MIN, SUM

Cons

• Raises on multiple rows — wrap in try/except if that's possible
• Easy to accidentally mix up with scalars()

scalars()¶

Returns a ScalarResult — an iterable that strips the outer Row wrapper so each element is the ORM model directly. Chain .all(), .first(), .one(), or .one_or_none() onto it.

result = session.execute(select(User))

# Without scalars() → list of Row namedtuples
result.all()           # [(User,), (User,), ...]

# With scalars() → list of User ORM objects
result.scalars().all() # [<User id=1>, <User id=2>, ...]

# Lazy iteration (no full materialisation)
for user in result.scalars():
    process(user)

Pros

• Required glue for clean ORM usage in 2.x
• Supports lazy streaming when not followed by .all()

Cons

• Extra method call compared to the legacy query API
• Easy to forget, leading to (User,) tuples instead of User objects

Quick Decision Guide¶

Exception Reference¶

Legacy vs Modern API¶

SQLAlchemy 2.x supports both the legacy query() API and the modern execute() + select() style. The legacy API still works but is not recommended for new code.

from sqlalchemy import select

# All rows
session.execute(select(User)).scalars().all()

# First row
session.execute(select(User)).scalars().first()

# Exactly one
session.execute(select(User).where(User.id == 1)).scalars().one()

# One or None
session.execute(select(User).where(User.email == e)).scalars().one_or_none()

# Aggregate
session.execute(select(func.count(User.id))).scalar()

# All rows
session.query(User).all()

# First row
session.query(User).first()

# Exactly one
session.query(User).filter(User.id == 1).one()

# One or None
session.query(User).filter(User.email == e).one_or_none()

# Aggregate
session.query(func.count(User.id)).scalar()

Common Patterns & Gotchas¶

Primary key lookup: use session.get() first¶

session.get() checks the identity map (in-session cache) before hitting the database, making repeated lookups within the same unit of work essentially free.

# Preferred for PK lookups
user = session.get(User, user_id)  # may not hit DB at all

# Equivalent but always issues SQL
user = session.execute(
    select(User).where(User.id == user_id)
).scalars().one_or_none()

Streaming large results¶

# Load in batches of 500 — avoids full materialisation
for user in session.execute(select(User)).scalars().yield_per(500):
    process(user)

Eager loading relationships¶

All fetch methods return the mapped model without its relationships pre-loaded by default. Accessing a relationship attribute after session close raises DetachedInstanceError.

# Bad — triggers N+1 and breaks after session closes
users = session.execute(select(User)).scalars().all()
for u in users:
    print(u.posts)  # lazy SQL per user, DetachedInstanceError risk

# Good — load relationships in one query
from sqlalchemy.orm import selectinload

users = session.execute(
    select(User).options(selectinload(User.posts))
).scalars().all()

Multi-column results — don't use scalars()¶

When projecting multiple columns, keep the Row namedtuple intact so you can access columns by name.

rows = session.execute(
    select(User.id, User.name, User.email)
).all()

for row in rows:
    print(row.id, row.name, row.email)  # named access