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 and less like object collections the more size and performance start to matter; object collections behave less and 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.

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.
Highly Modular / Non-Opinionated

Different parts of SQLAlchemy can be used independently of the rest. You can use the connection pool by itself and deal with raw connections; or you can use the SQL construction language by itself, either in direct conjunction with one or more database connections or as standalone constructs which return their string-compiled contents. While SQLAlchemy has a great ORM provided, the other parts have no dependency on it; its usage is completely optional. Simpler facades for the ORM can be used as well, such as the popular Elixir Declarative Layer and the SqlSoup Row Gateway. SQLAlchemy is architected in an open style that allows plenty of customization, supporting user-defined datatypes, custom SQL extensions, and a plugin system which can augment or extend any functionality including SQL expressions and ORM behavior.
Community Supported

SQLAlchemy has an active user community. The communities of both Pylons and Turbogears are largely involved with SQLAlchemy as it's the central ORM platform used by both frameworks; not to mention that there are no less than three integration frameworks available for Zope. The popular Elixir project provides a so-called "declarative" layer on top of SQLAlchemy which retains the feel of popular active record tools. SQLAlchemy itself includes several user-provided extensions, such as SqlSoup and orderinglist. Migrate is a popular add-on which adds "schema migration" support to SQLAlchemy.

SQLAlchemy is currently involved in integration projects (or is integrated) with the following systems:

The SQLAlchemy in the Wild page tracks a listing of all known integration tools as well as notable applications and websites which use SQLAlchemy.

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