Querying — 2.0 select() style

SQLAlchemy 2.0 unified Core and ORM queries around select(). The 1.x session.query() API still works but is “legacy.” New code should use the modern style.

The basic pattern

from sqlalchemy import select

# All users
stmt = select(User)
users = session.scalars(stmt).all()

# Filter
stmt = select(User).where(User.is_active == True)
users = session.scalars(stmt).all()

# One row or None
stmt = select(User).where(User.email == "alice@example.com")
user = session.scalars(stmt).one_or_none()

# Exactly one (raises if 0 or 2+)
user = session.scalars(stmt).one()

# Just the first row (no error if multiple)
user = session.scalars(stmt).first()

execute() vs scalars()

This trips people up.

stmt = select(User)

# execute() returns Row objects:
result = session.execute(stmt)
for row in result:
    user = row[0]     # or row.User
    print(user.name)

# scalars() unwraps each Row to its first column (or the only entity):
for user in session.scalars(stmt):
    print(user.name)

For “select model instances,” scalars() is what you want. For “select columns / multiple entities” use execute():

stmt = select(User.id, User.name)            # selecting tuples, not the model
for row in session.execute(stmt):
    print(row.id, row.name)

Result terminators

After execute() or scalars():

WHERE — comparison operators

select(User).where(User.id == 42)
select(User).where(User.name != "alice")
select(User).where(User.age > 18)
select(User).where(User.email.like("%@example.com"))
select(User).where(User.email.ilike("%@EXAMPLE.com"))         # case-insensitive
select(User).where(User.name.in_(["alice", "bob", "carol"]))
select(User).where(User.name.not_in(["spam", "test"]))
select(User).where(User.deleted_at.is_(None))                  # IS NULL
select(User).where(User.deleted_at.is_not(None))
select(User).where(User.bio.contains("python"))                # LIKE %python%
select(User).where(User.tags.any(Tag.name == "vip"))           # for collection relationship
select(User).where(User.posts.any())                           # has at least one post

Multiple where() calls AND together:

select(User).where(User.is_active == True).where(User.age >= 18)

For explicit AND/OR/NOT:

from sqlalchemy import and_, or_, not_

select(User).where(
    and_(
        User.is_active == True,
        or_(User.age >= 18, User.has_consent == True),
    )
)

The &, |, ~ operators also work on column expressions (but parentheses matter due to operator precedence):

select(User).where((User.age >= 18) | (User.has_consent == True))

ORDER BY, LIMIT, OFFSET

select(User).order_by(User.created_at.desc())
select(User).order_by(User.created_at.desc(), User.name)       # multiple
select(User).limit(10).offset(20)                              # pagination

For keyset / cursor pagination (much better at scale than LIMIT/OFFSET):

select(User).where(User.id > last_seen_id).order_by(User.id).limit(50)

JOINs

# Implicit join via relationship — SQLAlchemy infers the ON clause
stmt = select(Post).join(Post.author).where(User.name == "alice")

# Explicit
stmt = select(Post).join(User, Post.author_id == User.id)

# LEFT OUTER JOIN
stmt = select(User).outerjoin(Post, User.id == Post.author_id)

Selecting from multiple tables:

stmt = select(User.name, Post.title).join(Post, Post.author_id == User.id)
for name, title in session.execute(stmt):
    print(f"{name}: {title}")

Aggregates and GROUP BY

from sqlalchemy import func

stmt = (
    select(User.id, func.count(Post.id).label("post_count"))
    .join(Post, Post.author_id == User.id)
    .group_by(User.id)
    .having(func.count(Post.id) > 5)
)

for user_id, count in session.execute(stmt):
    print(user_id, count)

func.<anything> is the SQL function namespace (works for any DB function: func.now(), func.coalesce(...), func.json_build_object(...)).

Subqueries

from sqlalchemy import select

# Subquery in WHERE
active_user_ids = select(User.id).where(User.is_active == True).scalar_subquery()
stmt = select(Post).where(Post.author_id.in_(active_user_ids))

# Subquery as a derived table
post_counts = (
    select(Post.author_id, func.count(Post.id).label("n"))
    .group_by(Post.author_id)
    .subquery()
)
stmt = (
    select(User.name, post_counts.c.n)
    .join(post_counts, post_counts.c.author_id == User.id)
)

