query for values based on date w/ Django ORM

Question

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)

I want this returned:

[10, 15, 0, 8]

or better yet, an aggregate of the total up to that point:

[10, 25, 25, 33]

I would be best to get this data directly from the database, but otherwise I can do the totaling pretty easily with a forloop.

I'm using Django's ORM and also Postgres

edit:

Just to note, that my example only covers a few days, but in practice, I have hundreds of objects covering a couple decades... What I'm trying to do is create a line graph showing how the sum of all my objects has grown over time (a very long time)

Answer 1

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.

Answer 2

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.

Answer 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]