Advanced Queries in Python

CS 374, Fall 2026

Merge Day

Merge main Into Your Branch

main now has the requirements and plans fixes, and the core update.
One person per team does this, while the others watch:

git switch <area>/gp4
git pull                         # your teammates' commits first
git fetch origin
git merge origin/main            # a merge commit; keep the default message
python scripts/load.py --scratch # the same check the server runs
git push

Then everyone else on the team runs git pull.

  • A conflict means someone edited a file outside your directory. Stop and ask.
  • No pins changed, so there is no need to reinstall.

What Is New on main

from planner.core import CURRENT_TERM, TODAY    # 1268, date(2026, 11, 1)
  • scripts/build.py runs each team's queries.sql, in AREAS order, after the tables
  • A view that fails, or whose name does not start with your area, fails the check
  • load.py --drop drops your views too
  • Requirements' student_program and program.code are fixed; read them, don't guess
  • The plans load is about 7 times faster on PostgreSQL

Joins and Aggregates in select()

From SQL to select()

SQL SQLModel
JOIN ... ON ... .join(Table, condition)
LEFT JOIN ... ON ... .outerjoin(Table, condition)
GROUP BY, HAVING .group_by(...), .having(...)
count(*) AS logins func.count().label("logins")
ORDER BY logins DESC .order_by(logins.desc())
x IN (SELECT ...) X.x.in_(select(...))
NOT EXISTS (SELECT ...) ~exists().where(...)
(SELECT avg(...)) select(func.avg(...)).scalar_subquery()
WITH t AS (SELECT ...) t = select(...).cte("t"), then t.c.column
rank() OVER (PARTITION BY) func.rank().over(partition_by=..., order_by=...)

func and exists come from sqlalchemy; select comes from sqlmodel.

Outer Join and Aggregate

Successful logins this term for every employee, including those with none:

term = session.get(Term, CURRENT_TERM)
logins = func.count(LoginEvent.login_id).label("logins")
statement = (
    select(Person.last_name, logins)
    .join(Employee, Employee.person_id == Person.person_id)
    .outerjoin(
        LoginEvent,
        (LoginEvent.person_id == Person.person_id)
        & LoginEvent.succeeded
        & (LoginEvent.occurred_at >= term.start_date),
    )
    .group_by(Person.person_id, Person.last_name)
    .order_by(logins, Person.last_name)
)
  • 42 rows, 9 of them 0; an inner .join() returns 33. Conditions go inside the ON: in .where(), they drop the zeros.
  • count(LoginEvent.login_id), not count(): a row with no login has a null id

Rows, Not Objects

Select columns, and each result is a Row, which works like a named tuple:

for row in session.exec(statement):
    print(row.last_name, row.logins)   # by label
    name, count = row                  # or unpack it

Select a class, and each result is an object:

session.exec(select(Person))             # Person objects
session.exec(select(Person.last_name))   # strings
session.exec(select(Person, logins))     # (Person, int) rows
  • .label("logins") is what gives the column its name in the row
  • Return type hints say which: list[Person] or list[tuple[str, int]]

Subqueries and Window Functions

Subqueries in WHERE

Employees who have never logged in:

never = ~exists().where(LoginEvent.person_id == Person.person_id)
statement = select(Person).join(Employee).where(never)                     # 9 rows

The same question with not_in:

everyone = select(LoginEvent.person_id)
statement = select(Person).join(Employee).where(Person.person_id.not_in(everyone))   # 0 rows!
  • 40 failed logins have a null person_id, and x NOT IN (..., NULL) is never true
  • Prefer ~exists() for "has no ...": it is correlated, and nulls cannot break it
  • Or filter the nulls out of the subquery: .where(LoginEvent.person_id.is_not(None))

A Subquery That Returns One Value

How many requests took more than three times the average?

average = select(func.avg(ActivityEvent.duration_ms)).scalar_subquery()
statement = (
    select(func.count())
    .select_from(ActivityEvent)
    .where(ActivityEvent.duration_ms > 3 * average)
)
session.exec(statement).one()     # 1720
  • .scalar_subquery() makes a statement usable as a value, like (SELECT avg(...)) in SQL
  • .one() for exactly one row, .first() for one or none, .all() for a list
  • .select_from() says what count() counts, since nothing else names a table

WITH: Name a Step

Average requests per student, by catalog year:

