Skip to content
Tutorials Center
My learning

Topics

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.

Python
from sqlalchemy import create_engine

engine = create_engine("sqlite:///:memory:")

Try it

Python Core: tables and select()

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.

Python ORM: models, a relationship and a session

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 use session.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.books inside a loop over many authors runs one query per author. Load related rows up front with selectinload(Author.books) in select(...).options(...).
  • Result helpers: .all() returns a list, .first() the first or None, .one() exactly one (raises NoResultFound or MultipleResultsFound otherwise), .scalar_one_or_none() one value or None.
  • 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.asyncio provides AsyncEngine and AsyncSession for async/await code with async drivers.