Skip to content
Tutorials Center
My learning

Topics

Lesson 104 of 149 ยท Data and the Web

ORMs with SQLAlchemy

Learn SQLAlchemy 2.0. Run Core queries, define ORM models with Mapped[], use sessions and relationships, avoid N+1 queries and migrate with Alembic.

Writing SQL by hand with sqlite3 or a server driver works well, but in a bigger application the same chores come back again and again: turning rows into objects, building INSERT statements from objects, writing slightly different queries for slightly different filters, and keeping SQL that runs on SQLite in tests and PostgreSQL in production. An ORM (object-relational mapper) handles those chores. You describe tables as Python classes, work with ordinary objects, and the ORM writes parameterized SQL for you.

SQLAlchemy is the most widely used database toolkit in Python. It powers Flask apps, FastAPI services, data pipelines and many tools you may have heard of. It has two layers: Core, a Python way to build and run SQL statements, and the ORM, which maps classes to tables on top of Core. This lesson teaches the modern 2.0 style of both, and every example runs live on an in-memory SQLite database.

Know your SQL first. An ORM doesn't replace SQL; it writes it for you. You'll understand SQLAlchemy far better if you've worked through Databases with sqlite3 and MySQL and PostgreSQL from Python. Install it on your own machine with pip install sqlalchemy (in the editors here, it loads automatically).

The engine and plain SQL

Everything starts with an engine, created once per database. You describe the database with a URL of the form dialect+driver://user:password@host/dbname:

Python
from sqlalchemy import create_engine

engine = create_engine("sqlite:///shop.db")                          # a file
engine = create_engine("sqlite://")                                  # in memory
engine = create_engine("postgresql+psycopg://app:secret@localhost/shop")
engine = create_engine("mysql+pymysql://app:secret@localhost/shop?charset=utf8mb4")

The engine doesn't connect right away. It holds a connection pool and opens connections when you first need them. You then run statements on a connection. The text() function wraps raw SQL, and values go in with :name placeholders, which SQLAlchemy translates into whatever style the driver underneath expects (?, %s, โ€ฆ):

Python Engine, connections and text()

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.

There are two ways to manage transactions with a connection:

  • engine.begin() opens a connection and a transaction together. It commits when the block ends and rolls back if an exception escapes. This is the safest default.
  • engine.connect() gives you a connection; you call conn.commit() whenever you want to save. Leaving the block without committing rolls back.

A result can be read in several shapes: loop over rows (which act like named tuples), call .scalar() for a single value, .all() for a list, or .mappings() for dictionary-like rows.

SQLAlchemy Core: SQL as Python objects

Raw SQL strings work, but SQLAlchemy can also build statements from Python objects. First you describe tables with Table objects collected in a MetaData. Then functions like select(), insert(), update() and delete() produce statements that you can combine, reuse and inspect:

Python Core: tables and statement builders

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.

Printing a statement shows the SQL SQLAlchemy generates, with placeholders such as :price_1 where your values go. The values are always sent as parameters, so building queries this way is safe from SQL injection. And because statements are objects, adding a filter only when it's needed (like cheaper_than() does) needs no string gluing.

Core is a great fit for reports, data pipelines and bulk work, where you think in rows and tables. When your application thinks in objects (a customer, an order, a book with its author), the ORM is usually the better tool.

Defining ORM models

In the ORM, each table is a class. In SQLAlchemy 2.0 you write the classes with type hints:

  • A base class inherits from DeclarativeBase; all models inherit from it.
  • __tablename__ names the table.
  • Mapped[int], Mapped[str], โ€ฆ declare columns. Mapped[str | None] makes a column nullable; plain Mapped[str] means NOT NULL.
  • mapped_column(...) adds details such as the primary key, a length, a default, uniqueness or a foreign key.

The objects you create are normal Python objects until you add them to a session. The session tracks every object you load or add, and on commit() it writes all pending changes in one transaction. This pattern is called the unit of work:

Python ORM models and the session

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.

Notice what you didn't write: no INSERT, no UPDATE, no column lists. Changing book.price marked the object as dirty, and the next commit() turned that into an UPDATE statement. The last line shows the identity map: within one session, each database row is represented by exactly one Python object, so loading the same row twice returns the same object.

Querying looks just like Core: select(Book) selects whole objects, and session.scalars(...) returns those objects instead of rows. session.get(Model, id) is the quick way to fetch by primary key; it returns None when there's no such row.

One session per unit of work. In a web app, create a session for each request and close it at the end (with Session(engine) as session: does that). Don't share one session between threads or keep it alive for the whole program.

Relationships

Real data is connected: an author has many books, and every book has one author. You declare the database link with a ForeignKey column, and the Python link with relationship(). With back_populates on both sides, SQLAlchemy keeps the two directions in sync:

Python One-to-many relationships

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.

