Up until now, you've probably been handling data in memory or maybe playing around with raw SQLite strings. But in the real world, we rarely write raw SQL strings inside our Python code—it's messy, prone to injection attacks, and a nightmare to maintain. That's where SQLAlchemy comes in. It's the gold standard for database toolkits in Python.

Now, SQLAlchemy is a bit of a beast because it actually gives you two different ways to work: Core and the ORM (Object Relational Mapper). Core is more like "SQL expressed in Python," while the ORM lets you treat database rows as if they were regular Python objects. I usually recommend understanding a bit of Core first so you aren't totally confused when the ORM does some "magic" under the hood.

Setting up the engine and a raw table

Let's build a simple reading list tracker. I'll start with Core. First, we need an engine, which is basically the bridge between Python and the database file. I'm using SQLite here because it doesn't require a server setup, making it perfect for this example.

from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData, insert, select

# This creates a local file called 'books.db'
engine = create_engine("sqlite:///books.db", echo=False)
metadata_obj = MetaData()

# Define the table schema using Core
books_table = Table(
    "books",
    metadata_obj,
    Column("id", Integer, primary_key=True),
    Column("title", String(100), nullable=False),
    Column("author", String(100)),
)

# Actually create the table in the database
metadata_obj.create_all(engine)

With Core, we interact with the database using a connection. I like to use a context manager (the with statement) so I don't have to worry about manually closing the connection if something crashes. Let's pop a book in there.

with engine.connect() as conn:
    # We use the insert() construct instead of a raw string
    stmt = insert(books_table).values(title="The Martian", author="Andy Weir")
    conn.execute(stmt)
    conn.commit() # Don't forget to commit!

    # Now let's grab it back
    query = select(books_table).where(books_table.c.title == "The Martian")
    result = conn.execute(query).fetchone()
    print(f"Found book: {result.title} by {result.author}")

Switching to the ORM for better ergonomics

Core is powerful, but writing books_table.c.title every time feels clunky. This is why the ORM exists. We can map a Python class directly to a database table. I'm going to redefine our setup using the modern SQLAlchemy 2.0 style (using DeclarativeBase).

from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session

class Base(DeclarativeBase):
    pass

class Book(Base):
    __tablename__ = "books_orm" # Different table name to avoid confusion
    
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column(String(100))
    author: Mapped[str] = mapped_column(String(100))

# Create the table
Base.metadata.create_all(engine)

Now, instead of thinking about "inserts" and "rows," I can just think about Python objects. I'll create a new book instance and add it to a Session.

with Session(engine) as session:
    new_book = Book(title="Project Hail Mary", author="Andy Weir")
    session.add(new_book)
    session.commit()

A common slip-up with session state

Here is where I almost tripped up while writing this. I tried to update the book's title immediately after the commit, like this:

with Session(engine) as session:
    # ... (added the book)
    session.commit()
    new_book.title = "Project Hail Mary (Special Edition)"
    # I thought it was saved now!

I ran the code, checked the database, and the title hadn't changed. Why? Because session.commit() expires the objects in the session to ensure they are refreshed from the database. When I changed the title, I was modifying a Python object, but I never told the session to push those changes back to the database with another commit(). I forgot that the ORM tracks changes, but it doesn't auto-save them to the disk the moment you change an attribute.

The fix is simple: always call session.commit() after you've finished your mutations.

with Session(engine) as session:
    book = session.query(Book).filter_by(title="Project Hail Mary").first()
    book.title = "Project Hail Mary (Special Edition)"
    session.commit() # This is the crucial line I missed

Handling relationships between tables

To wrap this up, let's make it a bit more realistic. A book shouldn't just have an author string; it should have an Author object. This is where the ORM really shines with relationship.

from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship

class Author(Base):
    __tablename__ = "authors"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(100))
    
    # Link to the Book class
    books: Mapped[list["Book"]] = relationship(back_populates="author")

# Update the Book class to use a foreign key
class Book(Base):
    __tablename__ = "books_rel"
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column(String(100))
    author_id: Mapped[int] = mapped_column(ForeignKey("authors.id"))
    
    author: Mapped["Author"] = relationship(back_populates="books")

Base.metadata.create_all(engine)

Now I can just do my_author.books.append(my_book) and SQLAlchemy handles the foreign keys for me behind the scenes. It's a lot more intuitive than manual ID management.

📋 Practical Task

Exercise: Building a Movie Review Database with SQLAlchemy ORM

Your task is to create a small database system to track movies and their reviews. You should use the SQLAlchemy ORM (2.0 style) to achieve the following:

• Define two models:
  • Movie: Should have an id, title, and release_year.
  • Review: Should have an id, content (the text of the review), a rating (integer), and a movie_id as a foreign key linking it to the Movie model.
• Establish a relationship: Ensure that a Movie object can access all of its related Review objects via a relationship property.
• Implement a script that:
  1. Creates the database and tables.
  2. Adds one movie (e.g., "Inception", 2010).
  3. Adds two different reviews for that specific movie using the relationship (by appending to the movie's review list).
  4. Commits the changes.
  5. Queries the movie back from the database and prints the average rating of its reviews.