Django: Count objects in a particular month

I have this model:

class Person(models.Model):
   city = models.CharField(max_length=20, blank=True)
   added_date = models.DateField(default=datetime.date.today)

I want to create a template/view that has a table of months and the number of people added that month (ie, 5 in january, 10 in february, 8 in march, etc.). I have a similar table for all the people from a each city using:

cities = Patient.objects.values('city').annotate(city_count=Count('city')).order_by('-city_count')

I don't know how to do that for my months table. I could filter for a particular month, then count all. But then I'd need to run that through a loop over every month, which would be multiple database hits. I don't w开发者_C百科ant to do that.

Is there a way to do this without writing sql and just using django's api?

Its a very old thread, but i guess I'll answer in case someone else ended up here looking for a solution. The solution is for Django 1.10+ using the ExtractMonth function, for more detail visit official documentation

First you have to import ExtractMonth, like

from django.db.models.functions import ExtractMonth

Then using your Persons model, the code will be like this

personsMonthlyData = Person.objects.annotate(month=ExtractMonth('added_date')).values('month').annotate(count=Count('id')).order_by('month')

personsMonthlyData will output something like this

[{month: 1, count: 3}, {month: 2: count: 1}]

where month represent the month number e.g. 1 for January and 2 for February and the count against each month is assigned to the count item.

I hope this helps.

The fact that most DBs have such a perfect way of doing this with a GROUP BY query that has no analog in Django AFAIK would lead me to drop into SQL to do this. I did a google search for "django sql" and turned up this post by Doug Hellman on this exact problem: http://blog.doughellmann.com/2007/12/using-raw-sql-in-django.html. I would use that as a starting point for getting your month counts into Django.