A few things to notice:

  • mira.books.append(...) set each book's author (and, at commit, its author_id) automatically.
  • Adding the authors was enough: the session followed the relationship and saved their books as well.
  • .join(Author.books) joins along the relationship, so you don't repeat the ON condition.
  • cascade="all, delete-orphan" means a book can't exist without its author: deleting the author deletes the books. Without it, SQLAlchemy would try to set their author_id to NULL instead.
  • .one() returns exactly one object and raises an error if there are zero or several matches. Use .first() or .one_or_none() when "not found" is a normal outcome.

For many-to-many links (books and tags, students and courses), you add an association table and pass it as secondary= to relationship(). The idea is the same.

The N+1 query problem

Relationships load lazily by default: author.books runs a query the first time you touch it. That's convenient, but in a loop it becomes a trap. Listing 100 authors with their books runs 1 query for the authors plus 100 more, one per author. This is the famous N+1 problem, and it's the most common reason ORM code is slow.

You can watch it happen by counting the statements SQLAlchemy sends, using its event system. Then fix it with selectinload(), which loads all the books for all the authors in one extra query:

Python Spotting and fixing N+1 queries

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.

Six queries became two, and with 1,000 authors it would be 1,001 versus two. The other common option, joinedload(), fetches parents and children in a single query with a JOIN; it suits many-to-one links (each book's author). While developing, create_engine(url, echo=True) logs every statement SQLAlchemy sends, which is the easiest way to notice an N+1 pattern.

Changing the schema: migrations with Alembic

Base.metadata.create_all() creates tables that don't exist yet, but it never changes existing ones. Once a real database holds data, you can't drop and recreate tables every time a model gains a column. Instead you use migrations: small, versioned scripts that change the schema step by step, stored in Git with your code. SQLAlchemy's companion tool for this is Alembic:

Terminal
pip install alembic
alembic init migrations                  # creates alembic.ini and a migrations/ folder
# in migrations/env.py, set: target_metadata = Base.metadata
alembic revision --autogenerate -m "add year to books"
alembic upgrade head                     # apply every migration not yet applied
alembic downgrade -1                     # undo the most recent one

--autogenerate compares your models with the database and writes a script like this one, which you should always read and adjust before running:

Python
"""add year to books"""
from alembic import op
import sqlalchemy as sa

revision = "3f2a9c1d7b10"
down_revision = "a81c55e2f004"

def upgrade() -> None:
    op.add_column("books", sa.Column("year", sa.Integer(), nullable=True))
    op.create_index("ix_books_year", "books", ["year"])

def downgrade() -> None:
    op.drop_index("ix_books_year", table_name="books")
    op.drop_column("books", "year")

Alembic records the current version in a small table (alembic_version) in your database, so every environment (your laptop, the test server, production) can be brought to the same schema with alembic upgrade head.

When to use the ORM, Core or plain SQL

Use When
ORM Application code built around objects and relationships: web apps, APIs, business logic
Core Reports, bulk inserts and updates, data pipelines, dynamic queries over tables
text() / plain SQL A complex query that is clearer as SQL, or a database-specific feature

You can mix all three in one project, even in one session (session.execute(text(...)) works). Other Python ORMs exist too: Django's ORM comes built into Django (see Web Development with Flask and Django), and SQLModel combines SQLAlchemy models with Pydantic validation for FastAPI apps. What you learned here (sessions, lazy loading, N+1, migrations) applies to all of them.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What happens to a change like book.price = 9.99 on an object loaded through a session?

    The session tracks loaded objects (the unit of work). Changed attributes become an UPDATE when the session flushes, which commit() does.
  2. 2. In a SQLAlchemy 2.0 model, which declaration creates a nullable integer column?

    Mapped[int | None] (or Optional[int]) makes the column nullable. Plain Mapped[int] means NOT NULL.
  3. 3. A page lists 50 authors and, for each, len(author.books). With default settings, how many queries run?

    One query loads the authors, then each lazy author.books access runs its own query, so 1 + 50 = 51. selectinload(Author.books) reduces this to 2.
  4. 4. What does with engine.begin() as conn: do?

    engine.begin() is the "begin once" style. engine.connect() is the "commit as you go" style where you call conn.commit() yourself.
  5. 5. Your model gained a new column, but the production table already exists with data. What should you do?

    create_all() only creates missing tables and never alters existing ones. Migrations change the schema safely and keep every environment in step.

Summary

  • An engine (create_engine(url)) represents one database and owns a connection pool; URLs name the dialect and driver, like postgresql+psycopg://.
  • text() runs raw SQL with :name parameters; engine.begin() commits automatically, engine.connect() needs conn.commit().
  • Core builds SQL from Table objects with select(), insert(), update() and delete(); printing a statement shows its SQL.
  • ORM models subclass a DeclarativeBase and declare columns with Mapped[...] and mapped_column(); Mapped[X | None] is nullable.
  • A session tracks objects and writes all changes on commit(); query with session.scalars(select(Model)) and session.get(Model, id).
  • relationship(back_populates=...) links models; watch for N+1 queries and fix them with selectinload() or joinedload().
  • Change existing schemas with Alembic migrations, never by recreating tables.

Next: Document Databases with MongoDB, where data is stored as flexible JSON-like documents instead of rows.