Skip to content

Querying with SQLModel

Written for the core schema of Oct 15

This lab used the core schema as it was before GP3, when faculty was a table and person had a preferred_name. The core now has an employee table in place of faculty, and no preferred_name, as the Core Schema Design page describes. So the load_core.py script no longer runs against the current class repository; the lab is kept here as a record of what we did.

In this lab you will write queries against the project's core schema, using the classes in planner.core. The load_core.py script builds a SQLite database with real courses and about 80 generated people. You don't need the SSH tunnel today.

You will submit one file, queries.py, with your code for each step. Answer each question in a # comment next to the code it is about; those answers are what gets graded. Several steps tell you what result to expect, so you can check your own work. Steps 1–5 are required; Step 6 is if you have time.

Step 1: Set up

Make a folder for today's lab outside of your planner clone, and activate the planner environment:

source path/to/planner/.venv/bin/activate        # Linux and macOS
source path/to/planner/.venv/Scripts/activate    # Windows (Git Bash)

Download load_core.py into the folder and run it:

python load_core.py

It creates lab.db with eight of the ten core tables (credential and activity_event are empty). Because the script imports from planner.core, it only works with the planner venv active. Run it again whenever you want to start over with the original data.

Then create queries.py with this skeleton:

from sqlalchemy import inspect
from sqlalchemy.orm import selectinload
from sqlmodel import Session, create_engine, func, or_, select

from planner.core import Course, Faculty, LoginEvent, Person, Student, Subject, Term

engine = create_engine("sqlite:///lab.db", echo=True)


def step1(session: Session) -> None:
    course = session.get(Course, 13)
    print(course)
    print(repr(course))


if __name__ == "__main__":
    with Session(engine) as session:
        step1(session)

Write each step as its own function, like step1(), and call it from the main block.

Run it, and find the SELECT statement in the output. __str__ (from print) and __repr__ show two different views of the same object.

Note

The echo output is how you learn what SQLModel is doing, but it gets long. Keep it on while you work, and comment out the calls to earlier steps as you go. Comment out the calls, not the functions, so that all of your code is still there when you submit. Change it to echo=False only when you want to see just your own output.

Step 2: Select objects

Write each query below, and print each result. Before running each one, print(stmt) and check that the SQL is what you meant.

  1. All the active CS courses, ordered by number. (21 courses, from CS 101 to CS 499C)
  2. The courses worth four credits. (4 courses)
  3. CS 374 itself, using .one(). Print its title.
  4. Change the last query to look for MATH courses, and keep .one(). What happens? What would .first() have done instead? Which one would you rather have when there is a bug in your where()?

For the first one, remember that a boolean column can stand by itself in a where(). Ruff flags Course.is_active == True, so don't write that.

From the slides

Each query is the one on slide 3 with a different where(). Slide 4 shows how to chain methods, and slide 6 explains .one() and .first().

Step 3: Conditions and columns

  1. Students who have no expected graduation term. (26 students) Use .is_(None), not == None.
  2. The first and last names of people whose last name starts with S, ordered by last name. Select only the two columns, not Person. (7 rows, from Salazar to Stone) What type is each item in the result?
  3. Active courses that are in CS or worth four credits. Use or_(). How many of the results are MATH courses?

What is the difference between select(Person) and select(Person.first_name, Person.last_name)? Which one could you use to change someone's name?

From the slides

Slide 5 lists the operators you need, and slide 7 shows what comes back when you select columns instead of a class.

Step 4: Joins and relationships

  1. Graduated students, as Person objects ordered by last name. Use .join(Student) and filter on Student.status. (9 people, from Ashley to Wright)
  2. In a loop over those results, print each person's student number using person.student.student_number. Look at the echo output. How many SELECT statements did the loop send?
  3. Loop over every Subject, and print the subject and len(subject.courses). Count the SELECT statements again. Then add .options(selectinload(Subject.courses)) to the query, and count once more.
  4. People who are both students and faculty. Join twice: select(Person).join(Student).join(Faculty). (2 people)

For Questions 2 and 3, how many queries would there be if there were 10,000 students?

From the slides

Question 1 is the query on slide 8 with an order_by(), and Questions 2 and 3 are the loops on slide 9. The SQLModel tutorial shows the same idea with heroes and teams in Read Relationships.

Step 5: Attached and detached

Write this function above the main block:

def find_course(subject: str, number: str) -> Course:
    """Look up one course, in a session of its own."""
    with Session(engine) as session:
        stmt = select(Course).where(Course.subject_code == subject, Course.number == number)
        course = session.exec(stmt).one()
        print("inside:", inspect(course).persistent)
        return course

Then write a step5() function that takes no session, call it from the main block outside the with block, and try each of these lines in it, one at a time:

course = find_course("CS", "374")
print("outside:", inspect(course).detached)
print(course.title)
print(course.subject.name)

Which line fails, and why can the other one succeed? Fix find_course so that course.subject.name works after it returns. There are at least two ways: touch the relationship before returning, or use selectinload.

Now write a second function, rename_course(course_id, title), that gets a course, changes its title, commits, and returns the course. Print the returned course's title. What error do you get, and what does it have to do with commit()? Fix it by adding one line, session.refresh(course), and look at the echo output to see what that line sent.

Finally, test the identity map: inside one session, get course 13 twice, once with session.get() and once with select(). Are the two results ==? Are they the same object (is)? How many SELECT statements did that take?

From the slides

find_course is the example on slide 13, rename_course is the one on slide 14, and the identity map is at the bottom of slide 11. Slide 12 summarizes the four states of an object. For what commit() expires and what refresh() does, see Automatic IDs, None Defaults, and Refreshing Data in the SQLModel tutorial.

Step 6: Aggregates (if time)

Each of these returns tuples, not objects. Loop over the result with two or three variables, like for year, total in session.exec(stmt):.

  1. The number of students in each catalog year, in order. (5 rows; 2022 has 14)
  2. For each subject, its name, the number of active courses, and their total credits. (Computer Science has 21 courses and 60 credits)
  3. The email address of every person with at least 3 failed logins. Join LoginEvent, and use ~LoginEvent.succeeded for "not succeeded". (4 people)
  4. The number of failed logins that matched nobody: person_id is null. Use select(func.count()).select_from(LoginEvent) and .one(). (28)

Write the SQL for Question 3 by hand, then compare it with print(stmt). Which one is easier to read?

From the slides

Question 1 is the query on slide 10 without the having().

Submission

Submit your file queries.py via Gradescope by the end of the day. The autograder only checks that the file has the queries, functions, and comments the steps ask for; it does not run your code. Your answers to the questions are graded by hand.