Query For Values Based On Date W/ Django Orm

I have a bunch of objects that have a value and a date field: obj1 = Obj(date='2009-8-20', value=10) obj2 = Obj(date='2009-8-21', value=15) obj3 = Obj(date='2009-8-23', value=8)

Solution 1:

This one isn't tested, since it's a bit too much of a pain to set up a Django table to test with:

from datetime import date, timedelta
# http://www.ianlewis.org/en/python-date-range-iterator
def datetimeRange(from_date, to_date=None):
    while to_date is None or from_date <= to_date:
        yield from_date
        from_date = from_date + timedelta(days = 1)

start = date(2009, 8, 20)
end = date(2009, 8, 23)
objects = Obj.objects.filter(date__gte=start)
objects = objects.filter(date__lte=end)

results = {}
for o in objects:
    results[o.date] = o.value

return [results.get(day, 0) for day in datetimeRange(start, end)]

This avoids running a separate query for every day.

Solution 2:

result_list = []
for day in range(20,24):    
    result = Obj.objects.get(date=datetime(2009, 08, day))
    if result:
        result_list.append(result.value)
    else:
        result_list.append(0)
return result_list

If you have more than one Obj per date, you'll need to check len(obj) and iterate over them in case it's more than 1.

Solution 3:

If you loop through a Obj.objects.get 100 times, you're doing 100 SQL queries. Obj.objects.filter will return the results in one SQL query, but you also select all model fields. The right way to do this is to use Obj.objects.values_list, which will do this with a single query, and only select the 'values' field.

start_date = date(2009, 8, 20)
end_date = date(2009, 8, 23)

objects = Obj.objects.filter(date__range=(start_date,end_date))
# values_list and 'value' aren't related. 'value' should be whatever field you're querying
val_list = objects.values_list('value',flat=True)
# val_list = [10, 15, 8]

To do a running aggregate of val_list, you can do this (not certain that this is the most pythonic way)

for i in xrange(len(val_list)):
    if i > 0:
        val_list[i] = val_list[i] + val_list[i-1]

# val_list = [10,25,33]

EDIT: If you need to account for missing days, @Glenn Maynard's answer is actually pretty good, although I prefer the dict() syntax:

objects = Obj.objects.filter(date__range=(start_date,end_date)).values('date','value')
val_dict = dict((obj['date'],obj['value']) for obj in objects)
# I'm stealing datetimeRange from @Glenn Maynard
val_list = [val_dict.get(day, 0) for day in datetimeRange(start_date, end_date)]
# val_list = [10,15,0,8]