SQLAlchemy

SQLAlchemy is the database library most Python web apps use when they are not built on Django. It lets Python code define tables, build queries and map rows to objects across many databases without hand-built SQL strings.

It has two layers. Core handles engines, connection pooling, dialects and the SQL Expression Language, a way of composing SQL statements from Python objects and running them in a transaction. The ORM sits on top. You map classes to tables, and a Session tracks changes to those objects and turns them into INSERT, UPDATE and DELETE statements when you commit, a pattern called "unit of work". You can use Core alone or both together. Version 2.1 is the current release series and requires Python 3.11 or newer.

What a team gets: built-in dialects for PostgreSQL, MySQL and MariaDB, SQLite, Oracle and SQL Server, with external dialects for CockroachDB, ClickHouse, Redshift, Databricks and others; asyncio support for both Core and the ORM through async drivers such as asyncpg and aiosqlite; and fine control over how relationships load. Schema migrations are handled by Alembic, a separate tool from the same authors that can autogenerate migration scripts from your models. It is an MIT-licensed library that runs inside your app.

Limits: SQLAlchemy is large, and its ORM rewards learning how sessions, loading strategies and transactions interact. In async code, lazy-loaded relationships must be avoided or loaded explicitly, since attribute access cannot quietly run a query. Alembic's autogenerate needs manual review and cannot detect renames.