SQLAlchemy

noun

say it: “ESS-kyoo-el AL-kuh-mee” /ˌɛs kju ˈɛl ˈælkəmi/

Python's most used ORM and database toolkit: tables are described as classes, and queries built in Python are turned into SQL.

What is SQLAlchemy?

SQLAlchemy is Python's most used ORM and database toolkit. Each table is a class, and a query such as select(Workout).where(Workout.member == "Luis") is built in Python and turned into SQL with a placeholder. SQLModel builds on it.

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

class Base(DeclarativeBase):
    pass

class Workout(Base):
    __tablename__ = "workouts"
    id: Mapped[int] = mapped_column(primary_key=True)
    member: Mapped[str]
    minutes: Mapped[int]

engine = create_engine("sqlite://")   # a database in memory
Base.metadata.create_all(engine)      # writes CREATE TABLE workouts for you
with Session(engine) as session:
    session.add_all([Workout(member="Luis", minutes=40), Workout(member="Ana", minutes=25)])
    session.commit()
    for workout in session.scalars(select(Workout).where(Workout.member == "Luis")):
        print(workout.member, workout.minutes)   # Luis 40

In a sentence

  • “The workout API on GitHub used SQLAlchemy, so its query read select(Workout).where(Workout.member == "Luis").”
  • “SQLModel builds on SQLAlchemy and Pydantic.”

Level: Freelancer · More Python terms at this level

Updated