Credit: hnphotog/Flickr
When you want to work with a relational database in Python, or most any other programming language, it's common to write database queries "by hand," using the SQL syntax supported by most databases.
This approach has its downsides, however. Hand-authored SQL queries can be clumsy to use since databases and software applications tend to live in separate conceptual worlds. It's hard to model how your app and your data work together.
Another approach is to use a library called an ORM, or object-relational mapping tool. ORMs let you describe how your database works through your application's code—what tables look like, how queries work, and how to maintain the database across its lifetime. The ORM handles all the heavy lifting for your database and you can concentrate on how your application uses the data.
This article introduces six ORMs for the Python ecosystem. All provide programmatic ways to create, access, and manage databases in your applications, and each one embodies a slightly different philosophy of how an ORM should work. Additionally, all of the ORMs profiled here will let you manually issue SQL statements if you so choose, for those times when you need to make a query without the ORM's help.
6 of the best ORMs for Python
- Django ORM
- Peewee
- PonyORM
- SQLAlchemy
- SQLObject
- Tortoise ORM
Django
The Django web framework comes with most everything you need to build professional-grade websites, including its own ORM and database management tools. Most people will only use Django's ORM with Django, but it is possible to use the ORM on its own. Also, Django's ORM has massively influenced the design of other Python ORMs, so it's a good starting point for understanding Python ORMs generally.
Models for a Django-managed database follow a pattern similar to other ORMs in Python. Tables are described with Python classes and Django's custom types are used to describe the fields and their behaviors. This includes things like one-to-many or many-to-many references with other tables, but also types commonly found in web applications like uploaded files. It's also possible to create custom field types by subclassing existing ones and using Django's library of generic field class methods to alter their behaviors.
Django's command-line management tooling for working with sites includes powerful tools for managing a project's data layer. The most useful ones automatically create migration scripts for your data, when you want to alter your models and migrate the underlying data to use the new models. Each change set is saved as its own migration script, so all migrations for a database are retained across the lifetime of your application. This makes it easier to maintain data-backed apps where the schema might change over time.
Peewee
Peewee has two big claims to fame. One, it's a small but powerful library, around 6,600 lines of code in a single module. Two, it's expressive without being verbose. While Peewee natively handles only a few databases, they're among the most common ones: SQLite, PostgreSQL, MySQL/MariaDB and CockroachDB.
Defining models and relationships in Peewee is a good deal simpler than in some other ORMs. One uses Python classes to create tables and their fields, but Peewee requires minimal boilerplate to do this and the results are highly readable and easy to maintain. Peewee also has elegant ways to handle situations like foreign key references to tables that are defined later in code, or self-referential foreign keys.
Queries in Peewee use a syntax that hearkens back to SQL itself; for example, Person.select(Person.name, Person.id).where(Person.age20). Peewee also lets you return the results as rich Python objects, as named tuples or dictionaries, or as a simple tuple for maximum performance. The results can also be returned as a generator, for efficient iteration over a large rowset. Window functions and CTEs (Common Table Expressions) also have first-class support.
Peewee uses many common Python metaphors beyond classes. For instance, transactions can be expressed by way of a context manager, as in with db.atomic():. You can't use keywords like and or not with queries, but Peewee lets you use operators like and ~ instead.
Sophisticated behaviors like optimistic locking and top n objects per group aren't supported natively, but the Peewee documentation has a useful collection of tricks to implement such things. Schema migration is not natively supported, but Peewee includes a SchemaManager API for creating migrations along with other schema-management operations.
PonyORM
PonyORM's standout feature is the way it uses Python's native syntax and language features to compose queries. For instance, PonyORM lets you express a SELECT query as a generator expression: query = select (u for u in User if u.name == "Davis").order_by(User.name). You can also use lambdas as parts of queries for filtering, as in query.filter(lambda user: user.is_approved is True). The generated SQL is also always accessible.
When you create database tables with Python objects, you use a class to declare the behavior of each field first, then its type. For instance, a mandatory, distinct name field would be name = Required(str, unique=True). Most common field types map directly to existing Python types, such as int/float/Decimal, datetime, bytes (for BLOB data), and so on. One potential point of confusion is that large text fields use PonyORM's LongStr type; the Python str type is basically the underlying database's CHAR.
PonyORM automatically supports JSON and PostgreSQL-style Array data types, as more databases now support both types natively. Where there isn't native support, PonyORM can often shim things up—for example, SQLite versions earlier than 3.9 can use TEXT to store JSON, but more recent versions can work natively via an extension module.
Some parts of PonyORM hew less closely to Python's objects and syntax. To describe one-to-many and many-to-many relationships in PonyORM, you use Set(), a custom PonyORM object. For one-to-one relationships, there are Optional() and Required() objects.
PonyORM has some opinionated behaviors worth knowing about before you build with it. Generated queries typically have the DISTINCT keyword added automatically, under the rationale that most queries shouldn't return duplicates anyway. You can override this behavior with the .without_distinct() method on a query.
A major omission from PonyORM's core is that there's no tooling for schema migrations yet, although it's planned for a future release. On the other hand, the makers of PonyORM offer a convenient online database schema editor as a service, with basic access for free and more advanced feature sets for US$9/month.
SQLAlchemy
SQLAlchemy is one of the best-known and most widely used ORMs. It provides powerful and explicit control over just about every facet of the database's models and behavior. SQLAlchemy 2.0, released early in 2023, introduced a new API and data modeling system that plays well with Python's type linting and data class systems.
SQLAlchemy uses a two-level internal architecture consisting of Core and ORM. Core is for interaction with database APIs and rendering of SQL statements. ORM is the abstraction layer, providing the object model for your databases. This decoupled architecture means SQLAlchemy can, in theory, use any number or variety of abstraction layers, though there is a slight performance penalty. To counter this, some of SQLAlchemy's components are written in C (now Cython) for speed.
SQLAlchemy lets you describe database schemas in two ways, so you can choose what's most appropriate for your application. You can use a declarative system, where you create Table() objects and supply field names and types as arguments. Or you can declare classes, using a system reminiscent of the way dataclasses work. The former is easier, but may not play as nicely with linting tools. The latter is more explicit and correct, but requires more ceremony and boilerplate.
SQLAlchemy values correctness over convenience. For instance, when bulk-inserting values from a file, date values have to be rendered as Python date objects to be handled as unambiguously as possible.
Querying with SQLAlchemy uses a syntax reminiscent of actual SQL queries—for example, select(User).where(User.name == "Davis"). SQLachemy queries can also be rendered as raw SQL for inspection, along with any changes needed for a specific dialect of SQL supported by SQLAlchemy (for instance, PostgreSQL versus MySQL). The expression construction tools can also be used on their own to render SQL statements for use elsewhere, not just as part of the ORM. For debugging queries, a handy echo=True options` lets you see SQL statements in the console as they are executed.
Various SQLAlchemy extensions add powerful features not found in the core or ORM. For instance, the "horizontal sharding" add-on transparently distributes queries across multiple instances of a database. For migrations, the Alembic project lets you generate change scripts with a good deal of flexibility and configuration.
