In SQLAlchemy Core, how do you build a select() step by step with where(), group_by(), order_by() and limit(), and why reassign each call?
answer
- generative API pattern
- what each method returns
- several where() calls combine how
- & | ~ versus and, or, not
- in_() rather than Python in
basics
~10 sStart from select(columns) and chain where(), join(), group_by(), having(), order_by() and limit(). Each call returns a new Select and leaves the original untouched, so a call whose result is not reassigned is silently lost.
solid answer
~40 s`select()` is **generative**: `where()`, `join()`, `group_by()`, `having()`, `order_by()`, `limit()` and `offset()` each return a *new* `Select`, so an optional filter is added with `stmt = stmt.where(...)`; a bare `stmt.where(...)` builds a statement nobody keeps. Repeated `where()` calls, or several arguments to one call, are combined with AND, and `and_()`, `or_()` or the `&`, `|`, `~` operators build compound criteria. Python's own `and`, `or` and `in` must never touch a SQL expression: `c.id > 1 and ...` raises `TypeError: Boolean value of this clause is not defined`, while `==` comparisons quietly keep one operand or collapse to `WHERE false`. `order_by()` and `group_by()` append columns, `order_by(None)` clears them, and a second `limit()` replaces the first. `print(stmt)` shows the result with `:status_1`-style placeholders for the values.
code
python · 44 linesfrom sqlalchemy import (Column, ForeignKey, Integer, MetaData, Numeric, String,
Table, desc, func, or_, select)
metadata = MetaData()
customers = Table(
"customers", metadata,
Column("id", Integer, primary_key=True),
Column("name", String(100), nullable=False),
Column("region", String(20)),
)
orders = Table(
"orders", metadata,
Column("id", Integer, primary_key=True),
Column("customer_id", ForeignKey("customers.id"), nullable=False),
Column("status", String(20), nullable=False),
)
order_lines = Table(
"order_lines", metadata,
Column("id", Integer, primary_key=True),
Column("order_id", ForeignKey("orders.id"), nullable=False),
Column("quantity", Integer, nullable=False),
Column("unit_price", Numeric(10, 2), nullable=False),
)
revenue = func.sum(order_lines.c.quantity * order_lines.c.unit_price).label("revenue")
stmt = (
select(customers.c.name, revenue)
.join_from(customers, orders)
.join(order_lines)
.where(orders.c.status == "PAID")
)
stmt.where(customers.c.region == "EU") # discarded: returns a new Select
region = "EU"
if region:
stmt = stmt.where(or_(customers.c.region == region, customers.c.region.is_(None)))
stmt = (
stmt.group_by(customers.c.id, customers.c.name)
.having(func.count(orders.c.id.distinct()) >= 3)
.order_by(desc("revenue"))
.limit(10)
)
print(stmt)go deeper
Recall that select() is generative: every where(), join() or order_by() returns a new Select you must reassign. Know and_(), or_() and in_() by name.
Explain which methods accumulate (where, having, order_by, group_by) and which replace (limit, offset), and why Python's and, or and in misbehave on SQL expressions.
Review composed query builders for discarded where() results and for and, or or in applied to expressions; both pass silently and return wrong rows instead of raising.
Standardise how the team composes optional filters, such as a criteria list passed to where(*criteria), so builders stay reviewable as filters multiply.
## What `select()` builds `select()` returns a `Select` object: a Python tree describing one SELECT statement, which becomes SQL text only when it is compiled for a dialect. You pass it the columns you want, such as `customers.c.name`, an expression like `func.sum(...).label("revenue")`, or a whole `Table`, and SQLAlchemy derives the FROM clause from the tables those columns belong to. Every further clause is added by a method call on that object. ## Each method returns a new `Select` The construct is **generative**: `where()`, `join()`, `join_from()`, `outerjoin()`, `group_by()`, `having()`, `order_by()`, `limit()`, `offset()` and `distinct()` copy the statement, apply the change to the copy and return it. The original is never modified. Two consequences follow: - **A call whose result you drop is lost.** `stmt.where(customers.c.region == "EU")` on a line of its own builds a filtered statement and throws it away. The next `print(stmt)` shows no WHERE clause, and nothing warns you. - **A base statement can be shared safely.** Keep `base = select(...)` with the joins every report needs, then derive `base.where(...)` for one report and `base.order_by(...)` for another; neither leaks into the other or into `base`. Conditional composition therefore always reassigns: 1. Start with `stmt = select(...)` plus the joins every variant needs. 2. For each optional filter, write `stmt = stmt.where(...)` inside its `if`. 3. Add grouping, ordering and paging last, then execute `stmt`. ## Combining criteria Repeated `where()` calls, and several arguments passed to one call, are joined with **AND**. Everything else uses SQLAlchemy's own constructs: - `and_()`, `or_()` and `not_()`, or the overloaded operators `&`, `|` and `~`. Python binds `&` and `|` tighter than `==`, so parenthesise each comparison: `(c.id > 1) & (c.region == "EU")`. - Column operators: `col.in_([...])`, `col.is_(None)` (`col == None` also renders `IS NULL`), `col.like()`, `col.ilike()`, `col.between()`. - **Never Python's `and`, `or`, `not` or `in`.** They call `bool()` on a SQL expression. For an ordering comparison such as `c.id > 1`, SQLAlchemy refuses with `TypeError: Boolean value of this clause is not defined`. An `==` comparison does have a Python truth value, so `(c.region == "EU") and (c.id > 1)` silently keeps only the first criterion, and `c.region in ("EU", "UK")` becomes the constant `WHERE false`. No error or warning tells you. When filters arrive as a list, `stmt.where(*criteria)` adds nothing for an empty list. Calling `and_()` with no arguments is deprecated and emits a deprecation warning; write `and_(true(), *criteria)` if you need a single expression. ## Accumulate or replace | Method | A second call | How to undo | |---|---|---| | `where()`, `having()` | ANDed with the first | keep the earlier statement | | `join()`, `outerjoin()` | adds another join | keep the earlier statement | | `group_by()`, `order_by()` | appends columns | `group_by(None)`, `order_by(None)` | | `limit()`, `offset()` | replaces the value | `limit(None)`, `offset(None)` | `order_by()` also accepts a label name as a string, so `.order_by(desc("revenue"))` sorts by the `revenue` label defined in the columns clause, and `group_by()` with `having()` takes aggregate expressions built from `func`, such as `func.count(orders.c.id.distinct()) >= 3`. ## A worked report For a sales report over `customers`, `orders` and `order_lines`, the composition reads top to bottom: - `select(customers.c.name, revenue)` names the output, where `revenue` is a labelled `func.sum()` over quantity times unit price. - `join_from(customers, orders).join(order_lines)` lays out the FROM clause; each ON clause is inferred from a foreign key. - `where(orders.c.status == "PAID")` filters rows, and an optional region filter is reassigned only when a region was requested. - `group_by(customers.c.id, customers.c.name)`, `having(...)`, `order_by(desc("revenue"))` and `limit(10)` shape the result. ## Review checklist for query builders 1. Every `where()`, `join()` or `order_by()` inside a branch is assigned back to the variable it extends. 2. No `and`, `or`, `not` or `in` keyword sits between column expressions; search builder code for them. 3. Every comparison combined with `&` or `|` is wrapped in parentheses. 4. Each label named as a string in `order_by()` exists in the columns clause. An unknown one fails at compile time with `CompileError: Can't resolve label reference for ORDER BY / GROUP BY / DISTINCT etc.` ## Reading what you built `print(stmt)` compiles with a generic dialect and shows each Python value as a named placeholder such as `:status_1`; the values travel separately as bound parameters. That string is the quickest check that every reassignment took effect and that the WHERE clause holds each criterion you meant, in the grouping you meant. It is not exactly what a given database receives: compiling against that database's dialect shows its placeholder style and its spelling of operators such as `ilike()`.
- How do you build a WHERE clause from a list of optional filters without an empty and_()?Collect the criteria in a list and call `stmt.where(*criteria)`; an empty list adds no WHERE clause at all. If you need one expression, for example to pass to `or_()`, write `and_(true(), *criteria)`. Calling `and_()` with no arguments emits a deprecation warning saying it will be disallowed in a future release.
- Why does `where((c.region == "EU") and (c.id > 1))` filter only on region, with no error?Python's `and` calls `bool()` on the first operand. An `==` comparison between a column and a value is falsy as a Python bool, so `and` returns that first expression unchanged and the second criterion never reaches SQLAlchemy. Pass both to `where()`, use `and_()`, or use `&` with parentheses.
- How do you remove an ordering that a shared base statement already carries?Call `order_by(None)` on the derived statement; it clears every ORDER BY column, and the next `order_by()` starts fresh. `group_by(None)` does the same for grouping. WHERE criteria have no such reset, so keep the unfiltered statement in a variable and derive from it.
Like Python's str.replace(): calling it without keeping the result changes nothing, because the string is immutable and every call hands back a new one. A Select behaves the same way under where(), join() and order_by().
- stmt.where(...) changes stmt in place, so reassigning is optional
- Python's and and or combine SQLAlchemy conditions like and_() and or_()
- c.region in ("EU", "UK") renders a SQL IN list
- A second where() call replaces the first one
- Calling order_by() twice keeps only the last ordering