Skip to content

Nov 05: Advanced Queries in Python

Learning Objectives

After today's class, you should be able to:

  • Write joins, aggregates, and subqueries using SQLModel.
  • Execute raw SQL from Python when the object model gets in the way.

Lesson Outline

Merge Day [15 min]

  • Merged before class: the requirements and plans early pull requests, and the core updates
  • One person per team merges main into the team's gp4 branch and pushes; everyone else pulls
  • Then reload your own schema on sec1 (now without the temporary fix):
    DATABASE_URL=postgresql+psycopg://your_username@data.cs.jmu.edu/sec1 python scripts/load.py --drop

Advanced Queries in Python [20 min]

  • Advanced Queries in Python
    • Outer joins, subqueries, CTEs, and window functions with select()
    • Raw SQL with text(), mapped into a SQLModel class, for each of your views

Examples / Practice [20 min]

  • Download air_models.py and practice.py
  • Set USERNAME in practice.py, then from that folder, with your planner venv active: python practice.py 0
  • The air queries (1, 3, 5) are written; read each one, then write the core query with the same shape (2 and 4)
    • Each docstring gives the answer, so check your own work; nothing is submitted

GP4 team time [20 min]

  • One person per team: download queries.sql and queries.py into src/planner/<area>/, replace every <area> with your area, commit, and push; everyone else pulls
  • Each person: one view in queries.sql and one select() query in queries.py, each replacing a stub, with your name in it
  • Check every answer on SQLite and on PostgreSQL

Your To-Do List

  • GP4 ← due Monday