Django Print SQL Middleware

Django’s ob­ject-re­la­tion­al map­per (ORM) is per­haps that frame­work’s best fea­ture. It’s both easy to use and very power­ful. Nev­er­the­less, I be­lieve it’s im­port­ant to keep an eye on the quer­ies that it gen­er­ates. It’s of­ten easy to miss cases where you’ve un­in­ten­tion­ally triggered a new query when ac­cess­ing a for­eign key or when du­plic­ate quer­ies are be­ing gen­er­ated.

The Django De­bug Tool­bar is prob­ably the most widely used tool for peer­ing in­to Django’s data­base activ­ity. Per­son­ally, though, I’ve al­ways found that it tends to get in the way of the UI that I’m de­vel­op­ing. I’ve long pre­ferred to see the SQL out­put to the con­sole in­stead. I used to use Django devserv­er’s SQLRe­al­TimeModule for that pur­pose but devserv­er has been run­ning in­to com­pat­ib­il­ity prob­lems with re­cent ver­sions of Django. So, I’ve writ­ten my own mid­dle­ware based on the devserv­er mod­ule. I re­cently up­dated it to work with Django 1.10’s new style of mid­dle­ware, 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))
                    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 mid­dle­ware in de­vel­op­ment and nev­er in pro­duc­tion. It re­quires SQL­Parse to be in­stalled.