SQLAlchemy ORM Basics

In SQLite Databases you wrote SQL by hand and turned rows into Python values yourself. That is fine for a small script, but a real application may have dozens of tables, and the same "row to object" code gets written again and again. Most Python web projects therefore use an ORM (Object Relational Mapper): a library that maps database tables to Python classes, so a row becomes an object and a column becomes an attribute.

SQLAlchemy is the most widely used database toolkit for Python. Flask projects and FastAPI projects commonly use it, and Django has its own ORM built on the same ideas. This lesson teaches the modern SQLAlchemy 2 style (the current release line is 2.1). SQLAlchemy is a third-party package, so the examples run on your computer, not in the browser.

The job an ORM does for you

First look at what you do without an ORM. Each query needs SQL text, placeholders, and a line that turns each row into an object:

import sqlite3
from dataclasses import dataclass

@dataclass
class Customer:
    id: int
    name: str
    city: str

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, city TEXT)")
conn.executemany(
    "INSERT INTO customers (name, city) VALUES (?, ?)",
    [("Priya", "Pune"), ("Farhan", "Lucknow"), ("Isha", "Pune")],
)

def customers_in(city: str) -> list[Customer]:
    rows = conn.execute(
        "SELECT id, name, city FROM customers WHERE city = ? ORDER BY name", (city,)
    )
    return [Customer(*row) for row in rows]      # rows -> objects, by hand

for customer in customers_in("Pune"):
    print(customer)

Customer(id=3, name='Isha', city='Pune')
Customer(id=1, name='Priya', city='Pune')

An ORM writes the SQL, fills the placeholders safely and builds the objects. It also follows links between tables (a customer's orders) and works with SQLite, PostgreSQL, MySQL and others with the same Python code. The price: one more library to learn, and you must still understand the SQL it produces.

Install and define models

Install it in your project's virtual environment with python -m pip install sqlalchemy (or uv add sqlalchemy). Then describe your tables as classes, called models. Save this as models.py:

from sqlalchemy import ForeignKey, String, create_engine
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship


class Base(DeclarativeBase):
    pass


class Customer(Base):
    __tablename__ = "customers"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(80))
    city: Mapped[str] = mapped_column(String(40))
    orders: Mapped[list["Order"]] = relationship(back_populates="customer")

    def __repr__(self) -> str:
        return f"Customer({self.name!r}, {self.city!r})"


class Order(Base):
    __tablename__ = "orders"

    id: Mapped[int] = mapped_column(primary_key=True)
    item: Mapped[str] = mapped_column(String(100))
    amount: Mapped[int]
    customer_id: Mapped[int] = mapped_column(ForeignKey("customers.id"))
    customer: Mapped[Customer] = relationship(back_populates="orders")


engine = create_engine("sqlite:///shop.db")
Base.metadata.create_all(engine)

Read it piece by piece:

• Every model inherits from one Base class made from DeclarativeBase. __tablename__ names the table.
• Mapped[int] is a type hint that SQLAlchemy reads to choose the column type. Mapped[str] means NOT NULL; Mapped[str | None] would allow NULL.
• mapped_column() adds details: primary_key=True, a length with String(80), or a ForeignKey. A plain amount: Mapped[int] needs no call at all.
• relationship() is not a column. It gives you customer.orders (a list) and order.customer (one object), and back_populates keeps both sides in step.
• The engine knows how to reach the database. The URL sqlite:///shop.db means a SQLite file; a PostgreSQL URL looks like postgresql+psycopg://user:password@host/dbname.
• create_all() runs CREATE TABLE for any table that does not exist yet.

Running python models.py creates shop.db with this SQL (SQLAlchemy can show its SQL; see the tip below):

CREATE TABLE orders (
	id INTEGER NOT NULL,
	item VARCHAR(100) NOT NULL,
	amount INTEGER NOT NULL,
	customer_id INTEGER NOT NULL,
	PRIMARY KEY (id),
	FOREIGN KEY(customer_id) REFERENCES customers (id)
)

Sessions: adding and querying

A session is your conversation with the database. You add objects to it, query through it, and commit() to save everything as one transaction. Use it in a with block so it is always closed. Queries are built with select(), and session.scalars() runs a query and gives you the objects:

from sqlalchemy import func, select
from sqlalchemy.orm import Session

from models import Customer, Order, engine

