SQLAlchemy Tutorial: Databases in Python

SQLAlchemy is the most widely used database toolkit for Python. It has two layers: Core, a Pythonic way to build SQL statements, and the ORM, which maps rows to Python objects. Version 2.0 unified them around one query style, select(), and added typed model definitions (SQLAlchemy: unified tutorial). This tutorial builds a small customers-and-orders database in memory, so every step runs as shown with nothing to install except sqlalchemy.

Models: tables as typed classes

from sqlalchemy import ForeignKey, String, create_engine, event, func, select
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column, relationship, selectinload

class Base(DeclarativeBase):
    pass

class Customer(Base):
    __tablename__ = "customers"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(100))
    city: Mapped[str] = mapped_column(String(50))
    orders: Mapped[list["Order"]] = relationship(back_populates="customer")

class Order(Base):
    __tablename__ = "orders"
    id: Mapped[int] = mapped_column(primary_key=True)
    amount: Mapped[float]
    customer_id: Mapped[int] = mapped_column(ForeignKey("customers.id"))
    customer: Mapped[Customer] = relationship(back_populates="orders")

engine = create_engine("sqlite://")          # in-memory SQLite; a real app uses its database URL
Base.metadata.create_all(engine)

Each class is a table and each Mapped[...] attribute a column; the type annotation also sets the column type and whether it can be null (SQLAlchemy: ORM quick start). relationship links the two classes in both directions, so customer.orders and order.customer are ordinary attributes. The engine manages connections; for PostgreSQL or MySQL you would pass a URL such as postgresql+psycopg://user:password@host/dbname, read from configuration rather than written in code.

Adding rows with a Session

with Session(engine) as session:
    asha = Customer(name="Asha", city="Hyderabad", orders=[Order(amount=1200), Order(amount=450)])
    ravi = Customer(name="Ravi", city="Pune", orders=[Order(amount=900)])
    session.add_all([asha, ravi])
    session.commit()
    print(asha.id, [o.id for o in asha.orders])

1 [1, 2]

A Session is a unit of work: it collects changes to objects and writes them in one transaction when you call commit() (SQLAlchemy: session basics). Adding a customer also added their orders, because they are attached through the relationship, and the database assigned the IDs. Using the session as a context manager guarantees it is closed.

Querying with select()

with Session(engine) as session:
    stmt = select(Customer).where(Customer.city == "Hyderabad")
    print(stmt)
    print([c.name for c in session.scalars(stmt)])

SELECT customers.id, customers.name, customers.city 
FROM customers 
WHERE customers.city = :city_1
['Asha']

Printing a statement shows the SQL it will run, which is the quickest way to learn what SQLAlchemy does. Note :city_1: the value is sent as a bound parameter, never pasted into the SQL text, which is what protects against SQL injection. session.scalars() returns the objects themselves; session.execute() returns rows, which suits queries that select columns rather than whole objects.

Joins and aggregates

with Session(engine) as session:
    per_customer = (
        select(Customer.name, func.count(Order.id), func.sum(Order.amount))
        .join(Customer.orders)
        .group_by(Customer.name)
        .order_by(Customer.name)
    )
    print(session.execute(per_customer).all())

[('Asha', 2, 1650.0), ('Ravi', 1, 900.0)]

The query reads like the SQL it produces: join along the relationship, group, aggregate with func.count and func.sum, sort. Anything SQL can express, select() can build, and when a query is clearer as raw SQL, sqlalchemy.text() runs it with bound parameters.

The N+1 query problem

Relationships load lazily by default: customer.orders is fetched only when you access it. That is convenient, and it becomes slow when you loop over many objects. One query loads the customers, then one more runs for each customer's orders. Counting the queries sent to the database shows it:

queries = {"n": 0}

@event.listens_for(engine, "before_cursor_execute")
def count_query(*args):
    queries["n"] += 1

with Session(engine) as session:
    for customer in session.scalars(select(Customer)):
        len(customer.orders)                          # lazy load: one query per customer
    print("lazy loading:", queries["n"], "queries")

    queries["n"] = 0
    session.expire_all()
    for customer in session.scalars(select(Customer).options(selectinload(Customer.orders))):
        len(customer.orders)
    print("selectinload:", queries["n"], "queries")

lazy loading: 3 queries
selectinload: 2 queries

With two customers the difference is one query; with a thousand customers it is 1,001 queries against 2. selectinload fetches the related orders for all loaded customers in a single extra query (SQLAlchemy: relationship loading). Whenever code loops over objects and touches a relationship, choose the loading strategy explicitly.

Transactions and errors

Everything inside a session is one transaction until commit(). If an exception occurs, call session.rollback(), or use with Session(engine) as session, session.begin():, which commits when the block succeeds and rolls back when it raises. Keep transactions short: open a session per unit of work, such as one web request or one batch, rather than one long-lived session for the whole program. In a web framework, a dependency that creates a session per request does exactly this; the FastAPI guide shows the dependency pattern.

Changing the schema: migrations

create_all creates missing tables but never alters existing ones. Real applications change their schema over time, adding columns and indexes, and use Alembic, SQLAlchemy's migration tool, which generates versioned migration scripts from model changes and applies them in order (Alembic documentation). Commit the migration scripts with the code, so every environment's database can be brought to the same version.

Organising database code in a real project

A small but durable layout keeps database code in three places. A db.py module creates the engine once, from a URL read from configuration, and provides a session factory with sessionmaker(engine). A models.py module holds the mapped classes. Functions that run queries, such as get_customer(session, customer_id) or orders_since(session, date), live together and take the session as an argument instead of creating their own. That last point matters most: the caller decides where a transaction begins and ends, tests can pass a session bound to a test database, and nothing opens connections behind your back. Create the engine once per process; creating one per request throws away its connection pool.

ORM or plain SQL?

The ORM pays off in applications with many related objects and create-read-update-delete logic: models document the schema, relationships remove join boilerplate, and the session batches writes. For analytics queries over large tables, writing SQL directly, through text() or Core, is often clearer, and moving the data into pandas with pandas.read_sql is common. The two approaches mix freely in one project, since both run on the same engine.

Database access is usually one layer of an API; building REST APIs with Python covers the layer above it. If your SQL is rusty, the SQL interview questions double as a practical refresher on joins, grouping and window functions. Connecting Python to databases, including SQLite basics and CRUD operations, is covered in the database connectivity module of the Python Programming course.

Sources

• SQLAlchemy Unified Tutorial — SQLAlchemy documentation
• ORM Quick Start — SQLAlchemy documentation
• Relationship Loading Techniques — SQLAlchemy documentation
• Session Basics — SQLAlchemy documentation
• Alembic documentation — Alembic documentation