You have been writing raw SQL strings inside Python, gluing column names together by hand, and quietly hoping nobody passes a quote character into your WHERE clause. Python SQLAlchemy exists so you can stop hoping. It is the Object-Relational Mapping (ORM) that lets you describe your tables as plain Python classes, then query them with Python methods instead of hand-written SQL.

“The ORM’s job is to let you think in objects, not in SQL.”

Mike Bayer, SQLAlchemy creator

Last Updated: July 2026 | Tested on: Python 3.14.6, SQLAlchemy 2.0.51 | Difficulty: Advanced | Reading Time: 13 minutes

Raw SQL is fine for one quick query. It stops being fine the moment your app grows a User, a Post, a Comment, and a Tag with relationships running between them. Now you have SQL strings scattered across a dozen files, you map rows to objects by hand, and a typo in a column name stays invisible until it blows up at runtime in front of a user.

Here is the everyday version. Think of a restaurant. You (the Python code) tell the waiter what you want in plain language. The waiter (the ORM) walks to the kitchen, translates your order into the kitchen’s exact format, brings the plate back, and you never once stepped behind the stove. SQLAlchemy is that waiter. Say you want to look up a user named Rahul: you write session.scalars(select(User).where(User.name == "Rahul")) in Python, and SQLAlchemy writes the SELECT statement, runs it, and hands you back real User objects. You think in objects. It thinks in SQL.

Everything below uses SQLAlchemy 2.0’s modern syntax: Mapped typed columns, the select() query style, the session lifecycle, relationships, and the layered architecture that makes the whole thing work. Every example runs on an in-memory SQLite database, so you can copy, paste, and run it with nothing installed but SQLAlchemy itself.

Tip: click the diagram to open it full screen, then use the zoom and pan controls for a closer look.

The diagram shows SQLAlchemy’s layered architecture. Your Python class definitions map to database tables through the ORM layer, which builds SQL and hands it to the engine underneath. This separation is why you write Python objects and method calls instead of raw SQL strings, and why the same code can talk to SQLite, MySQL, or PostgreSQL with only the connection string changing. The layers also explain why SQLAlchemy ships two APIs (Application Programming Interfaces): a “Core” layer (SQL expressions) and an “ORM” layer (object mapping) built on top of it.

Install and Verify

One pip command and you are done. SQLAlchemy is pure Python with no system dependencies, so it installs cleanly on Windows, macOS, and Linux.

📄 Terminal: install SQLAlchemy and check the version

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

▶ Output

2.0.51

What happened here: If you see 2.0.51 (or any 2.0.x), you are on the modern 2.0 series and every example below will work. If you see a 1.4.x number, run pip install --upgrade sqlalchemy first. The 2.0 line changed how you write queries (the select() style you will see in a minute), so old 1.x tutorials will steer you wrong. SQLite ships inside Python itself, so you do not install a database server to follow along.

The Quick Win: Define Models

Think of a printed admission form at a school: the form fixes the fields once (name, email, age), and every student who joins fills in the same fields. A model is that form for your database. It is a Python class that maps to a table: each typed attribute becomes a column, and SQLAlchemy reads those type hints to build the table for you. Here are two models, a User and a Post, with a link between them. We point the engine at "sqlite://", which is an in-memory database that lives only while the program runs, so there is no file to clean up afterwards.

📄 models.py: define tables with SQLAlchemy 2.0 syntax

from sqlalchemy import create_engine, String, ForeignKey, select
from sqlalchemy.orm import (
    DeclarativeBase, Mapped, mapped_column,
    relationship, Session
)

class Base(DeclarativeBase):
    pass

class User(Base):
    __tablename__ = "users"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(100))
    email: Mapped[str] = mapped_column(String(200), unique=True)
    age: Mapped[int]

    posts: Mapped[list["Post"]] = relationship(back_populates="author")

    def __repr__(self) -> str:
        return f"User(id={self.id}, name={self.name!r})"

class Post(Base):
    __tablename__ = "posts"

    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column(String(200))
    user_id: Mapped[int] = mapped_column(ForeignKey("users.id"))

    author: Mapped["User"] = relationship(back_populates="posts")

    def __repr__(self) -> str:
        return f"Post(id={self.id}, title={self.title!r})"

# Create the engine and build the tables (in-memory SQLite)
engine = create_engine("sqlite://", echo=False)
Base.metadata.create_all(engine)
print("Tables created!")

▶ Output

Tables created!

What happened here: Every model inherits from Base, which is how SQLAlchemy keeps a registry of your tables. The Mapped[int] and Mapped[str] hints are not decoration: SQLAlchemy reads them to decide the column type, so name: Mapped[str] with String(100) becomes a VARCHAR(100) column. The ForeignKey("users.id") on Post.user_id wires each post back to a user, and the two relationship() lines with back_populates let you walk from a user to their posts and from a post back to its author in plain Python. Base.metadata.create_all(engine) issues the CREATE TABLE statements. The echo=False keeps it quiet; flip it to echo=True when you want to watch the real SQL scroll past.

Core Concept: Session CRUD

