The Python SQL Toolkit and Object Relational Mapper

SQLAlchemy is the Python SQL toolkit and Object Relational Mapper that gives application developers the full power and flexibility of SQL.

It provides a full suite of well known enterprise-level persistence patterns, designed for efficient and high-performing database access, adapted into a simple and Pythonic domain language.

SQLALCHEMY'S PHILOSOPHY

SQL databases behave less like object collections the more size and performance start to matter; object collections behave less like tables and rows the more abstraction starts to matter. SQLAlchemy aims to accommodate both of these principles.

SQLAlchemy doesn't view databases as just collections of tables; it sees them as relational algebra engines. Its object relational mapper enables classes to be mapped against the database in more than one way. SQL constructs don't just select from just tables—you can also select from joins, subqueries, and unions. Thus database relationships and domain object models can be cleanly decoupled from the beginning, allowing both sides to develop to their full potential.

The main goal of SQLAlchemy is to change the way you think about databases and SQL!

Most importantly, SQLAlchemy is not just an ORM. Its data abstraction layer allows construction and manipulation of SQL expressions in a platform agnostic way, and offers easy to use and superfast result objects, as well as table creation and schema reflection utilities. No object relational mapping whatsoever is involved until you import the orm package. Or use SQLAlchemy to write your own !

Read what people are saying about SQLAlchemy.

Scales Up

Powerful enough for complicated tasks, such as:

  • Eager-load a graph of objects and their dependencies via joins
  • Map recursive adjacency structures automatically
  • Map objects to not just tables but to any arbitrary join or select statement
  • Combine multiple tables together to load whole sets of otherwise unrelated objects from a single result set
  • Commit entire graphs of object changes in one step
  • Interface with multiple databases simultaneously
  • Use real two phased transactions as well as "nested" transactions via savepoints, in a database-agnostic manner
Scales Down

Extremely easy to use for all the basic tasks, such as:

  • Constructing SQL from Python expressions
  • Pooling database connections
  • Loading objects from the database and saving changes back
DBA Approved

Built to conform to what DBAs demand, including the ability to swap out generated SQL with hand-optimized statements, full usage of bind parameters for all literal values, fully transactionalized and batched database writes using the Unit of Work pattern. All object-relational patterns are designed around the usage of proper referential integrity, and foreign keys are an integral part of its usage.
Non-Opinionated

SQLAlchemy places the highest value on not getting in the way of database and application architecture. Unlike many tools, it never "generates" schemas (not to be confused with issuing user-defined DDL, in which it excels) or relies on naming conventions of any kind. SQLAlchemy supports the widest variety of database and architectural designs as is reasonably possible.

Modular and Extensible

Different parts of SQLAlchemy can be used independently of the rest. Elements like connection pooling, SQL statement compilation and transactional services can be used independently of each other, and can also be extended through various plugin points. The Object Relational Mapper (ORM) is a separate package which builds on top of these, and itself has several extension systems for modifying behavior at various levels.

SQLAlchemy's extensible nature has spawned a whole community of plugins and third party extensions, including:
Community Supported

SQLAlchemy has a substantially sized community, with over 1200 mailing list users and well over 3000 downloads a month. It is the default ORM for most of the popular web frameworks, including Pylons, Turbogears and Grok. Integration projects exist for Django, Trac, Trellis, and Zope as well. It's used in a growing number of applications and third party add ons (see the current known list).

Development is similarly active, with an active developer count hovering around ten, around 200 commits per month and a typical release pace of one point release per month, one major version release every six months (see SQLAlchemy Trac for development timeline and project information).

Check out some of the key features or dive straight into the tutorial — then download!