12.2. SQLAlchemy

Why Use SQLAlchemy?

SQLAlchemy is a popular Python library that helps you work with relational databases like PostgreSQL, MySQL, or SQLite using Python code instead of writing raw SQL everywhere.

You can still use SQL when you need it, but SQLAlchemy gives you:

A database connection manager
A way to define tables as Python classes
A way to write queries in Python expressions
A consistent API across different databases

Key idea: SQLAlchemy does not replace SQL. It wraps SQL in Python code, makes it safer and more convenient, and still lets you drop down to raw SQL when needed.

In this chapter, you will see how SQLAlchemy works at a high level and get a feel for its two main styles: Core and ORM. Later child chapters like Models, Database Sessions, Creating Records, and Queries will go deeper into each topic.

Installing SQLAlchemy

You install SQLAlchemy with pip. In real projects you will usually do this inside a virtual environment.

bash

pip install sqlalchemy

If you know you will use PostgreSQL, you also need a driver:

bash

pip install psycopg2-binary

For SQLite you do not need an extra driver. Python ships with one built in.

A minimal requirements.txt might look like this:

text

sqlalchemy==2.0.32
psycopg2-binary==2.9.9

You can then install everything:

bash

pip install -r requirements.txt

SQLAlchemy Core vs ORM

SQLAlchemy has two major layers:

Layer	What it is	When to use
SQLAlchemy Core	Low level API for building SQL expressions	Simple scripts, highly optimized SQL, migrations, reporting
SQLAlchemy ORM	Object Relational Mapper that maps classes	Web applications, domain models, working with objects

You can use one or both in the same project. The ORM actually uses Core under the hood.

SQLAlchemy Core in One Glance

Core focuses on tables and SQL expressions.

Example with SQLite in memory:

python

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, select, insert
# 1. Create an engine (DB connection factory)
engine = create_engine("sqlite:///:memory:", echo=True)
metadata = MetaData()
# 2. Define table structure
users_table = Table(
    "users",
    metadata,
    Column("id", Integer, primary_key=True),
    Column("name", String, nullable=False),
    Column("email", String, unique=True, nullable=False),
)
# 3. Create the table in the database
metadata.create_all(engine)
# 4. Insert data
with engine.connect() as conn:
    stmt = insert(users_table).values(name="Alice", email="alice@example.com")
    conn.execute(stmt)
    conn.commit()
# 5. Query data
with engine.connect() as conn:
    stmt = select(users_table).where(users_table.c.name == "Alice")
    result = conn.execute(stmt).fetchone()
    print(result)  # (1, 'Alice', 'alice@example.com')

Points to notice:

You work with Table objects and column objects.
You build SQL with Python functions like select() and insert().
You get back rows as tuples or row objects, not your own Python classes.

SQLAlchemy ORM in One Glance

The ORM lets you map tables to Python classes so you work with objects.

Same example using the ORM:

python

from sqlalchemy import create_engine
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session
from sqlalchemy import String
# 1. Engine
engine = create_engine("sqlite:///:memory:", echo=True)
# 2. Base class for models
class Base(DeclarativeBase):
    pass
# 3. Define a model class
class User(Base):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String, nullable=False)
    email: Mapped[str] = mapped_column(String, unique=True, nullable=False)
# 4. Create tables
Base.metadata.create_all(engine)
# 5. Use Session to work with objects
with Session(engine) as session:
    # Create
    alice = User(name="Alice", email="alice@example.com")
    session.add(alice)
    session.commit()
    # Read
    user = session.query(User).filter(User.name == "Alice").first()
    print(user.id, user.name, user.email)

Here you:

Define User as a Python class that represents a users table row.
Use a Session to add and query User objects.
Do not write SQL strings yourself, but SQLAlchemy still sends SQL to the database.

In this course, the later chapters under ORM and Database Integration will mainly use the ORM style, because it fits backend applications well.

Engines and Database URLs

At the center of SQLAlchemy is the Engine. The engine knows how to:

Connect to your database
Create connection pools
Send SQL statements
Return results

You create an engine once, then reuse it.

Database URLs

To create an engine you provide a database URL. The format is:

Database URL format:
dialect+driver://username:password@host:port/database_name

Examples:

Database	Example URL
SQLite file	`sqlite:///./app.db`
SQLite memory	`sqlite:///:memory:`
PostgreSQL	`postgresql+psycopg2://user:password@localhost:5432/mydb`
MySQL	`mysql+pymysql://user:password@localhost:3306/mydb`

Basic engine creation:

python

from sqlalchemy import create_engine
SQLALCHEMY_DATABASE_URL = "sqlite:///./app.db"
engine = create_engine(
    SQLALCHEMY_DATABASE_URL,
    echo=True,          # Log SQL statements to stdout, useful for learning
    future=True,        # Modern SQLAlchemy 2 style
)

