Querying with SQLAlchemy

The SQLAlchemy query API lets you build database reads in Python — selecting, filtering, sorting, joining, and aggregating rows — and have them translated into efficient SQL for you.

Learn Querying with SQLAlchemy in our free Flask course — a beginner-friendly interactive lesson with worked examples, a practice exercise and a quick…

Part of the free Flask course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

In this lesson you'll use select() with where , order_by , limit , joins across tables, and aggregate functions like func.count — all against a seeded in-memory database.

Every read starts with select(Model) . You refine it with .where(...) to filter, .order_by(...) to sort, and then execute it. Wrapping in session.scalars(stmt) returns the model objects; .all() gives a list and .first() gives the first match or None .

The runnable example seeds three books, then sorts by year, filters to recent titles, and fetches a single first result.

Output: oldest-first list, then ['1984', 'Animal Farm'] for post-1940 titles, then 1984 as the alphabetically first title.

When your data spans two tables — books and their authors — a join lets you filter or select using columns from both. You call .join(OtherModel) on the statement and SQLAlchemy figures out the ON clause from the foreign key.

The example finds every book written by a specific author by joining Book to Author and filtering on the author's name.

Output: Orwell's books: ['1984', 'Animal Farm'] — selected by joining the two tables and filtering on the author.

To compute summaries, use functions from sqlalchemy.func . func.count() counts rows; combined with .group_by(...) you get counts per group. Because aggregates return values (not entities), read them with session.execute(...).all() for the full rows, or session.scalar(...) for a single number.

.limit(n) and .offset(m) slice results — the foundation of pagination, covered next.

Complete the queries. Replace each ___ to filter, sort descending, and count.

You used execute() when you wanted whole entities. For select(User) use scalars() to get User objects; use execute() only when selecting multiple columns or aggregates.

❌ AttributeError: 'NoneType' has no attribute …

first() returned None because nothing matched. Check the result before reading attributes, or use one_or_none() intentionally.

In a where() , use Column.is_(None) instead of == None , and combine conditions with and_() / or_() , not Python's and / or .

Lesson complete — you can ask your database anything!

You can build reads with select() , filter with where , sort with order_by , join related tables, and summarize with func.count and group_by .

🚀 Up next: Pagination — turn limit and offset into clean, paged API responses.

Practice quiz

What does every modern SQLAlchemy read start with?

  • select(Model)
  • query(Model)
  • fetch(Model)
  • read(Model)

Answer: select(Model). select(Model) builds the statement you then refine and execute.

Which method filters rows on a select() statement?

  • .limit()
  • .where()
  • .order_by()
  • .join()

Answer: .where(). .where(condition) restricts which rows are returned.

What does session.scalars(stmt).first() return when nothing matches?

  • An empty list
  • Raises an error
  • None
  • 0

Answer: None. first() yields the first result or None when there are no matches.

Which call returns a list of every matching result?

  • .first()
  • .one()
  • .scalar()
  • .all()

Answer: .all(). .all() returns all matching rows as a list, possibly empty.

How do you sort results in descending order?

  • .order_by(desc(Book.year))
  • .sort(Book.year)
  • .reverse()
  • .order_desc(Book.year)

Answer: .order_by(desc(Book.year)). Wrap the column in desc() inside order_by for descending order.

Which method joins a select() across two related tables?

  • .merge()
  • .join(Author)
  • .union(Author)
  • .relate(Author)

Answer: .join(Author). .join(Author) builds the SQL join using the foreign key relationship.

How do you count rows without loading them all into Python?

  • len(query.all())
  • sum(rows)
  • select(func.count()).select_from(Book)
  • Book.count

Answer: select(func.count()).select_from(Book). func.count() lets the database compute the total in one query.

When selecting multiple columns or aggregates, which executes the statement?

  • session.scalars()
  • session.get()
  • session.add()
  • session.execute()

Answer: session.execute(). execute() returns full Row tuples, right for multi-column or aggregate selects.

Which produces counts per group?

  • .group_by(Author.name) with func.count
  • .limit(1)
  • .offset(0)
  • .distinct()

Answer: .group_by(Author.name) with func.count. group_by combined with func.count gives a count for each group.

Inside a where(), how should you test a column for NULL?

  • Column == None
  • Column.is_(None)
  • Column is None
  • Column.null()

Answer: Column.is_(None). Use Column.is_(None) for NULL checks rather than == None.