Django ORM operations compared with Peewee

April 21, 2017 10:37 / django peewee python / 1 comments

I saw a post in my weekly Python newsletter showing Django users how to execute common queries using SQLAlchemy. Here's the Peewee version.

Setup

Assume we have a Django model that looks like the following:

class Country(models.Model):
    name = models.CharField(max_length=255, unique=True)
    continent = models.CharField(max_length=50)

In Peewee, our table definition looks almost identical:

from peewee import *  # Peewee defines __all__, so import * is common practice.


class Country(Model):
    name = CharField(unique=True)  # Default max_length of 255.
    continent = CharField(max_length=50)

Because Peewee does not have a singleton, application-wide database configuration, we also need to associate our model with a database. A common convention, when working with more than one table, is to define a base Model class which configures the database. This will save you a lot of typing when you have more than one model:

from peewee import *
from playhouse.pool import PooledPostgresqlDatabase


db = PooledPostgresqlDatabase('my_app')


class BaseModel(Model):
    class Meta:
        database = db


class Country(BaseModel):
    name = CharField(unique=True)  # Default max_length of 255.
    continent = CharField(max_length=50)

To create tables, call the db.create_tables() method, passing in a list of tables. Peewee will re-order the tables so that they are created in the correct order if you have foreign-keys.

def create_tables():
    db.connect()
    db.create_tables([Country])
    db.close()  # Ensure we don't leave dangling connections.

Basic SELECTs

With Peewee, fetching all columns for all Countries looks like this:

countries = Country.select()
for country in countries:
    print country.name, 'is on', country.continent

With Django it looks quite similar:

countries = Country.objects.all()
for country in countries:
    print country.name, 'is on', country.continent

With Peewee, to select only the name of the country:

countries = Country.select(Country.name)
for country in countries:
    print country.name

The Django way is fairly similar, but behind-the-scenes it also selects the id column:

countries = Country.objects.only('name')

Filtering results using the WHERE clause

With Peewee, fetching only the name of countries in Europe, we write:

countries = Country.select(Country.name).where(Country.continent == 'Europe')

Django uses a keyword-argument hack:

countries = Country.objects.filter(continent='Europe').values('name')

More advanced filtering

If we want to find countries in Europe or Asia:

countries = (Country
             .select(Country.name)
             .where((Country.continent == 'Europe') |
                    (Country.continent == 'Asia')))

# Using "IN"
countries = (Country
             .select(Country.name)
             .where(Country.continent.in_(['Europe', 'Asia'])))

With Django, we introduce a special object called "Q":

from django.db.models import Q

countries = Country.objects.filter(Q(continent='Europe') |
                                   Q(continent='Asia'))

# Using "IN"
countries = Country.objects.filter(continent__in=['Europe', 'Asia'])

Grouping results

We want to get the number of countries per continent:

num_countries = fn.COUNT(Country.name).alias('count')
query = (Country
         .select(Country.continent, num_countries)
         .group_by(Country.continent))
for result in query:
    print '%s has %d countries' % (result.continent, result.count)

With Django we need to import a special "Count" function helper. The rows returned are dictionaries instead of model instances:

from django.db.models import Count

query = Country.objects.values('continent').annotate(count=Count('name'))
for result in query:
    print '%s has %d countries' % (result['continent'], result['count'])

Sorting rows

We want to get a list of countries, ordered by continent, then by name:

query = Country.select().order_by(Country.continent, Country.name)

With Django we use strings to reference the columns we're sorting by (this makes it more difficult to spot errors at runtime -- we'll get a strange SQL error if we make a typo, whereas Peewee will throw an AttributeError indicating the field is not valid).

query = Country.objects.all().order_by('continent', 'name')

Conclusion

Peewee maps more closely to SQL concepts, and it is internally consistent in its representation of tables, columns and functions. With Peewee, combining fields and functions follows predictable patterns. Django requires the use of special methods, special objects (like Q, Count and F), and has different semantics -- sometimes you must use keyword arguments, other times you use strings. Furthermore, those familiar with SQL will find themselves wondering how to translate a query into "Django" whereas with Peewee the APIs are very similar to their SQL counterparts. Although, as the author of Peewee, I'm probably a little biased in its favor.

For more thoughts on the poorly-designed Django ORM, check out Shortcomings in the Django ORM.

To learn more about Peewee, check out the quick-start guide or view an example Twitter-like site written using Peewee and Flask.

Comments (1)

Josh | apr 27 2017, at 01:53am

If I wasn't using Django, I'd definitely use PeeWee. I point people to it all the time when they're looking for a different ORM.

I'd just like to note that the lack of composability section in the shortcomings article has mostly been addressed with Expressions. They're slowly spreading to most functions of the ORM.

https://docs.djangoproject.com/en/1.11/ref/models/expressions/

This does lead to two different APIs for expressing the same thing, but as you pointed out, backwards compatibility is a major feature of Django, where deprecating something so fundamental would be awful for users.

When I added expressions I took a lot of inspiration from PeeWee, so thank you!

Commenting has been closed.