For SQLite with multiple threads (for example in FastAPI), you often use:

python

engine = create_engine(
    "sqlite:///./app.db",
    connect_args={"check_same_thread": False},
)

We will revisit engines again when we talk about Connection Pooling later in this section of the course.

Defining Tables and Models

SQLAlchemy gives you two styles to define your database structure:

Table objects (Core)
Model classes (ORM)

In this chapter we only introduce both at a high level. The child chapter Models will go into definitions in detail.

Table Objects (Core)

You define tables using Table, Column, and MetaData:

python

from sqlalchemy import (
    Table, Column, Integer, String, MetaData, Boolean, DateTime
)
metadata = MetaData()
tasks_table = Table(
    "tasks",
    metadata,
    Column("id", Integer, primary_key=True),
    Column("title", String, nullable=False),
    Column("description", String),
    Column("completed", Boolean, nullable=False, default=False),
    Column("created_at", DateTime),
)

Later:

python

from sqlalchemy import create_engine
engine = create_engine("sqlite:///./tasks.db")
metadata.create_all(engine)

All your tables live in metadata. Calling metadata.create_all(engine) will create them in the database if they do not exist.

Model Classes (ORM, Declarative Style)

With the ORM, you define classes that map to tables. You inherit from a base class that stores the metadata.

A common pattern:

python

from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
from sqlalchemy import Integer, String, Boolean, DateTime
class Base(DeclarativeBase):
    pass
class Task(Base):
    __tablename__ = "tasks"
    id: Mapped[int] = mapped_column(Integer, primary_key=True, index=True)
    title: Mapped[str] = mapped_column(String, nullable=False)
    description: Mapped[str | None]
    completed: Mapped[bool] = mapped_column(Boolean, default=False)
    # created_at will be covered later in this course with defaults
    created_at: Mapped[DateTime | None]

Then:

python

from sqlalchemy import create_engine
engine = create_engine("sqlite:///./tasks.db")
Base.metadata.create_all(engine)

Now you can create and query Task objects through the ORM instead of building SQL manually.

Basic CRUD with SQLAlchemy Core

CRUD stands for Create, Read, Update, Delete. You will later have dedicated chapters on these operations with ORM, but here is what they look like using Core so you can see the lower level layer.

Assume we have:

python

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, select, insert, update, delete
engine = create_engine("sqlite:///./example.db")
metadata = MetaData()
users_table = Table(
    "users",
    metadata,
    Column("id", Integer, primary_key=True),
    Column("name", String, nullable=False),
    Column("email", String, nullable=False, unique=True),
)
metadata.create_all(engine)

Create (INSERT)

python

with engine.connect() as conn:
    stmt = insert(users_table).values(name="Bob", email="bob@example.com")
    result = conn.execute(stmt)
    conn.commit()
    print(result.inserted_primary_key)  # [1] for id

You can also insert many rows:

python

with engine.connect() as conn:
    stmt = insert(users_table)
    conn.execute(
        stmt,
        [
            {"name": "Alice", "email": "alice@example.com"},
            {"name": "Charlie", "email": "charlie@example.com"},
        ],
    )
    conn.commit()

Read (SELECT)

python

from sqlalchemy import select
with engine.connect() as conn:
    stmt = select(users_table)
    result = conn.execute(stmt)
    rows = result.fetchall()
    for row in rows:
        print(row.id, row.name, row.email)

Filtering:

python

with engine.connect() as conn:
    stmt = select(users_table).where(users_table.c.email == "alice@example.com")
    row = conn.execute(stmt).fetchone()
    if row:
        print(row.id, row.name)

Update (UPDATE)

python

from sqlalchemy import update
with engine.connect() as conn:
    stmt = (
        update(users_table)
        .where(users_table.c.email == "bob@example.com")
        .values(name="Robert")
    )
    result = conn.execute(stmt)
    conn.commit()
    print(result.rowcount)  # Number of rows updated

Delete (DELETE)

python

from sqlalchemy import delete
with engine.connect() as conn:
    stmt = delete(users_table).where(users_table.c.email == "charlie@example.com")
    result = conn.execute(stmt)
    conn.commit()
    print(result.rowcount)  # Rows deleted

This Core style is useful when you want very explicit control over the SQL, or you do not need to map rows to Python objects.

Basic CRUD with the ORM

Now compare that with ORM style. You will later see full details in Creating Records, Reading Records, Updating Records, and Deleting Records, but here is the idea.

Assume:

python

from sqlalchemy import create_engine
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session
from sqlalchemy import String
engine = create_engine("sqlite:///./example_orm.db", echo=True)
class Base(DeclarativeBase):
    pass
class User(Base):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String, nullable=False)
    email: Mapped[str] = mapped_column(String, unique=True, nullable=False)
