Using Python's "operator" library to fetch lists from database tables

Say you have a model with an index_together of two fields

class Stuff(models.Model):
    color = models.CharField(max_length=20)
    thing = models.CharField(max_length=20)

    class Meta:
        unique_together = (('color', 'thing'), )
        index_together = [['color', 'thing'], ]

and a list of selector tuples. How to get a list of Stuff objects for the selectors?

li = [('blue', 'foo'), ('blue', 'bar'), ('blue', 'baz'),
      ('green', 'foo'), ('green', 'bar'), ('green', 'baz')]

An inefficient way would be

stuffs = [Stuff.objects.get(color=c, thing=t) for (c, t) in li]

that would execute one database call for each item in li. Python's "operator" library can help. It provides methods for all standard operators defined on objects, such as Object.__add__(), Object.__pow__(), Object.__or__(), etc.

>>> a, b = 5, 3
>>> a + b
8
>>> a.__add__(b)
8
>>> import operator
>>> operator.__add__(a, b)
8
>>> operator.add(a, b)
8
>>> operator.pow(a, b)
125
>>> operator.or_(a, b)
7

The methods can either be called as operator.__add__() or as operator.add() for convenience. Only the methods operator.or_() and operator.not_() come with a trailing underline.

Using operator.or_(), together with Django's Q() object and functools.reduce(), we can conveniently build a single query object to fetch all items on the li list in only one database call.

>>> from opreator import or_
>>> from functools import reduce
>>> from django.db.models import Q
>>> stuffs = Stuff.objects.filter(reduce(or_, [Q(color=c, thing=t) for c, t in li]))

That will call the Q.__or__() operator for each selector tuple and add it to the queryset, building one long query.

>>> str(stuffs.query)
'SELECT "some_stuff"."color", "some_stuff"."thing" FROM "some_stuff" 
WHERE (("some_stuff"."color" = blue AND "some_stuff"."thing" = foo) 
OR ("some_stuff"."color" = blue AND "some_stuff"."thing" = bar) 
OR ("some_stuff"."color" = blue AND "some_stuff"."thing" = baz) 
OR ("some_stuff"."color" = green AND "some_stuff"."thing" = foo) 
OR ("some_stuff"."color" = green AND "some_stuff"."thing" = bar) 
OR ("some_stuff"."color" = green AND "some_stuff"."thing" = baz))'

Source: http://stackoverflow.com/a/18615822/5520354