Django SQL Tracing Middleware

T
here are a number of tools within the Django framework itself as well as he community for inspecting all of the SQL [Django](http://djangoproject.com) is executing. The [Debug Toolbar](http://github.com/django-debug-toolbar/django-debug-toolbar) has be come the defacto standard in that respect. However, most all, if not all of the usual suspects are relying on template tags and rendered html templates / javascript to read the debug information. All fine and good, unless you are building an ajax application that never renders html or makes use of the template hooks.

The simple solution to this problem is to write a middleware class to spit out the sql generated by the database connection for the current request. And of course some color coding to make it look a little easier to read.

from django.db import connection 
from django.conf import settings
from sys import stdout

# mapping of ansi escape sequences
KEYWORDS = {
    "time":"\033[1;31m[%s]\033[0m",
    "text":"\033[2;37m%s\033[0m",
    "SELECT":'\033[1;36m%s\033[0m',
    "FROM":"\033[1;33m%s\033[0m",
    "INNER":"\033[1;31;40m%s\033[0m",
    "OUTER":"\033[1;34;40m%s\033[0m",
    "JOIN":"\033[1;31;40m%s\033[0m",
    "WHERE":"\033[1;32m%s\033[0m",
    "INSERT":"\033[0;35m%s\033[0m",
    "UPDATE":"\033[1;34m%s\033[0m",
    "DISTINCT":"\033[0;32m%s\033[0m",
    
}

# the middleware class
class TerminalLoggingMiddleware:
    def process_response(self, request, response):
        if settings.DEBUG and stdout.isatty():
            for query in connection.queries :
                _sql = query["sql"]
                _sql = [ KEYWORDS["time"] % query["time"] ] + _sql.split( )
                for index, item in enumerate( _sql ):
                    if KEYWORDS.has_key( item ):
                         _sql[index] = KEYWORDS[item] % item
                    else:
                         _sql[index] = KEYWORDS["text"] % item
                print u" ".join( _sql)
        return response

Obviously a pretty quick and dirty solution. It will only print out to the terminal if Django is in DEBUG mode and python has detected a device that supports terminal output. I was having a bit of trouble with string formatting and the ANSI escape chars and unicode - so keyword mapping just seemed to be the most simple and direct route. Quick, Dirty, and Insanely helpful.

django ansi tracing middleware sql