SQLAlchemy 2.0 #1 The Big Picture: Core, ORM, and the 2.0 Style

Python code that talks to a database eventually runs into SQLAlchemy, wherever you go — in FastAPI projects, in data pipelines, in aging Flask apps. The problem is that the internet is a mix of 1.x-era code and 2.0-style code, so learning by search leaves you with a jumble of mutually incompatible syntax. This series covers SQLAlchemy from the ground up, unified on the 2.0 style throughout, in seven parts. Where Modern Python in Practice #3 covered FastAPI integration as an overview, this series goes deep into SQLAlchemy itself.

SQLAlchemy is two layers: Core and ORM #

The first picture to get right when learning SQLAlchemy is this one. SQLAlchemy is not a single library but a two-layer stack.

• Core: database connectivity (the engine, the connection pool), transactions, and the SQL Expression Language for composing SQL as Python expressions. The tool for “writing SQL in Python.”
• ORM: the layer on top of Core that maps table rows to Python objects. You create and modify objects, and the ORM tracks the changes and produces the necessary SQL for you.

Even when you use the ORM, connections and transactions come straight from Core, and ORM queries are translated to Core expressions internally. That is why this series proceeds in the same order: the next part solidifies Core (engines, transactions), and part 3 climbs up to the ORM. Skip Core and learn only the ORM, and you will inevitably get stuck on questions like “why didn’t my commit happen” and “why did the connections run out.”

Which layer you lean on depends on the job. Applications with clear domain objects (users, orders, posts) fit the ORM; work like bulk aggregation and reporting, where rows do not need to be objects, is simpler and faster in Core. The two are not exclusive — mixing them in one project is the norm.

1.x and 2.0: what changed and why #

SQLAlchemy 2.0 (released in 2023) cleaned up two long-standing problems.

1. There were multiple ways to query. In 1.x, the ORM queried with session.query(User) while Core used select(). 2.0 unified everything on select(). The same syntax works in Core and in the ORM.
2. There was too much implicit behavior. Things like autocommit and implicit connections looked convenient but made debugging hard. 2.0 switched to an explicit style where starting and committing a transaction shows up in the code.

On top of that, 2.0 has first-class type hint support. Declare your models with Mapped[int] and Mapped[str], and IDE autocompletion and mypy checks flow all the way through to query results. That alone is reason enough to move to the 2.0 style.

Telling the styles apart in the wild is easy. If you see session.query(...), it is 1.x style (it still runs in 2.0, but it is legacy). If select(...) is passed to session.execute() or session.scalars(), it is 2.0 style. All code in this series is the latter.

Installation and the first connection #

With uv, installation is one line. If package management is new to you, start with the Python Packaging series.

uv add sqlalchemy

To start without a database server, we use SQLite. Its driver ships with Python, so there is nothing extra to install. The starting point of connectivity is the Engine.

from sqlalchemy import create_engine

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

• The first argument is the connection URL, in the form dialect+driver://user:password@host/dbname; SQLite just takes a file path. For PostgreSQL it looks like postgresql+psycopg2://user:pw@localhost/mydb.
• echo=True logs every SQL statement executed. Keep it on while learning — watching what SQL the ORM produces from your code is the fastest way to learn.
• create_engine does not connect at this point. The engine is an object holding “how to connect, plus a connection pool”; the actual connection happens at the first query.

The first query: raw SQL with text() #

Before learning the abstractions, verify the lowest layer. text() executes a SQL string as-is.

from sqlalchemy import text

with engine.connect() as conn:
    result = conn.execute(text("SELECT 'hello' AS greeting"))
    print(result.all())  # [('hello',)]

• engine.connect() borrows a connection from the pool and returns it when the with block ends.
• conn.execute() returns a Result object. Rows come out via methods like .all(), .first(), and .scalar(), and can be accessed like tuples or by column name.

Parameters must always be passed as bindings. Assembling SQL with string formatting is the shortest road to SQL injection.

with engine.connect() as conn:
    result = conn.execute(
        text("SELECT :name AS name, :age AS age"),
        {"name": "Alice", "age": 30},
    )
    row = result.first()
    print(row.name, row.age)  # Alice 30

text() alone can do every database task. The reason to use SQLAlchemy anyway is that the layers above it move string-assembly errors closer to compile time, absorb dialect differences between databases, and automate object mapping. We will climb those layers one at a time through the series.

The map of this series #

Summary #

• SQLAlchemy is a two-layer structure: Core (connectivity, transactions, SQL expressions) and ORM (object mapping). The ORM runs on top of Core.
• The 2.0 style unifies queries on select(), makes transactions explicit, and supports type hints natively. session.query() is the legacy tell.
• Connectivity starts with create_engine(); the engine manages how to connect and the pool, and does not connect at creation time.
• text() plus parameter binding runs SQL directly. Never assemble SQL with string formatting.
• The next part covers the engine, the connection pool, and 2.0’s two explicit transaction patterns.