Skip to content
Tutorials Center
My learning

Topics

SQLAlchemy Tutorial

Learn SQLAlchemy step by step: 37 lessons in 9 chapters with examples, quizzes, diagrams and projects.

Start learning →

of lessons completed

Getting Started

  1. 1 What Is SQLAlchemy? Meet SQLAlchemy, the Python toolkit that talks to relational databases through Core and the ORM. Learn what it does, why people use it, and what runs on this site.
  2. 2 Installing SQLAlchemy and Your First Program Install SQLAlchemy 2.0 with pip, pick a database driver, check the version, and write a first program that creates a table, inserts rows and reads them back.
  3. 3 Core vs ORM: How SQLAlchemy Is Organized Understand SQLAlchemy's layers, the difference between Core and the ORM, how the same query looks in each, and how to choose or mix them in a real project.
  4. 4 A Quick Tour of the ORM Take a fast, hands-on tour of the SQLAlchemy ORM. Define two related models, add objects with a Session, query them, update and delete, all in one runnable program.

Engine and Connections

  1. 5 Creating an Engine and Database URLs Learn how create_engine() works, how database URLs are built for SQLite, PostgreSQL and MySQL, how to hide passwords, and which engine options matter first.
  2. 6 Connections and Transactions Understand SQLAlchemy 2.0 connections, commit-as-you-go, engine.begin(), rollback on error, savepoints with begin_nested(), and how to inspect transaction state.
  3. 7 Running Text SQL with Bound Parameters Run hand-written SQL safely with text(), named bound parameters, typed bindparam, executemany, IN lists with expanding parameters, and exec_driver_sql.
  4. 8 Working with Results and Rows Learn how SQLAlchemy Result objects work, how to read Row tuples by name or position, and when to use all, first, one, scalar, scalars, mappings and partitions.
  5. 9 Connection Pooling and Events How SQLAlchemy pools connections, what pool_size, max_overflow, pool_recycle and pool_pre_ping do, and how to hook into connections and statements with events.

Core and Expressions

  1. 10 MetaData and Table Definitions Describe a database schema with MetaData, Table and Column, create and drop tables, add foreign keys, inspect and reflect existing databases, and read the generated DDL.
  2. 11 Column Types, Defaults and Constraints Pick the right SQLAlchemy column types, set Python and server defaults, and enforce data rules with NOT NULL, UNIQUE, CHECK, foreign keys and indexes.
  3. 12 INSERT, UPDATE and DELETE in Core Change data with SQLAlchemy Core: insert one or many rows, read primary keys, update with expressions, delete, use RETURNING, and write an upsert for SQLite.
  4. 13 Building SELECT Queries with where() Build SELECT statements in SQLAlchemy Core and ORM style with select(), where(), and_/or_, in_, like, between, NULL checks, ordering, limit, offset and labels.
  5. 14 Joins and Aggregates Combine tables with join() and outerjoin(), summarize with func.count, sum and avg, group with group_by and having, and alias tables for self joins.
  6. 15 Subqueries, CTEs and SQL Functions Compose queries with scalar subqueries, EXISTS, subquery() and cte(), combine with union, compute with case(), cast and window functions like row_number().

ORM Mapping

  1. 16 Declarative Models with Mapped and mapped_column Map Python classes to tables with DeclarativeBase, Mapped annotations and mapped_column. Learn nullable types, constructors, __repr__, table args and dataclass mapping.
  2. 17 Column Options, Computed and Deferred Columns Go beyond plain columns in the ORM with Enum and JSON types, Python and server defaults, onupdate timestamps, column_property expressions, deferred columns and Computed values.
  3. 18 Working with Objects: Add, Query, Update, Delete Learn the everyday ORM operations with a Session: add and add_all, get by primary key, select entities, update by attribute or bulk statement, delete, and refresh.
  4. 19 Mixins, Inheritance and Hybrid Properties Share columns with mixins and abstract bases, map class hierarchies with single-table and joined-table inheritance, and write hybrid properties that work in Python and SQL.

