Python Reference ยท Third-party Libraries
The SQLAlchemy library
Overview of SQLAlchemy 2.x: the Engine, declarative models, Session, select statements, relationships, transactions and the mistakes to avoid, with runnable examples.
SQLAlchemy is Python's most complete database toolkit. It has two layers you can use separately or together. Core builds and runs SQL statements from Python expressions, so the same code works on SQLite, PostgreSQL, MySQL and others. The ORM (object-relational mapper) goes further and maps your Python classes to tables, so rows become objects and changes are written back for you.
Install it with python -m pip install sqlalchemy; PostgreSQL and MySQL also need a driver package. This page uses the modern 2.x style (select(), Session, typed Mapped columns) and an in-memory SQLite database, so everything runs in the browser.
from sqlalchemy import create_engine engine = create_engine("sqlite:///:memory:")
Try it
Press Run (or Ctrl + Enter) to see the result here.
Program input ยท what you'd type while the program runs
Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.
Press Run (or Ctrl + Enter) to see the result here.
Program input ยท what you'd type while the program runs
Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.
Main pieces
| Piece | Role |
|---|---|
create_engine(url) |
Creates the connection source. Done once per application; the URL names the database and driver. |
Connection (engine.connect(), engine.begin()) |
Core-level access; begin() wraps a transaction. |
Table, MetaData |
Core description of tables. metadata.create_all(engine) creates missing ones. |
DeclarativeBase, Mapped, mapped_column |
ORM base class and typed column declarations. |
Session |
The ORM unit of work: tracks objects, flushes changes, commits or rolls back. |
select(), insert(), update(), delete() |
Statement builders, using .where(), .order_by(), .join() and so on. |
relationship() |
Links two classes, so author.books loads the related rows. |
session.scalars(stmt) / session.execute(stmt) |
Run a statement; scalars() yields entities, execute() yields rows. |
Usage notes
- Use the 2.x style. The legacy
session.query(Model)API still works but new code should usesession.scalars(select(Model)). - A session is not thread-safe or long-lived. Create one per unit of work (a request, a task) and close it, ideally with
with Session(engine) as session. - Nothing is saved until
commit().session.flush()sends pending SQL without ending the transaction;rollback()discards it. - Never build SQL with string formatting. Use expressions or bound parameters (
text("... WHERE id = :id")with{"id": 5}) to avoid SQL injection. - Lazy loading and N+1 queries: reading
author.booksinside a loop over many authors runs one query per author. Load related rows up front withselectinload(Author.books)inselect(...).options(...). - Result helpers:
.all()returns a list,.first()the first orNone,.one()exactly one (raisesNoResultFoundorMultipleResultsFoundotherwise),.scalar_one_or_none()one value orNone. - Migrations (changing tables after creation) are the job of Alembic.
create_all()creates missing tables but never alters existing ones. - SQLite and threads: an in-memory SQLite database exists per connection; that is fine for examples but not for a web app.
- Async:
sqlalchemy.ext.asyncioprovidesAsyncEngineandAsyncSessionforasync/awaitcode with async drivers.