Base.metadata.create_all(engine)

Create

python

with Session(engine) as session:
    user = User(name="Bob", email="bob@example.com")
    session.add(user)
    session.commit()
    session.refresh(user)  # Load generated id from DB
    print(user.id)         # New primary key

Read

python

with Session(engine) as session:
    users = session.query(User).all()
    for user in users:
        print(user.id, user.name, user.email)
    # Filter
    alice = session.query(User).filter(User.email == "alice@example.com").first()
    if alice:
        print(alice.name)

Update

python

with Session(engine) as session:
    bob = session.query(User).filter(User.email == "bob@example.com").first()
    if bob:
        bob.name = "Robert"
        session.commit()

The ORM tracks changes to objects and converts them to SQL UPDATE statements when you call commit().

Delete

python

with Session(engine) as session:
    bob = session.query(User).filter(User.email == "bob@example.com").first()
    if bob:
        session.delete(bob)
        session.commit()

Here you work with User objects and let SQLAlchemy figure out the necessary SQL.

Simple Example: Task Management Data Layer

To connect this chapter to later project work, here is a tiny example of how SQLAlchemy might be used in a backend for a Task Management API.

Database URL and engine:

python

from sqlalchemy import create_engine
from sqlalchemy.orm import Session, DeclarativeBase, Mapped, mapped_column
from sqlalchemy import String, Boolean
DATABASE_URL = "sqlite:///./tasks.db"
engine = create_engine(DATABASE_URL, echo=False)
class Base(DeclarativeBase):
    pass
class Task(Base):
    __tablename__ = "tasks"
    id: Mapped[int] = mapped_column(primary_key=True, index=True)
    title: Mapped[str] = mapped_column(String, nullable=False)
    completed: Mapped[bool] = mapped_column(Boolean, default=False)
Base.metadata.create_all(engine)

Basic operations:

python

def create_task(title: str) -> Task:
    with Session(engine) as session:
        task = Task(title=title, completed=False)
        session.add(task)
        session.commit()
        session.refresh(task)
        return task
def list_tasks() -> list[Task]:
    with Session(engine) as session:
        return session.query(Task).all()
def complete_task(task_id: int) -> Task | None:
    with Session(engine) as session:
        task = session.get(Task, task_id)
        if not task:
            return None
        task.completed = True
        session.commit()
        session.refresh(task)
        return task

Later, when you integrate this with FastAPI, these functions will be called from route handlers to implement your REST API.

Common Pitfalls for Beginners

When starting with SQLAlchemy, beginners often run into a few common problems.

Forgetting to Commit

If you do not call commit(), changes are not saved.

Wrong:

python

with Session(engine) as session:
    user = User(name="Test", email="test@example.com")
    session.add(user)
    # No commit here, nothing is actually written to the DB

Correct:

python

with Session(engine) as session:
    user = User(name="Test", email="test@example.com")
    session.add(user)
    session.commit()

Using a Closed Session

Once the context manager exits, the session is closed. You cannot use objects from it to perform new database operations.

This is wrong:

python

with Session(engine) as session:
    user = session.get(User, 1)
# Session is closed here
user.name = "New Name"
session.commit()  # Error, session is out of scope

The right patterns will be explained in detail in Database Sessions, including how to handle sessions in web frameworks.

Confusing Engine With Session

engine manages connections.
Session manages transactions and ORM objects.

Do not call engine.commit(). It does not exist. You always commit on a session or a Core connection.

Summary

In this chapter you learned:

What SQLAlchemy is and why backend developers use it.
The difference between SQLAlchemy Core and the ORM.
How to create an engine with a database URL.
How to define tables and ORM models at a high level.
How basic CRUD looks using both Core and the ORM.
Some common beginner mistakes to avoid.

Next chapters in ORM and Database Integration will build on this:

Models will show you how to define richer models.
Database Sessions will teach you proper session lifecycle patterns.
The CRUD chapters will show you how to implement each operation in a real backend.
Queries, Relationships, Transactions, and Connection Pooling will add more power step by step.

12.2. SQLAlchemy

Why Use SQLAlchemy?

Installing SQLAlchemy

SQLAlchemy Core vs ORM

SQLAlchemy Core in One Glance

SQLAlchemy ORM in One Glance

Engines and Database URLs

Database URLs

Defining Tables and Models

Table Objects (Core)

Model Classes (ORM, Declarative Style)

Basic CRUD with SQLAlchemy Core

Create (INSERT)

Read (SELECT)

Update (UPDATE)

Delete (DELETE)

Basic CRUD with the ORM

Create

Read

Update

Delete

Simple Example: Task Management Data Layer

Common Pitfalls for Beginners

Forgetting to Commit

Using a Closed Session

Confusing Engine With Session

Summary

Comments

Where to Move