.scalar_subquery() for “a subquery that returns one value/column” (for IN, scalar comparisons). .subquery() for “a subquery used as a derived table in FROM.”

CTEs:

cte = (
    select(Post.author_id, func.count(Post.id).label("n"))
    .group_by(Post.author_id)
    .cte("post_counts")
)
stmt = select(User.name, cte.c.n).join(cte, cte.c.author_id == User.id)

EXISTS

from sqlalchemy import exists

# Users with at least one post
stmt = select(User).where(
    exists().where(Post.author_id == User.id)
)

# Easier idiom for relationships:
stmt = select(User).where(User.posts.any())
stmt = select(User).where(User.posts.any(Post.is_published == True))

UPDATE and DELETE

2.0 style for bulk updates:

from sqlalchemy import update, delete

session.execute(
    update(User)
    .where(User.email == "alice@old.com")
    .values(email="alice@new.com")
)

session.execute(
    delete(User).where(User.deleted_at < cutoff)
)

session.commit()

These bypass the unit of work — they don’t load objects, don’t trigger cascades, don’t run Python-side hooks. For “delete 100k stale rows,” this is what you want. For “delete one user and their cascaded posts via Python,” use session.delete(user).

RETURNING (Postgres / SQLite)

from sqlalchemy import insert

stmt = insert(User).values(name="alice", email="alice@example.com").returning(User.id, User.created_at)
result = session.execute(stmt)
new_id, created_at = result.one()

Useful for getting DB-computed columns (auto-IDs, server defaults) without a second SELECT.

Composing queries — build incrementally

stmt = select(User)
if name_filter:
    stmt = stmt.where(User.name.ilike(f"%{name_filter}%"))
if active_only:
    stmt = stmt.where(User.is_active == True)
stmt = stmt.order_by(User.id).limit(50)

users = session.scalars(stmt).all()

Statements are immutable; each method returns a new statement. This makes them composable and safe to pass around.

1.x style — what you’ll see in older code

# Equivalent of select(User).where(User.is_active == True):
users = session.query(User).filter(User.is_active == True).all()

# Equivalent of session.get(User, 42):
user = session.query(User).get(42)

Both styles work; they hit the same code paths underneath. Don’t mix them randomly within one codebase — pick one (2.0 for new code).

Common pitfalls

• Forgetting scalars() when selecting a model — session.execute(select(User)) returns Row objects, not User instances. Iterating gives you tuples.
• session.execute(select(User)).all() returns list of Row, not list of User. Use scalars().all().
• .unique() not called when join produces duplicates due to one-to-many JOIN — same parent row appears N times. SQLAlchemy raises MultipleResultsFound in some cases; otherwise you get duplicates.
• .first() returns None not error when no rows match. Use .one() if you expect exactly one (and want the exception).
• .like("%foo%") is case-sensitive in Postgres. Use .ilike("%foo%") for case-insensitive, or func.lower(column) == "foo".

Common interview confusions

• “session.query() is gone in 2.0.” — still works, called “legacy.” 2.0 just discourages new use.
• “select(User).all() works.” — select() returns a Statement, not a Result. You need session.scalars(stmt).all().
• “.where() replaces previous WHERE.” — accumulates. Each .where() ANDs another clause.

Interview angle

• “What’s the difference between session.execute() and session.scalars()?” — execute() returns Row objects (tuple-like); scalars() unwraps each row to its first column (typically the model instance). For “give me User objects,” use scalars().
• “How do you build a query that conditionally adds filters?” — start with stmt = select(User), then if cond: stmt = stmt.where(...). Statements are immutable; each method returns a new one.
• “Difference between .one(), .first(), .one_or_none(), .all()?” — one() requires exactly 1 (else raises), first() returns 1 or None, one_or_none() allows 0 or 1 (raises on 2+), all() materializes all.
• “How do you JOIN to a related table?” — select(Post).join(Post.author) (uses the relationship to infer the ON clause), or explicit join(User, Post.author_id == User.id).
• “update()/delete() constructs vs session.delete()?” — bulk Core constructs bypass the unit of work — no cascades, no Python hooks, much faster for many rows. session.delete(instance) is the per-object path that runs cascades.
• “How do you use RETURNING with SQLAlchemy?” — insert(...).returning(Model.col1, Model.col2) then session.execute(stmt).one(). Returns server-computed columns without a second SELECT.