The Session is where the work happens in Python SQLAlchemy. Think of it as a shopping cart: you add new objects, change existing ones, and remove things, but nothing actually hits the database until you call session.commit() (checkout). Until then the session quietly tracks every change you made. The block below runs all four CRUD operations (Create, Read, Update, Delete) against the models we just defined: two people named Rahul and Niranjan sign up as users, and Rahul writes a couple of posts.

📄 crud.py: create, read, update, delete with sessions

# CREATE
with Session(engine) as session:
    rahul = User(name="Rahul", email="rahul@example.com", age=28)
    niranjan = User(name="Niranjan", email="niranjan@example.com", age=26)
    session.add_all([rahul, niranjan])

    rahul.posts.append(Post(title="Python SQLAlchemy Tutorial"))
    rahul.posts.append(Post(title="FastAPI Best Practices"))
    session.commit()

# READ: modern select() syntax (SQLAlchemy 2.0)
with Session(engine) as session:
    stmt = select(User).where(User.age > 25)
    users = session.scalars(stmt).all()
    for user in users:
        print(f"{user.name} ({user.age}): {len(user.posts)} posts")

# UPDATE
with Session(engine) as session:
    user = session.scalars(
        select(User).where(User.name == "Niranjan")
    ).first()
    if user:
        user.age = 27
        session.commit()

# DELETE
with Session(engine) as session:
    post = session.scalars(
        select(Post).where(Post.title.contains("FastAPI"))
    ).first()
    if post:
        session.delete(post)
        session.commit()

▶ Output

Rahul (28): 2 posts
Niranjan (26): 0 posts

What happened here: Each with Session(engine) as session: block opens a session and closes it automatically at the end, even if something raises. Notice you never wrote a single INSERT, SELECT, UPDATE, or DELETE by hand. You created Python objects, appended posts to rahul.posts like a normal list, and SQLAlchemy figured out the SQL on commit. The select(User).where(User.age > 25) reads almost like English, and session.scalars(stmt).all() returns real User objects, not raw tuples. The output is computed after the commit, so it reflects two users in the database with Rahul holding two posts. The update and delete blocks run silently, so they print nothing.

Core Concept: Relationships

This is the payoff of an ORM. It works like the chat app on your phone: open the profile of a friend, say Anvi, and you see every message she sent; tap any single message and you jump straight back to her profile. You never think about the lookup happening underneath. Because we declared the relationship() on both models, you walk between tables the same way, just by reading attributes. No JOIN, no foreign-key bookkeeping. It feels like Rahul simply has a list of posts, and each post simply knows its author.

📄 relationships.py: walk between objects, no manual JOIN

with Session(engine) as session:
    rahul = session.scalars(
        select(User).where(User.name == "Rahul")
    ).first()

    # Walk forward: user -> posts
    for post in rahul.posts:
        print(f"  {post.title} (by {post.author.name})")

    # Walk backward: post -> author
    post = session.scalars(select(Post)).first()
    print(f"\n{post.title} was written by {post.author.name}")

▶ Output

  Python SQLAlchemy Tutorial (by Rahul)

Python SQLAlchemy Tutorial was written by Rahul

What happened here: Rahul started with two posts, but the DELETE block in the previous section removed the “FastAPI Best Practices” one, so only “Python SQLAlchemy Tutorial” remains. Reading rahul.posts triggers a lazy load: SQLAlchemy quietly runs a SELECT against the posts table the first time you touch the attribute. Then post.author.name walks the relationship in the other direction. You described the link once with back_populates, and now both directions just work. That is the whole reason people reach for an ORM instead of stringing JOINs together by hand.

SQLAlchemy vs Django ORM

Python SQLAlchemy is not the only ORM in town. Django ships its own. Think of a built-in car stereo versus one you buy separately: the built-in unit fits its car perfectly but goes nowhere else, while the separate one works in any vehicle. The short version: pick SQLAlchemy when you are not on Django, pick Django ORM when you are. Here is the fuller comparison.

My take: if you are building on Flask or FastAPI, SQLAlchemy is the obvious choice and it is what those communities expect to see. If you live inside Django, fighting its built-in ORM to bolt on SQLAlchemy is rarely worth it. The steeper learning curve is real, but it buys you an escape hatch: when a query gets complicated, you can drop down to Core or to raw SQL without leaving the library.

Ecosystem and Migrations

Python SQLAlchemy rarely travels alone. A few companions you will meet quickly:

