// the find
sqlalchemy/sqlalchemy
The Database Toolkit for Python
SQLAlchemy is the standard Python ORM and SQL toolkit, used everywhere from small Flask apps to large data pipelines. It gives you two distinct layers: a Core SQL expression language that maps closely to SQL semantics, and an ORM on top that implements identity map and unit-of-work patterns. If you're writing Python and touching a relational database, you're almost certainly either using this or explaining why you're not.
The Core/ORM split is the right design — you can write raw SQL expressions through Core without dragging in ORM overhead, or mix both in the same codebase without awkwardness. The identity map means you don't accidentally load the same row twice in a session, which prevents a whole class of subtle bugs that naive ORMs miss. The async support (via asyncio extension with greenlet) is real and production-tested, not bolted on. The dialect coverage is genuinely broad — PostgreSQL, MySQL, MariaDB, SQLite, MSSQL, Oracle — each with dialect-specific type extensions rather than a lowest-common-denominator abstraction.
The learning curve is steep and the docs, while thorough, require reading large sections before anything clicks — the Core vs ORM distinction trips up newcomers who assume it works like ActiveRecord. Session lifecycle management (when to commit, when to close, when you're in an expired state) is a common source of production bugs that SQLAlchemy's design doesn't prevent so much as document. The 1.x to 2.0 migration was painful and slow; `legacy_query` shims are still showing up in codebases years later, meaning you'll regularly encounter two incompatible query styles in the same project. Relationship loading strategies (lazy, eager, selectin, subquery) have real performance implications that are easy to get wrong and hard to debug without query logging turned on.