Using raw SQL in django
For a django project I’m working on, I need to run a simple query with
GROUP BY statements to collect some summary data. I tried working with the model API, and eventually fell back to using raw SQL instead.
Here’s the query I wanted to run:
SELECT (year/10)*10 as decade, count(*) as case_count FROM docket_case GROUP BY decade
One problem I encountered was there was no way (that I could find) to do a
GROUP BY. I found one reference to a group_by() method, but I don’t think the patch has been submitted or approved.
I spent some time trying to work out the right way to do it using the model API, before realizing I was banging my head on the wrong brick wall. I was using
values() and [STRIKEOUT:
extra(select='…') together, which should have clued me in right away. Well, that and the fact that the data I was retrieving wasn’t one of my model classes.
Using a tip from page 325 of the new django book (see my earlier
post) for a
review), I decided to just go ahead and run the query directly and
work with the result set. The results need to go into a template, so I
wanted to have dictionaries instead of the tuples I get from
cursor.fetchall(). This is what I came up with:
from itertools import * from django.db import connection def query_to_dicts(query_string, *query_args): """Run a simple query and produce a generator that returns the results as a bunch of dictionaries with keys for the column values selected. """ cursor = connection.cursor() cursor.execute(query_string, query_args) col_names = [desc for desc in cursor.description] while True: row = cursor.fetchone() if row is None: break row_dict = dict(izip(col_names, row)) yield row_dict return
You call it like:
results = query_to_dicts(""" SELECT (year/10)*10 as decade, count(*) as case_count FROM docket_case GROUP BY decade """)
or, with arguments (this query counts the cases in each year of a particular decade):
results = query_to_dicts(""" SELECT year, count(*) as case_count FROM docket_case WHERE year/10 = %s GROUP BY year """, decade/10)
I don’t expect a large result set, but I figured this was an excuse to experiment with generators and iterators. I’m pretty happy with the results, but surprised I had to write something like this myself. I didn’t see a
cursor method to fetch the rows as dictionaries instead of tuples, and the search I ran didn’t return anything that looked useful.
Am I missing something?
[Updated 31 Dec: One correction: The method is
extra(), the argument is select. I was adding a count field using
extra(), and then asking for the value with
values(). There does not seem to be a way to structure the query I wanted using the models API methods.]