Relationships and Loading

  1. 20 One-to-Many and One-to-One Relationships Connect ORM classes with relationship(), ForeignKey and back_populates. Build one-to-many and one-to-one links, navigate them as Python attributes and see the SQL.
  2. 21 Many-to-Many Relationships and Association Objects Model many-to-many links in SQLAlchemy with a secondary table, then upgrade to an association object when the link needs its own columns. Runnable examples.
  3. 22 Loading Strategies: Lazy, Joined, Selectin and More Control when related rows load. Compare lazy, joined, selectin, subquery, raise and noload strategies, count the SQL each emits and avoid the N+1 query problem.
  4. 23 Cascades, Orphans and Self-Referential Relationships Decide what happens to child rows when a parent is saved or deleted: cascade options, delete-orphan, passive_deletes, ON DELETE, plus adjacency-list trees.

Sessions and Transactions

  1. 24 Sessions, the Identity Map and the Unit of Work Understand what a SQLAlchemy Session really is: a unit of work with an identity map. Add, get, query, modify and delete objects and see exactly when SQL is sent.
  2. 25 Object States, Flush, Expire and Refresh Follow an ORM object through its life: transient, pending, persistent, detached. Learn flush vs commit, expire_on_commit, refresh, merge and DetachedInstanceError.
  3. 26 Transactions, Savepoints, Isolation and Locking Use session.begin(), commit and rollback correctly, nest work with savepoints, handle IntegrityError, pick isolation levels and prevent lost updates with version counters.
  4. 27 Session Patterns: sessionmaker, scoped_session and Events Create sessions the right way with sessionmaker, use scoped_session safely, build a request-scoped session helper, and hook into flushes with ORM events for audit fields.

Alembic and Migrations

  1. 28 Alembic: Your First Database Migrations Set up Alembic for SQLAlchemy, write a migration with op.create_table, run upgrade and downgrade, read the version table and understand revision files. Runs in your browser.
  2. 29 Alembic Autogenerate: Let Alembic Write the Migration Connect env.py to your models, generate migrations with --autogenerate, review what it produced and learn what autogenerate can and cannot detect. Runnable demo.
  3. 30 Production Migrations: Data Changes, Batch Mode, Offline SQL, Branches Migrate real databases safely: backfill data, change columns with batch_alter_table on SQLite, generate offline SQL, resolve branched heads and deploy with confidence.

Performance and Patterns

  1. 31 Finding N+1 Queries, Bulk Inserts and Streaming Results Measure before you optimize: count queries in tests, detect N+1 early, insert thousands of rows in one statement, update in bulk, stream big results and paginate by key.
  2. 32 Indexes, Query Plans and Connection Pooling Declare indexes in your models, read EXPLAIN QUERY PLAN output, design composite indexes, and tune pool_size, max_overflow, pool_pre_ping and pool_recycle.
  3. 33 The Repository and Unit of Work Patterns Keep SQLAlchemy out of your business logic: build a repository, wrap sessions in a unit of work, swap in a fake for fast tests and know when the patterns are overkill.
  4. 34 Hybrid Properties, Custom Types, Mixins and Soft Delete Write attributes that work in Python and in SQL with hybrid_property, store custom values with TypeDecorator, reuse columns with mixins and hide soft-deleted rows.

Projects and Practice

  1. 35 Project: A Blog Backend with SQLAlchemy Build the data layer of a blog: users, posts, comments and tags with relationships, a service layer, tag queries, pagination, counts and tests. Fully runnable.
  2. 36 Project: Library Loans with Business Rules and Reports Model a library with members, books, copies and loans. Enforce rules in code and with partial unique indexes, handle conflicts with transactions and write overdue reports.
  3. 37 Practice: SQLAlchemy Exercises with Solutions Seven hands-on SQLAlchemy exercises with self-checking tests: joins, aggregates, window functions, anti-joins, bulk updates, self-referential walks and query counting.