November 19, 2013 22:40 / 5 comments / django peewee python

I sat down and started working on a new library shortly after posting about Django's missing API for generating SQL. djpeewee is the result, and provides a simple translate() function that will recursively translate a Django model graph into a set of "peewee equivalents". The peewee versions can then be used to construct queries which can be passed back into Django as a "raw query".

Here are a couple scenarios when this might be useful:

I've included this module in peewee's playhouse, which is bundled with peewee.

Code samples

Suppose we have an Event model and want to find all events in the database that are longer than 2 hours:

class Event(models.Model):
    start = models.DateTimeField()
    end = models.DateTimeField()
    title = models.CharField(max_length=50)

two_hours = datetime.timedelta(hours=2)
Event.objects.filter(???)

With Django I believe you would have to use the extra() method. Edit: a helpful redditor pointed out this is not the case and you can use F() objects to write .filter(end__gt=F('start') + two_hours). At any rate, here is the equivalent with peewee:

from playhouse.djpeewee import translate
P = translate(Event)
query = (P.Event.select()
         .where((P.Event.end - P.Event.start) > two_hours))

What are P and query?

>>> P
{'Event': peewee.Event}

>>> query
<class 'peewee.Event'> SELECT t1."id", t1."start", t1."end", t1."title" FROM "events_event" AS t1 WHERE ((t1."end" - t1."start") > %s) [datetime.timedelta(0, 7200)]

So P is a container for all the auto-generated models, and query is a peewee SelectQuery object. By passing the query's SQL and parameters into django's raw() method, we can run this query and retrieve instances of our Django model:

>>> for event in Event.objects.raw(*query.sql()):
...     print event.title
Long event 1
Long event 2

Admittedly this is kind of a strange workflow. I will have to find a way to try it out in my work before I decide if I like it -- all those "interesting" queries I was working on last week would be good candidates! What do you think? Good idea, gross hack, or somewhere in between?

A more complicated example

Things get a bit more interesting when you bring foreign keys into the mix. The translate() function will automatically traverse explicit foreign keys and many-to-many relationships. Let's suppose we're working with the following models in our app:

class Genre(models.Model):
    name = models.CharField(max_length=20)

class Person(models.Model):
    name = models.CharField(max_length=20)

class Movie(models.Model):
    title = models.CharField(max_length=20)
    genres = models.ManyToManyField(Genre, related_name='movies')
    director = models.ForeignKey(Person, related_name='films')

Just by specifying Movie, translate() will automatically pick up the Person and Genre tables, as well as the intermediary "through" table used by the many-to-many relationship:

>>> translate(Movie)
{'Genre': <class 'peewee.Genre'>,
 'Movie': <class 'peewee.Movie'>,
 'Movie_genres': <class 'peewee.Movie_genres'>,
 'Person': <class 'peewee.Person'>}

If we attempt to traverse Person, though, we will only get that table because it has no 1-to-many relationships. By specifying backrefs=True we can force the translate() function to follow external foreign keys:

>>> translate(Person, backrefs=True)
{'Genre': <class 'peewee.Genre'>,
 'Movie': <class 'peewee.Movie'>,
 'Movie_genres': <class 'peewee.Movie_genres'>,
 'Person': <class 'peewee.Person'>}

For now things like generic foreign keys are ignored. I believe I've covered many common model structures, but please let me know if you try it out and encounter any problems.

Reading more

Thanks for reading the post, I hope you found it interesting. As always, feel free to leave a comment below.

Comments (5)

Josh | nov 2013, at 01:43am

Nice work! I've been looking into the lack of advanced sql operations in the django ORM, and something like this seems very useful.

I've been considering two options. First - trying to 'fix' the aggregation situation in the django ORM directly. The other is writing an adapter that would generate the parameters for .extra().

In the end, I think fixing the situation in django directly is going to be the best result. But after reading through all the failed pull requests, it seems like a really big project.

Charlie | nov 2013, at 10:10am

Thanks for your comment, Josh! I agree that the true fix should belong in Django, and a "wrapper" around extra() seems like a pretty flexible option. This PR adds arithmetic to aggregates and looks pretty nice, hopefully that will get merged.

Eric Carmichael | nov 2013, at 03:28pm

"What do you think? Good idea, gross hack, or somewhere in between?" I think it's an awesome idea, but admittedly pretty ugly.

I am pretty new to Django, hard to think of a more elegant way to do it... hrm...

Josh | nov 2013, at 03:38pm

I'm hoping so, too, but it doesn't look like it has too much attention. It's been sitting there for 3 months.

The one I'm most interested in is multiple F() expressions in an annotate/aggregate clause.

https://code.djangoproject.com/ticket/14030

I want to build, and have already mostly designed the spec for, an interactive report builder. But it's impossible to do with the django ORM without complex aggregates.

Charlie | nov 2013, at 04:05pm

"What do you think? Good idea, gross hack, or somewhere in between?" I think it's an awesome idea, but admittedly pretty ugly.

Haha, I'm glad you think it's a good idea, and yes I agree, it's pretty ugly. I haven't had a chance to use it in my work yet so I'm not sure whether or not I'll like it.


Commenting has been closed, but please feel free to contact me