Querying with select() in SQLAlchemy 2.x
Filtering, joining, aggregating and bulk-updating inventory data with select(), plus lazy loading, the N+1 problem and how eager loading options fix it.
In SQLAlchemy 2.x, Core and the ORM share one query language: build a statement with select(), then run it with session.scalars() or session.execute(). The old session.query(Item).filter(...) API is legacy; most blog posts still use it, so I translate them as I read rather than copying them.
All examples assume the Warehouse and Item models from the inventory example in the models note and an open session.
Two ways to run a select
from sqlalchemy import select
# ORM objects: scalars() unwraps the first column of each row
items = session.scalars(select(Item).where(Item.quantity < 10)).all()
# Rows of columns: execute() returns Row tuples with named access
rows = session.execute(select(Item.sku, Item.quantity)).all()
for row in rows:
print(row.sku, row.quantity)
Rule of thumb: scalars() when selecting one entity or one column, execute() when selecting several things. Without scalars(), select(Item) gives one-element rows like (Item,), which is a common source of confusion.
Fetching one result:
| Call | Zero rows | More than one row |
|---|---|---|
.one() |
raises NoResultFound |
raises MultipleResultsFound |
.one_or_none() |
returns None |
raises |
.first() |
returns None |
returns the first, silently |
session.get(Item, 42) fetches by primary key and checks the identity map first, so it may not query at all. session.scalar(stmt) is shorthand for the first column of the first row, which suits counts:
from sqlalchemy import func
total = session.scalar(select(func.count()).select_from(Item))
Filtering, ordering and paging
stmt = (
select(Item)
.where(Item.quantity < 10, Item.name.ilike("%bolt%")) # several conditions are ANDed
.order_by(Item.quantity, Item.sku)
.limit(20)
.offset(40)
)
low_stock_bolts = session.scalars(stmt).all()
Useful column operators: ==, !=, <, .in_([...]), .is_(None), .like(), .ilike(), .between(). Combine with or_(), and_() and not_() from sqlalchemy. Every value is sent as a bound parameter, so this is safe from injection.
Statements are immutable and composable, which makes optional filters easy:
def list_items(session: Session, warehouse_id: int | None = None, max_quantity: int | None = None):
stmt = select(Item).order_by(Item.sku)
if warehouse_id is not None:
stmt = stmt.where(Item.warehouse_id == warehouse_id)
if max_quantity is not None:
stmt = stmt.where(Item.quantity <= max_quantity)
return session.scalars(stmt).all()
Joins and aggregates
Joining along a relationship lets SQLAlchemy work out the ON clause from the foreign key:
stmt = (
select(Item)
.join(Item.warehouse)
.where(Warehouse.name == "London", Item.quantity < 2)
)
Stock value per warehouse, returning rows rather than objects:
stmt = (
select(
Warehouse.name,
func.count(Item.id).label("item_count"),
func.sum(Item.quantity * Item.price).label("stock_value"),
)
.join(Warehouse.items)
.group_by(Warehouse.id, Warehouse.name)
.order_by(func.sum(Item.quantity * Item.price).desc())
)
for name, item_count, stock_value in session.execute(stmt):
print(name, item_count, stock_value) # stock_value is a Decimal
.join() is an inner join, so warehouses with no items disappear; .outerjoin() keeps them (with a count of 0). For “warehouses that have an empty item”, .where(Warehouse.items.any(Item.quantity == 0)) renders an EXISTS subquery without duplicating rows.
Lazy loading and the N+1 problem
By default, relationships are lazy: warehouse.items is loaded with a separate query the first time I touch it. That is convenient and a performance trap:
for warehouse in session.scalars(select(Warehouse)):
print(warehouse.name, len(warehouse.items)) # one extra query per warehouse
With five warehouses I counted six queries: one for the warehouses, plus one per warehouse. With 500 warehouses it is 501. This is the N+1 problem, and echo=True on the engine makes it very visible.
Eager loading options
Loader options tell the query to fetch related objects up front:
from sqlalchemy.orm import joinedload, raiseload, selectinload
# Collections: one extra query with WHERE warehouse_id IN (...), 2 queries in total
stmt = select(Warehouse).options(selectinload(Warehouse.items))
warehouses = session.scalars(stmt).all()
# Many-to-one: a LEFT OUTER JOIN in the same query, 1 query in total
stmt = select(Item).options(joinedload(Item.warehouse))
items = session.scalars(stmt).all()
selectinloadis the documented best default for collections: the main query is untouched and the related rows come in one additionalSELECT ... WHERE ... IN.joinedloadsuits many-to-one relationships, where the join adds columns but not rows.joinedloadon a collection multiplies parent rows (one per child), so SQLAlchemy requires.unique()on the result:session.scalars(stmt).unique().all(). Forgetting it raisesInvalidRequestError.
To make accidental lazy loads impossible, use raiseload: per query with .options(raiseload("*")), or for a relationship with relationship(..., lazy="raise"). Any unplanned access then raises immediately instead of quietly adding queries. In an API, where objects are serialised after the query, I would rather find out in a test.
Bulk UPDATE, DELETE and upserts
Changing objects one at a time loads every row into Python. For set-based changes, run the statement directly:
from sqlalchemy import delete, update
session.execute(update(Item).where(Item.quantity == 0).values(quantity=1))
session.execute(delete(Item).where(Item.sku.like("DISCONTINUED-%")))
Arithmetic on the column (values(quantity=Item.quantity + 10)) runs inside Postgres, which is also how to avoid lost updates.
PostgreSQL’s INSERT ... ON CONFLICT comes from the dialect’s own insert:
from sqlalchemy.dialects.postgresql import insert
stmt = insert(Item).values(
warehouse_id=1, sku="BOLT-M6", name="M6 bolt", quantity=10, price=Decimal("0.12")
)
stmt = stmt.on_conflict_do_update(
index_elements=[Item.sku],
set_={"quantity": Item.quantity + stmt.excluded.quantity},
).returning(Item.quantity)
new_quantity = session.execute(stmt).scalar_one()
Gotchas
session.execute(select(Item))returns rows, not items. Usescalars()for entities..first()hides bugs. If exactly one row is expected,.one()or.one_or_none()will tell me when that assumption breaks.limit()withoutorder_by()returns arbitrary rows. Postgres guarantees no order withoutORDER BY, so pagination must be ordered by something unique.- Lazy loading after the session closes fails with
DetachedInstanceError. Eager-load what the response needs before returning. - Bulk
update()anddelete()bypass ORM cascades. A bulk delete of warehouses does not applydelete-orphanto their items in Python; only database-level rules (ON DELETE CASCADE,CHECK) still apply.
Check yourself
- When do I use
session.scalars()and whensession.execute()? - Why does listing warehouses and their items produce N+1 queries by default, and what is the usual fix?
- Why does
joinedloadon a collection require.unique(), and why doesselectinloadnot? - What does
raiseloaddo, and why is it useful in an API codebase? - How would I rewrite
session.query(Item).filter_by(sku="BOLT-M6").first()in 2.x style, and would I keep.first()?