Almost every real application needs to store data somewhere — users, orders, blog posts, sensor readings, you name it. Sooner or later, your Python code needs to talk to a database. You could write raw SQL strings by hand, open connections yourself, and turn rows into Python objects one by one. But that gets repetitive, error-prone, and hard to maintain very quickly.

SQLAlchemy solves that problem. It’s the most widely used database toolkit for Python, and it powers everything from small scripts to large production systems. This guide explains what SQLAlchemy is and why it’s worth learning, then walks you step by step through connecting to a database, defining models, working with data, building relationships and queries, managing transactions, and handling schema changes with Alembic.

All examples use SQLAlchemy 2.0 style, the modern API that every new project should use.

What Is SQLAlchemy?

SQLAlchemy is a free, open-source Python library for working with relational databases such as SQLite, PostgreSQL, MySQL/MariaDB, Microsoft SQL Server, and Oracle. It has two main layers:

  • SQLAlchemy Core — a Python way of writing SQL. You build queries with Python objects and functions instead of strings, and SQLAlchemy turns them into the correct SQL for your database.
  • SQLAlchemy ORM (Object Relational Mapper) — built on top of Core. It maps database tables to Python classes and rows to objects, so you can work with a Book object instead of a tuple of values.

You can use either layer, or both in the same project. Most applications use the ORM for everyday work and drop down to Core (or even raw SQL) when they need to.

Key Terms

You’ll see these words throughout the guide:

  • Engine — the starting point. It knows how to connect to your database and manages a pool of connections.
  • Dialect — the part of SQLAlchemy that speaks a specific database’s flavor of SQL. You don’t use it directly; the engine picks it from your connection URL.
  • Model — a Python class that represents a table.
  • Session — your workspace for ORM operations. It tracks the objects you load and change, and saves those changes to the database when you commit.

Why Use SQLAlchemy? The Benefits

  • Write Python, not SQL strings. Queries are built from Python objects, so your editor can autocomplete column names and catch typos before you run anything.
  • Switch databases easily. Develop with SQLite on your laptop and run PostgreSQL in production — usually by changing only the connection URL.
  • Built-in protection against SQL injection. SQLAlchemy sends values as bound parameters instead of pasting them into the query, which blocks one of the most common web attacks. (Learn more in SQL Injection: How It Works and How to Prevent It.)
  • Less repetitive code. The ORM handles turning rows into objects, tracking changes, and writing the INSERT, UPDATE, and DELETE statements for you.
  • You stay in control. Unlike some ORMs, SQLAlchemy never hides SQL from you. You can see every query it runs, and write raw SQL whenever you want.
  • It’s everywhere. Flask, FastAPI, pandas, Alembic, Apache Airflow, and many other tools use SQLAlchemy, so what you learn here applies in lots of places.

Prerequisites

To follow along, you’ll need:

We’ll use SQLite, which is built into Python, so you don’t need to install a database server.

Step 1: Install SQLAlchemy

Create a project folder and a virtual environment, then install SQLAlchemy:

mkdir sqlalchemy-library
cd sqlalchemy-library
python3 -m venv .venv
source .venv/bin/activate      # On Windows: .venv\Scripts\activate
pip install sqlalchemy

Using uv? Run uv init and uv add sqlalchemy instead. See Python uv: A Faster Way to Manage Projects and Dependencies.

Check the installed version — it should be 2.0 or newer:

python -c "import sqlalchemy; print(sqlalchemy.__version__)"

SQLite works out of the box. For other databases, you also need a driver:

  • PostgreSQL: pip install "psycopg[binary]"
  • MySQL/MariaDB: pip install pymysql

Step 2: Connect to a Database with an Engine

Create a file called library.py. We’ll add to it throughout this guide. Start by creating an engine:

from sqlalchemy import create_engine

engine = create_engine("sqlite:///library.db", echo=True)