per_person = (
    select(ActivityEvent.person_id, func.count().label("requests"))
    .group_by(ActivityEvent.person_id)
    .cte("per_person")
)
statement = (
    select(Student.catalog_year, func.avg(per_person.c.requests).label("avg_requests"))
    .join(per_person, per_person.c.person_id == Student.person_id)
    .group_by(Student.catalog_year)
)
catalog_year 2023 2024 2025 2026
avg_requests 169.5 165.4 167.0 143.3
  • A CTE's columns are under .c; .subquery() instead of .cte() puts it in FROM, same answer

Window Functions

The two busiest students in each catalog year:

requests = func.count(ActivityEvent.activity_id)
rank = func.rank().over(partition_by=Student.catalog_year, order_by=requests.desc())
ranked = (
    select(Student.catalog_year, Person.last_name,
           requests.label("requests"), rank.label("rank"))
    .join(Person, Person.person_id == Student.person_id)
    .join(ActivityEvent, ActivityEvent.person_id == Student.person_id)
    .group_by(Student.catalog_year, Person.person_id, Person.last_name)
    .subquery()
)
statement = select(*ranked.c).where(ranked.c.rank <= 2)
# (2023, 'Underwood', 596, 1), (2023, 'Wright', 450, 2), (2024, 'Alexander', 656, 1), ...
  • A window cannot go in WHERE, so filter on it from an outer query
  • select(*ranked.c): select(ranked) returns only the first column

See the SQL

print(statement)
SELECT person.person_id, person.first_name, person.last_name, person.email, ...
FROM person JOIN employee ON person.person_id = employee.person_id
WHERE (person.person_id NOT IN (SELECT login_event.person_id
FROM login_event))

Or log every statement the engine sends:

engine = create_engine(DATABASE_URL, echo=True)
  • When a query returns nothing, print it before you debug the Python
  • Paste the printed SQL into DB Browser or pgAdmin, and compare with your view

Raw SQL with text()

When the Object Model Gets in the Way

Write the SQL yourself, with text(), when:

  • You select from a view: SQLModel has no class for it
  • The SQL is clearer than the Python: a long WITH, a set operation, a CASE
  • You want exactly the SQL you tested in pgAdmin

Stay with select() when:

  • You want objects, relationships, and type hints back
  • The query is built up in pieces: an optional filter, a parameter, a sort chosen by the caller
  • A column is renamed: select() breaks loudly, text() breaks at run time

text() With Parameters

class BusyPath(SQLModel):          # no table=True: a view is not a table
    """One row of demo_busy_path."""

    path: str
    requests: int
    errors: int


def busy_paths(session: Session, least: int) -> list[BusyPath]:
    sql = text("SELECT * FROM demo_busy_path WHERE requests >= :least ORDER BY requests DESC")
    rows = session.exec(sql, params={"least": least}).mappings()
    return [BusyPath.model_validate(row) for row in rows]
BusyPath(path='/catalog/courses', requests=4502, errors=19)
BusyPath(path='/requirements/programs', requests=4475, errors=24)
  • :least is bound by the driver, never pasted in: no f-strings, no injection
  • .mappings() gives dictionaries keyed by column name, which model_validate reads

The Same Answer, a Different Type

session.exec(select(func.avg(ActivityEvent.duration_ms))).one()
database result
SQLite 45.20855237001369 (float)
PostgreSQL Decimal('45.2085523700136944')
  • PostgreSQL's avg of integers is numeric, which psycopg returns as Decimal
  • A class with avg_ms: float converts either one, which is another reason to return objects
  • round(100.0 * part / whole, 1) in SQL works in both

Shape of queries.py

"""Queries and views for the advising team."""

from sqlmodel import Session

from planner.core import CURRENT_TERM, EmployeeKind, engine


def logins_by_kind(session: Session, term_code: int = CURRENT_TERM) -> list[tuple[EmployeeKind, int]]:
    """Author: Pat Doe. Successful logins this term for each kind of employee."""
    ...                                  # as in the GP4 spec


if __name__ == "__main__":
    with Session(engine) as session:
        for row in logins_by_kind(session)[:5]:
            print(row)
  • Every function takes the session first, so GP5's endpoints can pass theirs in
  • Import only what you use: ruff check fails on an unused import
  • Run it with python -m planner.<area>.queries, on SQLite and on PostgreSQL

GP4 Team Time

Each person: one view in queries.sql, one select() query in queries.py