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
requirementsandplansearly pull requests, and thecoreupdates - One person per team merges
maininto the team'sgp4branch 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
- Outer joins, subqueries, CTEs, and window functions with
Examples / Practice [20 min]
- Download air_models.py and practice.py
- Set
USERNAMEinpractice.py, then from that folder, with yourplannervenv active:python practice.py 0 - The
airqueries (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.sqland oneselect()query inqueries.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