The string passed to create_engine() is a connection URL. It tells SQLAlchemy which database to use and how to reach it. Here are the most common formats:

sqlite:///library.db                                         # SQLite file in the current folder
sqlite:///:memory:                                           # temporary in-memory SQLite database
postgresql+psycopg://user:password@localhost:5432/library    # PostgreSQL
mysql+pymysql://user:password@localhost:3306/library         # MySQL or MariaDB

echo=True prints every SQL statement SQLAlchemy runs. Keep it on while learning — it’s the best way to see what’s happening behind the scenes — and turn it off in production.

Keep credentials out of your code. For real databases, store the URL in an environment variable or a .env file and read it with os.environ["DATABASE_URL"].

Want to practice with MySQL instead of SQLite? MySQL with Docker shows you how to run one in a container in a few minutes.

Step 3: Run Raw SQL with Core

Before we get to the ORM, here’s how to run plain SQL through SQLAlchemy. This is useful for quick queries, scripts, and database features the ORM doesn’t cover:

from sqlalchemy import text

with engine.connect() as conn:
    result = conn.execute(
        text("SELECT :greeting AS message"),
        {"greeting": "Hello, SQLAlchemy!"},
    )
    print(result.scalar())

Notice the :greeting placeholder. Always pass values as parameters like this — never build SQL with f-strings or string concatenation. SQLAlchemy sends the value separately from the query, so even malicious input can’t change what the query does.

The with block automatically returns the connection to the pool when you’re done.

Step 4: Define Your Models

Now for the ORM. We’ll model a small library with two tables: authors and books. Each author can write many books, and each book belongs to one author — a classic one-to-many relationship. (If you’re new to planning tables like this, read Database Design: A Beginner’s Guide to Planning It Right.)

Add the models to library.py:

from datetime import datetime

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


class Base(DeclarativeBase):
    pass


class Author(Base):
    __tablename__ = "authors"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(100), unique=True)
    country: Mapped[str | None] = mapped_column(String(50))

    books: Mapped[list["Book"]] = relationship(
        back_populates="author", cascade="all, delete-orphan"
    )

    def __repr__(self):
        return f"Author(id={self.id}, name={self.name!r})"


class Book(Base):
    __tablename__ = "books"

    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column(String(200))
    year: Mapped[int]
    pages: Mapped[int | None]
    created_at: Mapped[datetime] = mapped_column(server_default=func.now())
    author_id: Mapped[int] = mapped_column(ForeignKey("authors.id"))

    author: Mapped["Author"] = relationship(back_populates="books")

    def __repr__(self):
        return f"Book(id={self.id}, title={self.title!r})"

Let’s break this down:

  • Base is the parent class for all your models. SQLAlchemy uses it to keep track of every table you define.
  • __tablename__ sets the table’s name in the database.
  • Mapped[...] declares a column and its Python type. SQLAlchemy picks a matching SQL type automatically: int becomes INTEGER, str becomes VARCHAR, datetime becomes DATETIME, and so on.
  • str | None (or Optional[str]) makes the column nullable. Without None, the column is NOT NULL.
  • mapped_column() adds details such as primary_key=True, unique=True, a length like String(100), or a default value.
  • ForeignKey("authors.id") links each book to a row in the authors table.
  • relationship() lets you move between related objects in Python: author.books gives you a list of books, and book.author gives you the author. back_populates keeps both sides in sync.
  • cascade="all, delete-orphan" means that when you delete an author, their books are deleted too.

Step 5: Create the Tables

With the models defined, one line creates the tables in the database:

Base.metadata.create_all(engine)

Because echo=True is on, you’ll see the CREATE TABLE statements SQLAlchemy generates. create_all() is safe to run more than once — it only creates tables that don’t exist yet.

Note: create_all() never changes a table that already exists. If you add a column to a model later, use Alembic (covered in Step 11) to update the database.

Step 6: Create Records

To work with ORM objects, you open a Session. Create objects like any other Python class, add them to the session, and commit to save them:

