Querying with SQLModel

CS 374, Fall 2026

Last Week: Objects In

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

with Session(engine) as session:
    session.add(course)
    session.commit()

• A class is a table; an object is a row
• add() and commit() write the INSERT for you

Today: objects out. SELECT returns rows; can it return objects instead?

A Query Returns Objects

SELECT * FROM course WHERE subject_code = 'CS' ORDER BY number;

from sqlmodel import select

stmt = select(Course).where(Course.subject_code == "CS").order_by(Course.number)
courses = session.exec(stmt).all()      # list[Course]

for course in courses:
    print(course.number, course.title)  # attributes, not row[2]

• select(Course) builds a statement; nothing runs yet
• session.exec(stmt) sends it, and turns each row into a Course object
• Course.subject_code is the column; course.subject_code is a value

Building a Statement

stmt = (
    select(Course)
    .where(Course.subject_code == "CS", Course.credits >= 3)  # comma means AND
    .where(Course.is_active)                                  # so does a 2nd where()
    .order_by(Course.number.desc())
    .limit(5)
)
print(stmt)       # prints the SQL, without running it

SELECT course.course_id, course.subject_code, course.number, ...
FROM course
WHERE course.subject_code = :subject_code_1 AND course.credits >= :credits_1
  AND course.is_active ORDER BY course.number DESC
 LIMIT :param_1

• Each method returns a new statement, so you can build one in steps
• :subject_code_1 is a parameter, like %s in psycopg: no SQL injection

Operators Become SQL

Ruff flags x == None (E711) and x == True (E712).
Write .is_(None), and use a boolean column by itself.

Getting the Results

result = session.exec(stmt)

course = session.get(Course, 13)    # by primary key; None if not found

• Use .one() when the answer must be exactly one row: a bug fails loudly

Objects or Rows? Depends on the select()

session.exec(select(Course)).all()
# [Course(course_id=1, title='Introduction to Computer Science', ...), ...]

session.exec(select(Course.title)).all()
# ['Introduction to Computer Science', 'Introduction to Programming', ...]

session.exec(select(Course.title, Course.credits)).all()
# [('Introduction to Computer Science', Decimal('3.0')), ...]

session.exec(select(Subject, Course).join(Course)).all()
# [(Subject(code='CS', ...), Course(course_id=1, ...)), ...]

• One class: objects. One column: values. Several: tuples.
• Only whole objects can be changed and saved back

Joins

SELECT person.* FROM person
JOIN student ON student.person_id = person.person_id
WHERE student.status = 'graduated';

stmt = (
    select(Person)
    .join(Student)                       # ON clause comes from the foreign key
    .where(Student.status == "graduated")
)

• The ON clause is inferred when exactly one foreign key connects the tables
• Otherwise, say it: .join(Term, Student.admit_term == Term.term_code)
• Still returns Person objects; Student is only used for filtering

Relationships: Joins You Don't Write

person = session.get(Person, 33)
print(person.student.student_number)     # a 2nd SELECT, sent right here

for subject in session.exec(select(Subject)).all():
    print(subject, len(subject.courses)) # one SELECT per subject!

• A Relationship attribute is loaded lazily: when you first touch it
• Convenient, but a loop can send N + 1 queries -- watch echo=True
• Fix: load them all up front

from sqlalchemy.orm import selectinload
select(Subject).options(selectinload(Subject.courses))   # 2 queries total

Aggregates Return Rows

from sqlmodel import func

stmt = (
    select(Student.catalog_year, func.count())
    .group_by(Student.catalog_year)
    .having(func.count() >= 10)
    .order_by(Student.catalog_year)
)
for year, total in session.exec(stmt):
    print(year, total)

• func.count(), func.sum(...), func.avg(...), ... : any SQL function
• A group is not an object, so the result is tuples
• For anything much harder, plain SQL is a fine choice (Nov 05)

Changing What You Found

course = session.get(Course, 13)
course.title = "Database Systems and Design"
session.commit()                         # UPDATE course SET title=? WHERE ...

session.delete(session.get(LoginEvent, 1))
session.commit()                         # DELETE FROM login_event WHERE ...

• The session tracks every object it loaded, and notices what changed
• Identity map: one row is one object per session

a = session.get(Course, 13)
b = session.exec(select(Course).where(Course.number == "374")).one()
a is b      # True

The Life of an Object

from sqlalchemy import inspect
inspect(course).persistent      # also .transient, .pending, .detached

• Only an attached object (pending or persistent) can talk to the database

Detached Objects

def find_course(subject: str, number: str) -> Course:
    with Session(engine) as session:
        stmt = select(Course).where(Course.subject_code == subject,
                                    Course.number == number)
        return session.exec(stmt).one()

course = find_course("CS", "374")
print(course.title)          # works: loaded before the session closed
print(course.subject.name)   # DetachedInstanceError

Parent instance <Course at 0x7f...> is not bound to a Session;
lazy load operation of attribute 'subject' cannot proceed

• A detached object keeps what it already loaded, and nothing else

Commit Expires Everything

with Session(engine) as session:
    course = session.get(Course, 13)
    course.title = "Database Systems and Design"
    session.commit()         # every object's attributes are now "expired"

print(course.title)          # DetachedInstanceError!

• After commit(), the session reloads an object the next time you read it
  -- someone else may have changed the row
• Detached, it has no way to reload

Fixes: use the object inside the with block;
call session.refresh(course) before it ends;
or use selectinload for relationships you'll need

Where This Is Going

• HW4: Models and Data -- due Monday, Oct 19
• Oct 20: Loading a Database -- real data from files and APIs
• Oct 22 -- Oct 29: outer joins, subqueries, window functions -- in SQL
• Nov 05: Advanced Queries in Python -- when (not) to use select()
• Nov 10: FastAPI -- every request opens a session; response is detached

Lab: Querying the Core Schema

Courses, students, and faculty, loaded into SQLite