TopolearnReal queries are mostly about narrowing and ordering rows correctly, and the operator API differs from plain Python in a few key places.
where() accepts any number of SQL expressions, joined with AND. Calling .where() again adds more conditions. Column attributes support Python operators that render as SQL: ==, !=, <, >=.
Some SQL operators need methods, because Python's own keywords can't be overloaded:
User.id.in_([1, 2, 3])and.not_in(...).User.name.like("a%"),.ilike(...)for case-insensitive matching,.startswith("a"),.contains("x").User.email.is_(None)rendersIS NULL;== Noneworks too but linters complain.or_(a, b),and_(a, b),not_(a)fromsqlalchemy; the bitwise|,&and~operators work as well, but mind their precedence and wrap comparisons in parentheses.User.age.between(18, 30).
Never use Python and, or, not or in on columns: they try to evaluate truthiness and raise or silently produce the wrong query.
Sort with order_by(User.name), User.created.desc() or .nulls_last(). Page with .limit(20).offset(40). Offset pagination gets slow on large tables because the database still walks the skipped rows; for big lists, "keyset" pagination (where(User.id > last_seen_id).order_by(User.id).limit(20)) is faster and stable.
Example
from sqlalchemy import create_engine, select, or_
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session
class Base(DeclarativeBase):
pass
class User(Base):
__tablename__ = "users"
id: Mapped[int] = mapped_column(primary_key=True)
name: Mapped[str]
age: Mapped[int | None]
engine = create_engine("sqlite://")
Base.metadata.create_all(engine)
with Session(engine) as s:
s.add_all([User(name=n, age=a) for n, a in
[("ada", 36), ("alan", 41), ("bob", None), ("cy", 19)]])
s.commit()
q = (select(User.name)
.where(or_(User.name.like("a%"), User.age.is_(None)))
.order_by(User.name.desc()).limit(2))
print(s.scalars(q).all()) # ['bob', 'alan']
print(s.scalars(select(User.name).where(User.id.in_([1, 4]))).all()) # ['ada', 'cy']Remember
- Chained or multiple where() conditions are ANDed
- Use in_(), like(), is_(None), between() for SQL operators
- or_/and_/not_ (or |, &, ~ with parentheses), never Python and/or/not
- Prefer keyset pagination over large offsets
Watch out for
- Writing
User.age > 18 and User.age < 30: Python'sandbreaks the expression.