Skip to content
Tutorials Center
My learning

Topics

Lesson 13 of 37 ยท Core and Expressions

Building SELECT Queries with where()

Build SELECT statements in SQLAlchemy Core and ORM style with select(), where(), and_/or_, in_, like, between, NULL checks, ordering, limit, offset and labels.

select() is the most important construct in SQLAlchemy 2.0. It builds a SELECT statement as a Python object, one method call at a time, and the very same object works for Core tables and for ORM classes. Learn it once and you can read data in every layer.

A statement does nothing until you execute it. That means you can build it in pieces, pass it to other functions, add filters conditionally and print it to see the SQL. All values in the statement are bound parameters, so user input is safe.

The sample data

Every example in this lesson uses the same employee table. The setup is repeated at the top of each program, so each can run on its own.

Columns, where() and ordering

Python Your first select()

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Notice that employee.c.dept == "eng" is not a Python boolean. Column objects overload the comparison operators, so the expression builds a SQL fragment (employee.dept = :dept_1) that where() adds to the statement.

Combining conditions

Calling .where() several times joins the conditions with AND. For anything more elaborate, use and_(), or_() and not_(), or the Python operators &, | and ~ (wrap each side in parentheses, because of operator precedence).

Python and_, or_, not_, and the operators

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The last example is the everyday pattern: start with a base statement and add where() only for the filters the user actually provided.

Operators

Goal SQLAlchemy SQL
equal, not equal col == v, col != v =, !=
comparison col > v, col <= v >, <=
IN a list col.in_([1, 2]), col.not_in([...]) IN (...)
range col.between(a, b) BETWEEN a AND b
NULL checks col.is_(None), col.is_not(None) IS NULL, IS NOT NULL
pattern col.like("A%"), col.ilike("a%") LIKE, case-insensitive match
string helpers col.startswith("A"), col.endswith(..), col.contains(..) LIKE with wildcards
arithmetic col + 1, col * 2, col / 3 +, *, /
string concat col + " text" (strings), col.concat(x) ||

== None is converted to IS NULL for you, but is_(None) is clearer and linters stop complaining.

Python in_, between, like, NULL and arithmetic

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

.label("yearly") gives an expression a name in the result, and the same trick works for ORDER BY and GROUP BY.

Ordering, limits and distinct

  • .order_by(col), .order_by(col.desc()), .order_by(col.desc().nulls_last()) (where supported).
  • .limit(n) and .offset(m) implement pagination. Always add an ORDER BY so pages are stable.
  • .distinct() removes duplicate rows.
Python Pagination and distinct

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Deep OFFSET pagination gets slow on big tables because the database still walks over the skipped rows. Chapter 8 shows keyset pagination as the faster alternative.

Seeing the final SQL

print(stmt) shows placeholders. To see the values filled in (for debugging only, never to execute), compile with literal_binds:

Python
print(stmt.compile(compile_kwargs={"literal_binds": True}))

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does calling .where(a).where(b) on the same select() produce?

    Multiple where() calls (or multiple arguments) are combined with AND.
  2. 2. Which expression finds rows where manager_id is NULL?

    is_(None) produces IS NULL. Comparing with the string 'NULL' would compare against a text value.
  3. 3. Why wrap each side in parentheses in (a == 1) & (b == 2)?

    Python operator precedence puts & above comparisons, so each comparison needs its own parentheses.
  4. 4. Why add an order_by() when using limit() and offset()?

    Without a defined order, the database may return rows in any order, so pages could repeat or skip rows.

Summary

  • select() builds a statement; where(), order_by(), limit(), offset() and distinct() refine it, and conn.execute() runs it.
  • Column expressions (==, in_, like, between, is_, +) build SQL, and and_/or_/not_ combine them.
  • Build statements incrementally to add filters conditionally; use label() to name expressions.

Next: Joins and Aggregates