from sqlalchemy.orm import Session

with Session(engine) as session:
    orwell = Author(name="George Orwell", country="United Kingdom")
    orwell.books = [
        Book(title="1984", year=1949, pages=328),
        Book(title="Animal Farm", year=1945, pages=112),
    ]

    austen = Author(name="Jane Austen", country="United Kingdom")
    austen.books = [Book(title="Pride and Prejudice", year=1813, pages=432)]

    marquez = Author(name="Gabriel García Márquez", country="Colombia")
    marquez.books = [
        Book(title="One Hundred Years of Solitude", year=1967, pages=417),
        Book(title="Love in the Time of Cholera", year=1985, pages=348),
    ]

    session.add_all([orwell, austen, marquez])
    session.commit()

You never set author_id yourself. Because the books are attached to orwell.books, SQLAlchemy inserts the author first, gets its new ID, and fills in author_id for each book automatically.

Until you call commit(), nothing is permanently saved. If an error happens before then, the with block closes the session and the changes are discarded.

Step 7: Read Records

In SQLAlchemy 2.0, every query starts with select(). You build the query, then run it with the session:

from sqlalchemy import select

with Session(engine) as session:
    # All books published after 1900, oldest first
    stmt = select(Book).where(Book.year > 1900).order_by(Book.year)
    for book in session.scalars(stmt):
        print(book.title, book.year)

    # A single row by primary key
    book = session.get(Book, 1)
    print(book)

    # Exactly one result (raises an error if there are zero or several)
    orwell = session.scalars(select(Author).where(Author.name == "George Orwell")).one()
    print([b.title for b in orwell.books])

There are a few ways to get results from a query:

  • session.scalars(stmt) — iterate over ORM objects (Book, Author, and so on).
  • .all() — get all results as a list.
  • .first() — get the first result, or None.
  • .one() — get exactly one result, or raise an error.
  • .one_or_none() — get one result, or None if there isn’t one.
  • session.get(Model, id) — the fastest way to load a row by its primary key.

Filtering, Sorting, and Limiting

where() accepts Python expressions that SQLAlchemy turns into SQL. Here are the operators you’ll use most:

from sqlalchemy import and_, or_

select(Book).where(Book.title == "1984")                     # equal
select(Book).where(Book.year != 1949)                        # not equal
select(Book).where(Book.title.like("%Time%"))                # pattern match
select(Book).where(Book.title.ilike("%time%"))               # case-insensitive match
select(Book).where(Book.year.between(1940, 1970))            # range
select(Book).where(Book.id.in_([1, 3, 5]))                   # in a list
select(Book).where(Book.pages.is_(None))                     # IS NULL
select(Book).where(and_(Book.year > 1900, Book.pages < 400)) # AND
select(Book).where(or_(Book.year < 1900, Book.pages > 400))  # OR
select(Book).order_by(Book.pages.desc()).limit(3)            # top 3 longest

Calling .where() several times also combines the conditions with AND. Each of these is just a query object — pass it to session.scalars() to run it.

Step 8: Update and Delete Records

To update a record, load it, change its attributes, and commit. The session notices the change and writes the UPDATE for you:

with Session(engine) as session:
    book = session.scalars(select(Book).where(Book.title == "1984")).one()
    book.pages = 336
    session.commit()

To update many rows at once without loading them into Python, use an update() statement:

from sqlalchemy import update

with Session(engine) as session:
    session.execute(
        update(Book).where(Book.year < 1900).values(pages=Book.pages + 10)
    )
    session.commit()

To delete a record, pass it to session.delete():

with Session(engine) as session:
    book = session.scalars(select(Book).where(Book.title == "Animal Farm")).one()
    session.delete(book)
    session.commit()

There’s also a delete() statement for removing many rows at once, for example, session.execute(delete(Book).where(Book.year < 1800)).

Step 9: Relationships, Joins, and Aggregates

