Reinout van Rees - Querying Django models: fabulous & fast filtering

See the PyGrunn website for more info about this one-day Python conference in Groningen, The Netherlands.

Goal: show what is possible. Everything is in the Django documentation. Just remember a few things you see here.

Example case: time registration system. Everyone seems to do this. A Person belongs to a Group. A Booking belongs to a Project and a Person.

The Python ORM gives you a mapping between the database and Python.

standard:

Person.objects.all()

basic filtering:

Person.objects.filter(group=1)

specific name:

Person.objects.filter(group__name='Systemen')

case insensitive searching for part of a name:

Person.objects.filter(group__name__icontains='onderhoud')

name starting with:

Person.objects.filter(name__startswith='Reinout')

without group:

Person.objects.filter(group__isnull=True)

Filtering strategy:

• sometimes .exclude() is easier
• you can stack: .filter().filter().filter()
• query sets are lazy: only really executed at the moment you need it.
• just assign the query to a variable, to make complicated queries more understandable
• start with the model you want

Speed:

• select_related: does a big join in SQL so you get one set of results

• prefetch_related: does one query for one table, and then one query to get all related items

• if you need only one or two fields, Django does not need to instantiate a model, but can give you a plain dictionary or list instead:

  Person.objects.filter(group__name='Systemen').values('name', 'group__name')
  Person.objects.filter(group__name='Systemen').values_list('name', 'group__name')
  Person.objects.filter(group__name='Systemen').values_list('group__name', flat=True)
  

Annotation and aggregation:

• annotate: sum, count, avg
• aggregation
• groupby via values (bit of a weird syntax)

Aggregation gives totals:

from django.db.models import Sum
Booking.objects.filter(
    booked_by__group__name='Systemen'
).aggregate(Sum('hours'))

Annotation adds extra info to each result row:

Booking.objects.filter(
    booked_by__group__name='Systemen'
).annotate(Sum('bookings__hours'))[10].bookings__hour__sum

Group bookings by year, give sums:

Booking.objects.filter(
    booked_on__description__icontains='Zwanger'
).values('booked_by__name', 'year_week__year'
).annotate(Sum('hours'))

Practice this with your own code and data! You'll get the gang of it and get to know your data and it is fun.

If you need to do special queries, you can create a sub query yourself:

from django.db.models import Q
query = Q(group__name='Systemen')
Person.objects.filter(query)

You can write filters that way that are not in default Django.

Twitter: @reinoutvanrees