• Alembic: the migration tool from the same author. create_all() builds tables once, but the day you add a column to a live database, you need migrations. Run pip install alembic then alembic init migrations, and Alembic can auto-generate migration scripts by diffing your models against the current schema.
• Database drivers: SQLite is built into Python. For PostgreSQL you add psycopg2-binary (connection string postgresql+psycopg2://...), and for MySQL you add a driver like mysql-connector-python. The models and queries stay identical; only the engine URL changes.
• Pydantic: pairs naturally with SQLAlchemy in FastAPI apps. SQLAlchemy models own the database side, Pydantic models validate and shape the JSON (JavaScript Object Notation) that goes in and out of your API.

Common Mistakes

Mistake 1: Using objects after the session closes

This one bites everybody once. A relationship like user.posts is loaded lazily, which means SQLAlchemy waits until you touch it and then runs a query. If the session is already closed, there is no connection to run that query on, and you get a DetachedInstanceError.

❌ Wrong: touching a lazy attribute after the session is gone

with Session(engine) as session:
    user = session.scalars(select(User)).first()

print(user.posts)  # session is closed: DetachedInstanceError

▶ Output

sqlalchemy.orm.exc.DetachedInstanceError: Parent instance <User at 0x...> is not
bound to a Session; lazy load operation of attribute 'posts' cannot proceed

✅ Correct: read what you need while the session is open

with Session(engine) as session:
    user = session.scalars(select(User)).first()
    post_titles = [p.title for p in user.posts]  # read inside the session

print(post_titles)  # works, the data is already in plain Python

▶ Output

['Python SQLAlchemy Tutorial']

Why: the fix is to pull the values you need into ordinary Python objects (a list of titles) before the with block ends. After that, you are holding plain strings, not lazy database attributes, so there is nothing left to load. If you genuinely need related objects after the session closes, look at eager loading with selectinload() or joinedload(), which fetch the relationship up front in one go.

Mistake 2: Forgetting to commit

Adding an object to a session is not saving it. Say a new user named Aditi fills in your signup form: session.add(aditi) only stages her row, the way an item sitting in a shopping cart is not yet purchased. Without session.commit(), the change vanishes when the with block exits and the session rolls back, and Aditi never appears in the table. If your inserts keep disappearing, this is almost always why.

Wrapping Up

You now have the working core of Python SQLAlchemy: models declared with Mapped and mapped_column(), sessions that stage your changes until commit(), the 2.0 select() query style, and relationships that let you walk between tables without writing a single JOIN. You also met the two traps that catch almost everyone, detached instances and forgotten commits, before they could catch you.

Next we leave databases behind and pull data straight off live web pages with web scraping using BeautifulSoup. And if you want to jump around or catch up on earlier posts, the Python + AI/ML tutorial series home lists every tutorial in order.

Frequently Asked Questions

Interview Questions on SQLAlchemy

Interviewers rarely ask for definitions. They ask what happens in situations like these.

Q: Your API endpoint lists 50 users along with their posts, and the query log shows 51 SELECT statements for a single request. What is happening and how do you fix it?

That is the classic N+1 problem. One SELECT fetches the 50 users, then lazy loading fires a separate SELECT for each user’s posts the moment the code touches that attribute. The fix is eager loading: add .options(selectinload(User.posts)) to the select statement, which fetches all the posts in one extra query instead of 50, or use joinedload() to pull everything in a single JOIN. Always confirm with echo=True or your query log that the count actually dropped.

Q: A background job crashes with DetachedInstanceError when it reads user.posts, but the same code works fine elsewhere. What do you check first?

Check where the object was loaded and whether that session is still open at the point of the crash. The usual culprit is a helper function that opens a session in a with block, returns the object, and closes the session, so the lazy posts attribute has no connection left to load from. Fix it by reading the relationship inside the session, eager loading it with selectinload() before the session closes, or passing plain data (ids, dicts) out of the function instead of live ORM objects.

Q: What is the difference between session.flush() and session.commit()?

flush() sends the pending INSERT, UPDATE, and DELETE statements to the database inside the current transaction, but does not end the transaction, so everything can still be rolled back. commit() flushes first and then commits the transaction, making the changes permanent. A common reason to call flush() yourself is to get an autogenerated primary key back before you are ready to commit.

Q: How does SQLAlchemy protect you from SQL injection?

Every value you pass through the ORM or Core, like User.name == some_input, becomes a bound parameter. The SQL is sent with placeholders and the values travel separately to the driver, so user input is never spliced into the SQL string and cannot change the query’s structure. You only reopen the door if you build text() queries by concatenating strings yourself, so always use the :param binding syntax with raw SQL too.

Q: Why should each web request get its own Session instead of sharing one global Session?

A Session is not thread-safe and it carries state: an identity map of loaded objects and a unit of work of pending changes. Share one across requests and users start seeing each other’s uncommitted data, and a single failed transaction poisons every request until someone rolls it back. The standard pattern is session-per-request: open at the start, commit or roll back, close at the end, which is exactly what FastAPI dependencies and Flask teardown hooks are used for.

Q: You add a new column to a model and everything works locally, but production fails with “no such column” even though create_all() runs at startup. Why?

Base.metadata.create_all() only creates tables that do not exist yet; it never alters an existing table, so the production table silently keeps its old shape. Locally it worked because your dev database was created fresh with the new column already in the model. The correct fix is a schema migration: generate one with Alembic (alembic revision --autogenerate), review the script, and apply it with alembic upgrade head.

Reference: the complete, always-current details live in SQLAlchemy documentation.

Related Posts

Previous: Python: MySQL and PostgreSQL Connecting and Querying

Next: Python: REST APIs with requests, Authentication, Pagination

Series Home: Python + AI/ML Tutorial Series