with Session(engine) as session:
    priya = Customer(name="Priya", city="Pune")
    farhan = Customer(name="Farhan", city="Lucknow")
    priya.orders.append(Order(item="Cricket bat", amount=1299))
    priya.orders.append(Order(item="Water bottle", amount=249))
    farhan.orders.append(Order(item="School bag", amount=899))
    session.add_all([priya, farhan])
    session.commit()

with Session(engine) as session:
    stmt = select(Customer).where(Customer.city == "Pune")
    for customer in session.scalars(stmt):
        print(customer, [order.item for order in customer.orders])

    big = select(Order).where(Order.amount > 500).order_by(Order.amount.desc())
    for order in session.scalars(big):
        print(order.item, order.amount, order.customer.name)

    totals = (
        select(Customer.name, func.sum(Order.amount).label("spent"))
        .join(Customer.orders)
        .group_by(Customer.name)
        .order_by(Customer.name)
    )
    for name, spent in session.execute(totals):
        print(f"{name}: ₹{spent:,}")

Customer('Priya', 'Pune') ['Cricket bat', 'Water bottle']
Cricket bat 1299 Priya
School bag 899 Farhan
Farhan: ₹899
Priya: ₹1,548

You never wrote an INSERT or a foreign key value: adding Priya also saved her two orders and filled in customer_id. Notice the two ways to run a query. session.scalars() is for queries that return whole objects; session.execute() returns rows of several columns, like (name, spent). func.sum produces SQL's SUM(); any SQL function is available through func.

Updating and deleting

To update, load an object, change its attributes and commit. The session notices the change and writes an UPDATE. To delete, pass the object to session.delete(). session.get() loads one object by its primary key, and .one() insists that a query returns exactly one result.

from sqlalchemy import func, select
from sqlalchemy.orm import Session

from models import Customer, Order, engine

with Session(engine) as session:
    farhan = session.scalars(select(Customer).where(Customer.name == "Farhan")).one()
    farhan.city = "Kochi"
    bottle = session.scalars(select(Order).where(Order.item == "Water bottle")).first()
    session.delete(bottle)
    session.commit()
    print(session.get(Customer, 2))
    print(session.scalar(select(func.count()).select_from(Order)))

Customer('Farhan', 'Kochi')
2

If an error happens before commit(), nothing is saved: leaving the with block without committing rolls the transaction back.

Changing tables later: migrations

create_all() only creates missing tables. It never adds a new column to an existing table. Real projects change their tables many times, so they use migrations: small, versioned scripts that change the database step by step, kept in Git with the code. For SQLAlchemy the standard tool is Alembic, written by the same authors. alembic revision --autogenerate -m "add phone to customers" compares your models with the database and drafts a script, which you review and apply with alembic upgrade head.

Common mistakes

• Forgetting commit(). Changes are only saved when you commit; closing the session without it discards them.
• Using an object after its session closed. Reading customer.orders then raises DetachedInstanceError, because SQLAlchemy loads related rows lazily, only when first used. Read what you need inside the with block.
• The "N+1 queries" problem. Looping over 100 customers and touching customer.orders runs 101 queries. Turn on echo=True to spot it, and load related rows in one go with options such as selectinload(Customer.orders).
• Expecting create_all() to change existing tables. Use Alembic migrations.
• Putting passwords in the engine URL in code. Read the URL from an environment variable instead (see Secure Coding Basics).

Key points

• An ORM maps tables to classes and rows to objects; SQLAlchemy is Python's most widely used one.
• Models inherit from a DeclarativeBase subclass and declare columns with Mapped[...] and mapped_column().
• relationship() links tables, so customer.orders and order.customer just work.
• Use with Session(engine) as session:, add objects, and commit() to save them as one transaction.
• Build queries with select(...).where(...).order_by(...); run them with session.scalars() for objects or session.execute() for rows.
• create_all() only creates missing tables; use Alembic migrations for later changes.

# in models.py, before create_all()
class Product(Base):
    __tablename__ = "products"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(100))
    price: Mapped[int]
    stock: Mapped[int]

from sqlalchemy import select
from sqlalchemy.orm import Session

from models import Product, engine

with Session(engine) as session:
    session.add_all([
        Product(name="Notebook", price=60, stock=40),
        Product(name="Geometry box", price=180, stock=3),
        Product(name="Fountain pen", price=450, stock=2),
    ])
    session.commit()

    low = select(Product).where(Product.stock < 5).order_by(Product.price)
    for product in session.scalars(low):
        print(product.name)
        product.price += 50
    session.commit()

Geometry box
Fountain pen