SQLAlchemy

2.0.48 · active · verified Tue Mar 24

The most widely used Python SQL toolkit and ORM. v2.0 released January 2023 — massive breaking change from v1.x. The entire ORM query API shifted from session.query() to select(). engine.execute() removed. Passing raw strings to execute() removed. Typed mapped_column() replaces Column(). Current version: 2.0.48 (Mar 2026). Flask-SQLAlchemy's Model.query pattern is also legacy in v2.

Warnings

breaking engine.execute() removed in v2.0. Raises AttributeError. Most LLM-generated SQLAlchemy code uses engine.execute().
Fix: Use 'with engine.connect() as conn: conn.execute(text(...))'
breaking Passing raw strings to execute() removed. conn.execute('SELECT 1') raises ArgumentError in v2.
Fix: from sqlalchemy import text; conn.execute(text('SELECT 1'))
breaking declarative_base() from sqlalchemy.ext.declarative is legacy in v2. Still works but generates RemovedIn20Warning.
Fix: from sqlalchemy.orm import DeclarativeBase; class Base(DeclarativeBase): pass
gotcha session.query() is legacy in v2 — still works but not recommended. Flask-SQLAlchemy's Model.query is also legacy. LLMs trained pre-2023 generate session.query() patterns.
Fix: Replace session.query(Model).filter(...) with session.scalars(select(Model).where(...))
gotcha session.execute() now returns a Result object. For ORM models use session.scalars() to get model instances directly, not session.execute().
Fix: users = session.scalars(select(User)).all() — not session.execute(select(User)).all()
gotcha Autocommit behavior changed in v2. Connections no longer autocommit. Must explicitly use with engine.begin() for autocommit or session.commit().
Fix: Use 'with engine.begin() as conn:' for autocommit. Or call session.commit() explicitly.
gotcha Column() without type annotations still works but loses IDE/mypy type inference. mapped_column() with Mapped[] is the v2 typed approach.
Fix: id: Mapped[int] = mapped_column(primary_key=True)

Install

pip install sqlalchemy Python (core)
pip install 'sqlalchemy[asyncio]' Python (with async support)

Imports

select (v2 ORM query)

from sqlalchemy import select
from sqlalchemy.orm import Session

with Session(engine) as session:
    # v2 style — use select()
    stmt = select(User).where(User.username == 'alice')
    user = session.scalars(stmt).first()

    # Insert
    new_user = User(username='bob', email='bob@example.com')
    session.add(new_user)
    session.commit()

session.query() is legacy API in v2. Still works but not recommended. Use select() + session.scalars() or session.execute() instead.

mapped_column (v2 ORM models)

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

class Base(DeclarativeBase):
    pass

class User(Base):
    __tablename__ = 'users'
    id: Mapped[int] = mapped_column(primary_key=True)
    username: Mapped[str] = mapped_column(String(50), unique=True)
    email: Mapped[str] = mapped_column(String(120))

declarative_base() still works in v2 but is legacy. Use DeclarativeBase class directly. mapped_column() with Mapped[] type annotations is the v2 style.

engine.connect / text()

from sqlalchemy import create_engine, text

engine = create_engine('postgresql://user:pass@localhost/db')

with engine.connect() as conn:
    result = conn.execute(text('SELECT 1'))
    print(result.fetchall())

engine.execute() removed in v2. Raw string execution removed — must wrap in text(). Always use text() for raw SQL.

Quickstart

SQLAlchemy 2.0 ORM quickstart with typed models.

# pip install sqlalchemy
from sqlalchemy import create_engine, String, select, text
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session

class Base(DeclarativeBase):
    pass

class User(Base):
    __tablename__ = 'users'
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(50))

engine = create_engine('sqlite:///test.db')
Base.metadata.create_all(engine)

with Session(engine) as session:
    # Insert
    session.add(User(name='Alice'))
    session.commit()

    # Query — v2 style
    stmt = select(User).where(User.name == 'Alice')
    user = session.scalars(stmt).first()
    print(user.name)  # 'Alice'

view raw JSON →