Queries with select()
26 min · Intermediate
Need help? Ask a tutor on WhatsApp (opens in a new tab)In SQLAlchemy 2.0 every query is a statement object made by select(). You build the statement first, then hand it to a session to run. This keeps queries easy to read, reuse and test.
select() builds a statement
select() statement is an object that describes a SELECT query. It does nothing until you pass it to session.execute() or session.scalars().
A query is just data until you run it. select(Product) means all columns of the product table. Methods like where() and order_by() return a new, longer statement.
stmt = ( select(Product) .where(Product.price > 100) .order_by(Product.price.desc()))print(stmt)Example 1 · A statement is SQL you can read
from sqlalchemy import create_engine, select, func, or_, Stringfrom sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session
class Base(DeclarativeBase): pass
class Product(Base): __tablename__ = "product" id: Mapped[int] = mapped_column(primary_key=True) name: Mapped[str] = mapped_column(String(50)) category: Mapped[str] = mapped_column(String(30)) price: Mapped[int]
engine = create_engine("sqlite://")Base.metadata.create_all(engine)with Session(engine) as seed: seed.add_all([ Product(name="Keyboard", category="electronics", price=1200), Product(name="Mouse", category="electronics", price=500), Product(name="Monitor", category="electronics", price=9000), Product(name="Notebook", category="stationery", price=60), Product(name="Pen", category="stationery", price=10), Product(name="Desk", category="furniture", price=7000), ]) seed.commit()
stmt = select(Product).where(Product.price > 100).order_by(Product.price.desc())print(stmt)SELECT product.id, product.name, product.category, product.price FROM product WHERE product.price > :price_1 ORDER BY product.price DESC
What this code does
:price_1is a placeholder. The value 100 is sent separately and safely.- Nothing was run: printing a statement only shows its SQL.
Printing a statement shows the SQL it will send. Nothing has touched the database yet. Several where() calls are joined with AND.
Run it and choose the result shape
Scalar result is a result that returns only the first column of each row. With select(Model) that column is the model object, so you get objects instead of tuples.
The same statement can give you objects, single values or rows. scalars() returns the first column of each row, which for select(Product) is the Product object itself. execute() returns rows.
products = session.scalars(select(Product)).all()first = session.scalars(stmt).first()rows = session.execute(select(Product.name, Product.price)).all()one = session.scalar(select(Product).where(Product.id == 1))Example 2 · Objects, rows and single values
from sqlalchemy import create_engine, select, func, or_, Stringfrom sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session
class Base(DeclarativeBase): pass
class Product(Base): __tablename__ = "product" id: Mapped[int] = mapped_column(primary_key=True) name: Mapped[str] = mapped_column(String(50)) category: Mapped[str] = mapped_column(String(30)) price: Mapped[int]
engine = create_engine("sqlite://")Base.metadata.create_all(engine)with Session(engine) as seed: seed.add_all([ Product(name="Keyboard", category="electronics", price=1200), Product(name="Mouse", category="electronics", price=500), Product(name="Monitor", category="electronics", price=9000), Product(name="Notebook", category="stationery", price=60), Product(name="Pen", category="stationery", price=10), Product(name="Desk", category="furniture", price=7000), ]) seed.commit()
with Session(engine) as s: cheap = select(Product).where(Product.price < 600).order_by(Product.price) for p in s.scalars(cheap): print(p.name, p.price) rows = s.execute(select(Product.name, Product.price).where(Product.id <= 2)).all() print(rows) print(s.scalar(select(Product.name).where(Product.id == 3))) print(s.scalars(select(Product).where(Product.id == 99)).first())Pen 10
Notebook 60
Mouse 500
[('Keyboard', 1200), ('Mouse', 500)]
Monitor
NoneWhat this code does
scalarsgave Product objects, so we read.nameand.price.executegave rows of two values.first()returnedNonefor a missing id.
Use .all() for a list, .first() for the first item or None, and .one() when you expect exactly one. one() raises an error if there are none or many.
Filter, group, count and page
Aggregate function is a SQL function such as count, sum, avg, min or max that turns many rows into one value. In SQLAlchemy you get them from func.
Real queries combine conditions and summarise data. SQLAlchemy has Python methods for the SQL keywords, so you rarely write SQL text.
stmt = select(Product).where( or_(Product.category == "furniture", Product.price < 100))count = select(func.count()).select_from(Product)per_category = select(Product.category, func.count()).group_by(Product.category)page = select(Product).order_by(Product.id).limit(2).offset(2)Example 3 · Combine conditions
from sqlalchemy import create_engine, select, func, or_, Stringfrom sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session
class Base(DeclarativeBase): pass
class Product(Base): __tablename__ = "product" id: Mapped[int] = mapped_column(primary_key=True) name: Mapped[str] = mapped_column(String(50)) category: Mapped[str] = mapped_column(String(30)) price: Mapped[int]
engine = create_engine("sqlite://")Base.metadata.create_all(engine)with Session(engine) as seed: seed.add_all([ Product(name="Keyboard", category="electronics", price=1200), Product(name="Mouse", category="electronics", price=500), Product(name="Monitor", category="electronics", price=9000), Product(name="Notebook", category="stationery", price=60), Product(name="Pen", category="stationery", price=10), Product(name="Desk", category="furniture", price=7000), ]) seed.commit()
with Session(engine) as s: stmt = select(Product.name).where(or_(Product.category == "furniture", Product.price < 100)).order_by(Product.name) print(s.scalars(stmt).all()) print(s.scalars(select(Product.name).where(Product.name.like("M%")).order_by(Product.name)).all()) print(s.scalars(select(Product.name).where(Product.id.in_([1, 4])).order_by(Product.id)).all())['Desk', 'Notebook', 'Pen'] ['Monitor', 'Mouse'] ['Keyboard', 'Notebook']
What this code does
or_matches furniture or anything under 100.like("M%")matches names starting with M, andin_matches a list of ids.
Example 4 · Count, group and page
from sqlalchemy import create_engine, select, func, or_, Stringfrom sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session
class Base(DeclarativeBase): pass
class Product(Base): __tablename__ = "product" id: Mapped[int] = mapped_column(primary_key=True) name: Mapped[str] = mapped_column(String(50)) category: Mapped[str] = mapped_column(String(30)) price: Mapped[int]
engine = create_engine("sqlite://")Base.metadata.create_all(engine)with Session(engine) as seed: seed.add_all([ Product(name="Keyboard", category="electronics", price=1200), Product(name="Mouse", category="electronics", price=500), Product(name="Monitor", category="electronics", price=9000), Product(name="Notebook", category="stationery", price=60), Product(name="Pen", category="stationery", price=10), Product(name="Desk", category="furniture", price=7000), ]) seed.commit()
with Session(engine) as s: print(s.scalar(select(func.count()).select_from(Product))) stmt = select(Product.category, func.count(), func.max(Product.price)).group_by(Product.category).order_by(Product.category) for category, n, top in s.execute(stmt): print(category, n, top) page = select(Product.name).order_by(Product.id).limit(2).offset(4) print(s.scalars(page).all())6 electronics 3 9000 furniture 1 7000 stationery 2 60 ['Pen', 'Desk']
What this code does
- The database does the counting, so only small results travel to Python.
- Page 3 of size 2 uses
offset(4). Only two products remain, Pen and Desk.
limit and offset give one page of results. Always add order_by with them, because without an order the database may return rows in any order.
Next step
Check your understanding
11 quick questions