Wednesday, August 17, 2016
Django Print SQL Middleware
Django’s object-relational mapper (ORM) is perhaps that framework’s best feature. It’s both easy to use and very powerful. Nevertheless, I believe it’s important to keep an eye on the queries that it generates. It’s often easy to miss cases where you’ve unintentionally triggered a new query when accessing a foreign key or when duplicate queries are being generated.
The Django Debug Toolbar is probably the most widely used tool for peering into Django’s database activity. Personally, though, I’ve always found that it tends to get in the way of the UI that I’m developing. I’ve long preferred to see the SQL output to the console instead. I used to use Django devserver’s SQLRealTimeModule for that purpose but devserver has been running into compatibility problems with recent versions of Django. So, I’ve written my own middleware based on the devserver module. I recently updated it to work with Django 1.10’s new style of middleware, so I thought I’d share it:
import re
from decimal import Decimal
import sqlparse
from django.db import connection
_sql_fields_re = re.compile(r'SELECT .*? FROM')
_sql_aggregates_re = re.compile(r'SELECT .*?(COUNT|SUM|AVERAGE|MIN|MAX).*? FROM')
def truncate_sql(sql, aggregates=True):
if not aggregates and _sql_aggregates_re.match(sql):
return sql
return _sql_fields_re.sub('SELECT … FROM', sql)
def printsql_middleware(get_response):
def middleware(request):
response = get_response(request)
num_queries = 0
time = Decimal('0.0000')
for query in connection.queries:
num_queries += 1
time += Decimal(query['time'])
sql = truncate_sql(query['sql'], aggregates=False)
formatted_sql = sqlparse.format(sql, reindent=True)
for index, line in enumerate(formatted_sql.split('\n')):
if index == 0:
print('[SQL] {}'.format(line))
else:
print(' {}'.format(line))
deduped = set([q['sql'] for q in connection.queries])
print('[SQL] {} queries, {} duplicates. {} seconds.'.format(num_queries, num_queries - len(deduped), time))
return response
return middleware
Of course, you should only use this middleware in development and never in production. It requires SQLParse to be installed.