SQLAlchemy

The de facto Python SQL toolkit. Two layers in one library: Core (a SQL-builder / expression language) and ORM (objects-to-rows mapping on top of Core). For interview prep, the deep notes live in sqlalchemy/ — this file is the entry point and pointer.

Core vs ORM in one paragraph

Core: build SQL programmatically using Table, select(), insert(). Returns rows (tuples / Row objects). No object identity, no change tracking, no lazy loading. Closer to “typed SQL builder.”
ORM: declare Python classes that map to tables; the ORM tracks instances, batches changes in a Unit of Work, flushes them as INSERT/UPDATE/DELETE on commit. Loads related objects on attribute access.

The ORM is built on top of Core — same engine, same connection pool, same expression language under the hood. You can mix both in one app.

2.0-style — what changed

SQLAlchemy 2.0 (released 2023) unified the Core and ORM query syntax around select(). The old session.query(Model).filter_by(...) style still works but is “legacy 1.x.” New code should use:

from sqlalchemy import select

stmt = select(User).where(User.name == "alice")
user = session.scalars(stmt).first()

session.query(...) is the older style; equivalent and still common in production code. Most interview answers expect you to mention 2.0 style as the modern default. See sqlalchemy/05_query_select_2_0_style.md.

A minimal end-to-end example (2.0 style)

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(50))
    posts: Mapped[list["Post"]] = relationship(back_populates="author")

class Post(Base):
    __tablename__ = "posts"
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str]
    author_id: Mapped[int] = mapped_column(ForeignKey("users.id"))
    author: Mapped["User"] = relationship(back_populates="posts")

engine = create_engine("postgresql+psycopg://user:pw@localhost/db", echo=False)
Base.metadata.create_all(engine)

with Session(engine) as session:
    session.add(User(name="alice"))
    session.commit()

    stmt = select(User).where(User.name == "alice")
    alice = session.scalars(stmt).one()
    print(alice.posts)  # loads from DB lazily on access

Files in this folder

sqlalchemy/ — focused topical notes

Related elsewhere:

07_n_plus_1.md — N+1 query problem (ORM-agnostic intro)
08_transactions_isolation.md — isolation levels at the DB layer
09_connection_pooling.md — pooling at the infra layer (pgbouncer etc.)
16_alembic.md — schema migrations for SQLAlchemy

Interview angle

“What’s the difference between Core and ORM?” — Core is a SQL expression builder; ORM maps Python classes to rows with change tracking and lazy loading on top of Core. Same engine and pool under both.
“1.x vs 2.0 style?” — 2.0 unifies queries around select(); the old session.query() is legacy but still works. New code: 2.0 style with typed Mapped[...] columns.
“When would you drop down to Core?” — bulk inserts/updates, complex reporting queries, when the ORM is producing inefficient SQL, or when object identity / change tracking is overhead you don’t need.