SQL databases in Python

CS 374, Fall 2026

Last Thursday: the hotel demo

cur.execute(
    "INSERT INTO hotel (name, street, city, country, year) "
    "VALUES (%s, %s, %s, %s, %s) RETURNING hotel_id",
    row,
)
ids.append(cur.fetchone()[0])

• The schema is written twice: once in CREATE TABLE, once in every INSERT
• A row is a tuple, so row[3] is... the country? the city?
• You fetched the generated ids yourself, then passed them to the next table
• A typo in the SQL string is found when the program runs, not before

What Is an ORM?

Object-Relational Mapper: translates between Python objects and table rows.

• The class is written once; the ORM writes the SQL

SQLModel

• Written by the author of FastAPI, so the two work together
• Built on two well-known libraries:
  • SQLAlchemy -- the ORM; talks to the database
  • Pydantic -- data validation; talks to the web (JSON)
• Already in your venv: pip show sqlmodel
• Documentation: https://sqlmodel.tiangolo.com/

AI assistants often answer with plain SQLAlchemy code.
If it doesn't match the SQLModel docs, trust the docs.

A Table Is a Class

CREATE TABLE hotel (
  hotel_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL, street text NOT NULL, city text NOT NULL, country text NOT NULL,
  year integer NOT NULL
)

class Hotel(SQLModel, table=True):
    hotel_id: int | None = Field(default=None, primary_key=True)
    name: str
    street: str
    city: str
    country: str
    year: int

• table=True makes it a table; the name defaults to hotel (lowercase)
• A type hint is the column's type, and NOT NULL unless it says | None
• int | None = None on the key: "the database will assign this"

Field() Says the Rest

class HotelPhone(SQLModel, table=True):
    __tablename__ = "hotel_phone"

    hotel_id: int = Field(foreign_key="hotel.hotel_id", primary_key=True)
    sort: int = Field(primary_key=True)
    phone: str
    label: str | None = None

• __tablename__ -- otherwise the table would be hotelphone
• foreign_key="table.column" -- the table name, not the class name
• Two primary_key=True fields -- a composite key
• label: str | None = None -- nullable, and optional in Python

A Real Example: Course

From planner/core/models.py (you saw it at kickoff):

class Course(SQLModel, table=True):
    __table_args__ = (UniqueConstraint("subject_code", "number"),)

    course_id: int | None = Field(default=None, primary_key=True)
    subject_code: str = Field(foreign_key="subject.code", index=True)
    number: str = Field(description="Catalog number as text, such as 149 or 445")
    title: str
    credits: Decimal = Field(default=Decimal("3.0"), max_digits=3, decimal_places=1)
    is_active: bool = Field(default=True)

What SQLModel Wrote

CREATE TABLE course (
    course_id SERIAL NOT NULL,
    subject_code VARCHAR NOT NULL,
    number VARCHAR NOT NULL,
    title VARCHAR NOT NULL,
    credits NUMERIC(3, 1) NOT NULL,
    is_active BOOLEAN NOT NULL,
    PRIMARY KEY (course_id),
    UNIQUE (subject_code, number),
    FOREIGN KEY(subject_code) REFERENCES subject (code)
)
CREATE INDEX ix_course_subject_code ON course (subject_code)

• str is VARCHAR with no length (PostgreSQL treats it like text)
• SERIAL, not GENERATED ... AS IDENTITY -- older, but same idea
• No DEFAULT! Python fills in 3.0 and True, not the database
• description= is documentation only; __table_args__ is for what Field() can't say

Engine, Tables, Session

engine = create_engine("sqlite:///hotels.db", echo=True)
SQLModel.metadata.create_all(engine)

with Session(engine) as session:
    hotel = Hotel(name="Madison Inn", street="800 S Main St",
                  city="Harrisonburg", country="USA", year=1908)
    print(hotel.hotel_id)        # None
    session.add(hotel)
    session.commit()
    print(hotel.hotel_id)        # 1

• Engine: where the database is, and a pool of connections
• create_all(): CREATE TABLE for every class it knows about
• Session: a unit of work; collects changes, then commit() sends them
• echo=True prints every SQL statement -- turn it on while learning!

What the Session Sent

BEGIN (implicit)
INSERT INTO hotel (name, street, city, country, year) VALUES (?, ?, ?, ?, ?)
('Madison Inn', '800 S Main St', 'Harrisonburg', 'USA', 1908)
COMMIT
BEGIN (implicit)
SELECT hotel.hotel_id, hotel.name, ... FROM hotel WHERE hotel.hotel_id = ?
(1,)

• Nothing happened at session.add() -- only at commit()
• PostgreSQL gets the same RETURNING hotel_id you wrote by hand
• After commit(), reading hotel.hotel_id re-SELECTs the row

Caution: Table Models Don't Validate

>>> Hotel(name=5)                          # table=True
Hotel(name=5, hotel_id=None)

• No error! The mistake surfaces at commit(), as an IntegrityError
• Example: Field(gt=0) creates no CHECK constraint on a table model

>>> class HotelData(SQLModel):             # table defaults to False
...     name: str
>>> HotelData(name=5)
ValidationError: Input should be a valid string

• A data model validates, but has no table
• FastAPI uses these for requests and responses (Week 12)

Relationships (Preview)

class Hotel(SQLModel, table=True):
    ...
    phones: list["HotelPhone"] = Relationship(back_populates="hotel")

class HotelPhone(SQLModel, table=True):
    ...
    hotel: Hotel = Relationship(back_populates="phones")

hotel.phones.append(HotelPhone(sort=1, phone="540-555-0100"))

• A Relationship is not a column; it follows a foreign key for you
• On commit(), the hotel is inserted first and its id copied into each phone
• Compare Person.student and Student.person in core/models.py

Where This Is Going

• Tue, Oct 13: Exam 1 (no SQLModel on the exam)
• Week 8 reading: SQLModel tutorial, after the exam
• Thu, Oct 15: Querying -- select(), where(), joins
• HW4: Models and Data -- classes, Faker, loading
• GP3: Build and Load -- GP2 DBML becomes src/planner/<area>/models.py

SQL_ECHO=1 python scripts/build.py      # watch create_all() build the core

Lab: Your HW2 Tables in SQLModel

Rewrite last Thursday's create_load.py