views:

786

answers:

3

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)

A: 
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.

Santi
+1  A: 

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.

Glenn Maynard
Django querysets are lazily evaluated, so you actually do end up running a separate query for each day.
David
Except the queryset here is only evaluated once, at the start of the for loop, so it's fine.
Wogan
Each time o.value is called you're hitting the database, so this hits the database for each day.
David
No, when objects.__iter__() is called, the ORM will hit the database and select all fields (date,value,etc) for all Obj objects that satisfy the filter conditions.
Wogan
You're badly confused, David. It does not hit the database for every o.value. It runs a single query which returns results in bulk.
Glenn Maynard
One other note: if o.date is a datetime rather than a date, you'll want `results[o.date.date()] = o.value`.
Glenn Maynard
A: 

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]
David
This answer fails to give a value for days that don't exist in the database.
Wogan
-1 for making false claims about my solution, then giving exactly the same solution I did (but in a less clear way).
Glenn Maynard
When I wrote this up it wasn't clear to me the DB would have missing dates. You're right, if the DB has missing dates then your solution is fine.
David