Skip to content
Tutorials Center
My learning

Topics

Lesson 4 of 37 ยท Getting Started

A Quick Tour of the ORM

Take a fast, hands-on tour of the SQLAlchemy ORM. Define two related models, add objects with a Session, query them, update and delete, all in one runnable program.

Before going deep into engines and expressions, it helps to see the whole ORM workflow once from start to finish. This lesson is a tour, not a reference: every step reappears later with more detail. You will define two related classes, create the tables, add objects, run queries, change data and delete it.

The data model is a tiny library: an Author has many Book rows. That one-to-many shape appears in almost every application, so it is a good first example.

Step 1: describe the models

Every ORM project starts with a base class. Subclasses of it become mapped classes, and the annotations decide the columns.

Python
class Base(DeclarativeBase):
    pass

class Author(Base):
    __tablename__ = "author"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(60))
    books: Mapped[list["Book"]] = relationship(back_populates="author")

class Book(Base):
    __tablename__ = "book"
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str]
    author_id: Mapped[int] = mapped_column(ForeignKey("author.id"))
    author: Mapped["Author"] = relationship(back_populates="books")

Mapped[int] means a non-null integer column. Mapped[str | None] would mean nullable. relationship() is not a column: it is a Python-side link between objects, and back_populates ties the two directions together.

Step 2: create tables, add objects, query

Python The ORM workflow from start to finish

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Look at how little SQL you wrote. Adding a Book to le_guin.books was enough for the ORM to set author_id correctly when it flushed. Reading author.books triggered a query on demand (lazy loading), which chapter 5 explores.

Step 3: update and delete

Objects loaded by a session are tracked. Change an attribute and the session notices; delete with session.delete().

Python Updating and deleting

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

cascade="all, delete-orphan" tells the ORM that books belong to their author, so deleting the author deletes the books too. Without it the ORM would try to set author_id to NULL and fail on the non-null column.

The mental model

sequenceDiagram
    participant You
    participant S as Session
    participant DB as Database
    You->>S: add(obj)
    You->>S: obj.name = "new"
    You->>S: commit()
    S->>DB: INSERT / UPDATE / DELETE (flush)
    S->>DB: COMMIT
    You->>S: scalars(select(...))
    S->>DB: SELECT
    DB-->>S: rows
    S-->>You: objects

The Session collects your changes and writes them at flush or commit time. Queries return objects, reusing the ones it already holds.

Remember three words: model (the class), session (the workspace), flush/commit (when SQL is sent). Chapters 4 to 6 unpack each.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does Mapped[str | None] declare?

    Optional types in the annotation make the column nullable. Plain Mapped[str] means NOT NULL.
  2. 2. When does the Session send INSERT statements for objects you added?

    add() only registers the object. SQL is emitted at flush time, which commit() triggers.
  3. 3. What did cascade='all, delete-orphan' do in the delete example?

    Cascade rules propagate Session operations from a parent object to its related objects.
  4. 4. How do you run a query that returns Book objects?

    In 2.0 you pass select(Book) to session.scalars() or session.execute().

Summary

  • Define models on a DeclarativeBase, with Mapped[...] annotations and relationship() between them.
  • Session.add() registers objects; commit() writes them. Loaded objects are tracked, so changing an attribute leads to an UPDATE.
  • Query with session.scalars(select(...)), join with .join(), and delete with session.delete().
  • Cascades decide what happens to related objects.

Next: Creating an Engine and Database URLs