SQLAlchemy ORM — objects that persist
The ORM lets you work with classes and instances — SQLAlchemy translates them to SQL under the hood.
The modern (2.x) declarative style
Uses type hints for mapping — the class is the schema.
Relationships
posts: Mapped[list["Post"]] = relationship(back_populates="author")
- One-to-many: a user has many posts.
back_populates— bidirectional link, sopost.authoralso works.- Foreign key +
relationshiptogether = navigable graph in Python.
Session — your unit of work
s.add(obj)— mark for insert.s.commit()— flush + commit transaction.s.rollback()— undo pending changes.s.query(User).filter_by(...)— build a query.- Objects returned are tracked — mutating them +
commit()issues an UPDATE.
Loading strategies (the one thing to know)
- Lazy (default): each
user.postsaccess triggers a fresh SELECT. Beware N+1 queries in loops. - Eager:
.options(selectinload(User.posts))— one extra query for all posts. - Rule of thumb: if you're iterating a list of objects and touching a relationship, eager-load.
Try it
- Add a
Post.bodycolumn and a second post. - Query all posts whose title contains "SQL".
3 graded exercises
Write the code, click Check. We’ll tell you exactly what to fix.
- Exercise 1
Define an ORM model called
Userthat inherits fromBase(aDeclarativeBasesubclass). Give it__tablename__ = 'users', anid: Mapped[int]primary key column, and anemail: Mapped[str]column. Then create the engine and callBase.metadata.create_all(engine). - Exercise 2
The
Usermodel, engine, and table are already set up. Open aSession, add aUser(email='a@x.com'), commit, then query the user back withsession.query(User).first()and print their email. - Exercise 3
The
Usertable already has one row with email 'a@x.com'. Open a Session, use.query(User).filter_by(email='a@x.com').first()to fetch that user, then print theirid.