SQLAlchemy, Deep Dive

The mapper and the query engine together: what a session really does, and the SQL your Python turns into.

Complete 20 notebooks 20 written

Before this guide: Object-Oriented Python, and Files, Paths and Formats.

The notebooks

Open in Colab runs a notebook in your browser, and Read shows it on GitHub without running it. Every notebook runs on its own, and the answers to its exercises are in a separate notebook, linked as Solutions.

1	Why SQLAlchemyThe query you build in three ifs, and the string you stop concatenating	Open in Colab Read Solutions
2	Engines and URLscreate_engine, echo, the pool you did not ask for, and the in-memory database that looks empty from another thread	Open in Colab Read Solutions
3	Connections and Transactionsconnect against begin, savepoints, and the rows that were never actually saved	Open in Colab Read Solutions
4	Reading ResultsRow, scalars, one, and the result you already consumed	Open in Colab Read Solutions
5	Tables and MetadataTable, MetaData, constraints that have names, and reflecting a database you did not design	Open in Colab Read Solutions
6	SQL Expressionsselect, insert, update, and delete as objects, and the where clause that quietly lost half of itself	Open in Colab Read Solutions
7	Declarative ModelsDeclarativeBase, Mapped and mapped_column, and the annotation it cannot map	Open in Colab Read Solutions
8	Column TypesNaive datetimes, the precision SQLite quietly loses, and the type that only accepts a datetime	Open in Colab Read Solutions
9	The Sessionsessionmaker, add, flush, commit, and what autoflush does behind your back	Open in Colab Read Solutions
10	The Identity MapSession.get, two queries and one object, and the attribute that expired the moment you committed	Open in Colab Read Solutions
11	RelationshipsForeignKey, back_populates, and the join condition it cannot determine	Open in Colab Read Solutions
12	Many to Manysecondary, the association table, and the extra column it cannot reach	Open in Colab Read Solutions
13	Loading StrategiesThe N plus 1 queries you did not know you wrote, selectinload against joinedload, and unique	Open in Colab Read Solutions
14	Joins and Aggregatesjoin, aliased, group_by, any and has, and the warning that you built a cartesian product	Open in Colab Read Solutions
15	Cascades and Deletesdelete-orphan against passive_deletes, and the SELECT that loads every child just to delete it	Open in Colab Read Solutions
16	Async SQLAlchemyAsyncSession and async_sessionmaker over a database that gains nothing from them, and the greenlet error from one unloaded attribute	Open in Colab Read Solutions
17	Migrations with Alembicautogenerate, upgrade, downgrade, and the batch mode SQLite makes you use	Open in Colab Read Solutions
18	Four Databases, One CodebasePostgreSQL, MySQL, SQL Server, and Oracle from one set of models, and the statements that still belong to one database	Open in Colab Read Solutions
19	Testing a Data LayerA fixture that builds the schema once, a transaction rolled back after every test, and the object that detached when the session closed	Open in Colab Read Solutions
20	A Complete Data LayerSchema, migration, load, and query in one script against one small database	Open in Colab Read Solutions