Because we defined a relationship(), you can move between related objects with plain attributes — author.books and book.author. When you need to filter or combine data from several tables in a single query, use a join. (For a refresher on how joins work in SQL, see SQL JOINs Explained.)

with Session(engine) as session:
    stmt = (
        select(Book.title, Author.name)
        .join(Book.author)
        .where(Author.country == "Colombia")
    )
    for title, author in session.execute(stmt):
        print(f"{title} by {author}")

Notice that this query selects columns rather than whole objects, so it uses session.execute() and returns rows, which you can unpack like tuples.

For aggregates like COUNT, SUM, and AVG, use func with group_by():

from sqlalchemy import func

stmt = (
    select(
        Author.name,
        func.count(Book.id).label("book_count"),
        func.avg(Book.pages).label("avg_pages"),
    )
    .join(Author.books)
    .group_by(Author.name)
    .order_by(func.count(Book.id).desc())
)

with Session(engine) as session:
    for row in session.execute(stmt):
        print(row.name, row.book_count, round(row.avg_pages))

.label() gives each calculated column a name, so you can access it as row.book_count.

Avoid the N+1 Query Problem

By default, relationships are lazy-loaded: author.books isn’t fetched until you access it. That’s convenient, but it can hurt performance. Look at this loop:

with Session(engine) as session:
    for author in session.scalars(select(Author)):
        print(author.name, len(author.books))

This runs one query to load the authors, then one more query per author to load their books. With 1,000 authors, that’s 1,001 queries — the infamous N+1 problem. With echo=True on, you’ll see it happen.

The fix is eager loading. selectinload() loads all the related books in a single extra query:

from sqlalchemy.orm import selectinload

with Session(engine) as session:
    stmt = select(Author).options(selectinload(Author.books))
    for author in session.scalars(stmt):
        print(author.name, [b.title for b in author.books])

Now it’s two queries total, no matter how many authors you have. For many-to-one relationships like book.author, joinedload() is often a better fit — it fetches the author in the same query with a JOIN. Slow pages are often caused by N+1 queries, so this is worth checking first. For more on query speed in general, read Why Your SQL Query Is Slow (And How to Fix It).

Step 10: Transactions and Error Handling

A transaction groups several changes so they either all succeed or all fail together. The session always works inside a transaction: commit() saves everything since the last commit, and rollback() throws it all away.

The cleanest way to manage a transaction is with session.begin(). It commits automatically at the end of the block, or rolls back if an exception is raised:

with Session(engine) as session:
    with session.begin():
        allende = Author(name="Isabel Allende", country="Chile")
        allende.books.append(Book(title="The House of the Spirits", year=1982, pages=448))
        session.add(allende)
    # Committed here, or rolled back if anything inside raised an error

When a database rule is broken — for example, inserting a duplicate value into a unique column — SQLAlchemy raises an IntegrityError. Catch it, and roll back so the session can be used again:

from sqlalchemy.exc import IntegrityError

with Session(engine) as session:
    try:
        session.add(Author(name="George Orwell"))
        session.commit()
    except IntegrityError:
        session.rollback()
        print("That author already exists.")

Step 11: Manage Schema Changes with Alembic

Your models will change over time — you’ll add columns, rename things, and create new tables. Since create_all() can’t modify existing tables, you need migrations: small, versioned scripts that update the database step by step. Alembic, made by the same author as SQLAlchemy, is the standard tool for this.

At this point, it’s a good idea to move Base and your models into their own module, such as models.py, so Alembic can import them. Then install Alembic and set up a migrations folder:

pip install alembic
alembic init migrations

Open alembic.ini and set your database URL:

sqlalchemy.url = sqlite:///library.db

Then open migrations/env.py and point Alembic at your models:

from models import Base

target_metadata = Base.metadata

Now, whenever you change a model — say you add isbn: Mapped[str | None] = mapped_column(String(13)) to Book — let Alembic compare your models with the database and write the migration for you:

