SQLAlchemy — The Python SQL Toolkit and ORM

What it is

SQLAlchemy is the most widely used Python database library. It provides two distinct layers: a Core layer with a SQL expression language for building queries programmatically, and an ORM layer with unit of work pattern, identity map, eager/lazy loading, and relationship management. It supports PostgreSQL, MySQL, SQLite, Oracle, and SQL Server through pluggable dialects.

SQLAlchemy targets Python developers who need database access ranging from simple queries to complex multi-table ORM patterns. It is the default choice for Flask, FastAPI, and many other Python web frameworks.

How it saves time or tokens

SQLAlchemy eliminates hand-written SQL string concatenation and the security risks it creates. The ORM automates object-relational mapping, change tracking, and relationship loading. The unit of work pattern batches database operations for efficiency. For developers using AI coding assistants, SQLAlchemy's well-documented API means generated code is more likely to be correct on the first attempt compared to raw SQL strings.

How to use

1. Install SQLAlchemy with a database driver:

pip install sqlalchemy psycopg2-binary

1. Connect and run a query with Core:

from sqlalchemy import create_engine, text

engine = create_engine('postgresql://user:pass@localhost/mydb')
with engine.connect() as conn:
    result = conn.execute(text('SELECT 1'))
    print(result.scalar())

1. Define an ORM model and query:

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

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))

with Session(engine) as session:
    users = session.query(User).filter_by(name='Alice').all()

Example

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

class Base(DeclarativeBase):
    pass

class Author(Base):
    __tablename__ = 'authors'
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column()
    books: Mapped[list['Book']] = relationship(back_populates='author')

class Book(Base):
    __tablename__ = 'books'
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column()
    author_id: Mapped[int] = mapped_column(ForeignKey('authors.id'))
    author: Mapped['Author'] = relationship(back_populates='books')

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

with Session(engine) as session:
    author = Author(name='Guido', books=[Book(title='Python Intro')])
    session.add(author)
    session.commit()

Related on TokRepo

• Database tools -- Database management and query tools
• AI tools for coding -- Developer productivity tools

Common pitfalls

• SQLAlchemy 2.0 introduced a new query style using session.execute(select(...)) instead of session.query(Model); mixing both styles creates confusing code
• Lazy loading relationships inside loops causes N+1 query problems; use joinedload() or selectinload() for bulk operations
• Forgetting to call session.commit() after modifications -- the unit of work pattern tracks changes but does not persist them until explicitly committed