alembic revision --autogenerate -m "add isbn to books"
alembic upgrade head

Always open the generated file in migrations/versions/ and review it before running upgrade — autogenerate is very good, but it can’t detect everything (for example, a renamed column looks like a dropped column plus a new one). If something goes wrong, undo the last migration with:

alembic downgrade -1

Commit the migrations/ folder to version control, so every environment — your laptop, a teammate’s laptop, and production — can apply exactly the same changes.

Using SQLAlchemy with Other Tools

Flask

Flask-SQLAlchemy is a thin wrapper that connects SQLAlchemy to a Flask app. It manages the engine and session for you, but the models and queries are the same SQLAlchemy you just learned — db.select(), db.session.execute(), and db.session.commit() work exactly like select() and the session above. See Python Flask: A Complete Guide to Building and Deploying Web Applications for a full example.

pandas

pandas can read query results straight into a DataFrame using your SQLAlchemy engine — perfect for analysis and reports:

import pandas as pd

df = pd.read_sql(select(Book.title, Book.year, Book.pages), engine)
print(df.describe())

From there, you can clean and analyze the data as usual. (See Data Cleaning with Python Pandas: From Raw Data to Analysis.)

SQLAlchemy Best Practices

  • Create one engine per application and reuse it. The engine manages a connection pool; creating a new one for every request wastes resources.
  • Keep sessions short. Open a session for a unit of work — one web request, one job, one script task — and close it when you’re done. The with Session(engine) as session: pattern does this for you.
  • Use the 2.0 style. Write queries with select() and session.scalars(). Older tutorials use session.query(...), which still works but is now considered legacy.
  • Never build SQL with f-strings. Use the ORM, Core expressions, or text() with bound parameters.
  • Watch for N+1 queries. Turn on echo=True while developing and use selectinload() or joinedload() when you loop over relationships.
  • Use Alembic from the start for any database that holds data you care about.
  • Add indexes to columns you filter or join on often, with mapped_column(index=True).
  • Keep your connection URL in an environment variable, never in your code or repository.

Troubleshooting Common SQLAlchemy Errors

sqlite3.OperationalError: no such table. The table hasn’t been created yet. Make sure Base.metadata.create_all(engine) runs after your models are defined (or imported), and that you’re connecting to the same database file. A relative path like sqlite:///library.db depends on the folder you run the script from.

IntegrityError: UNIQUE constraint failed. You inserted a value that already exists in a unique column. In this guide, running the Step 6 script twice causes this, because the author names are unique. Delete library.db to start over, or check whether the row exists before inserting it.

PendingRollbackError. An earlier error left the session’s transaction in a failed state. Call session.rollback() after catching an exception before using the session again.

DetachedInstanceError. You accessed a lazy-loaded relationship (like author.books) after the session was closed. Access the data while the session is still open, or load it up front with selectinload().

NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:postgres. The URL scheme must be postgresql:// (or postgresql+psycopg://), not postgres://. Some hosting providers give you the older form, so replace it before calling create_engine().

ModuleNotFoundError: No module named 'psycopg' (or pymysql). SQLAlchemy doesn’t include database drivers. Install the driver that matches your connection URL.

MultipleResultsFound or NoResultFound. You called .one(), but the query returned several rows or none. Use .first() or .one_or_none() if zero or many results are possible.

Conclusion

SQLAlchemy gives you the best of both worlds: the productivity of working with Python objects, and the full power of SQL whenever you need it. In this guide, you connected to a database with an engine, ran safe raw SQL, defined models and relationships, created, read, updated, and deleted records, wrote joins and aggregates, fixed the N+1 problem, handled transactions and errors, and set up Alembic for schema changes.

The best next step is to use it in a real project. Take a script that writes to CSV files or builds SQL strings by hand, and rewrite it with SQLAlchemy models. Once you’ve seen how much cleaner the code becomes, you’ll want to use it for every project that touches a database.