""" $Id: SQLLogMiddleware.py 2159 2011-11-03 15:29:49Z tguettler $ $HeadURL: svn+ssh://svnserver/svn/djangotools/trunk/middleware/SQLLogMiddleware.py $ This snippets adds a html table to html pages. The table shows the items of connection.queries. Duplicate SQL queries have a column which shows how many times the query was executed. http://djangosnippets.org/snippets/344/ # settings.py: DEBUG=True DEBUG_SQL=True # Since you can't see the output if the page results in a redirect, # you can log the result into a directory: # DEBUG_SQL='/mypath/...' MIDDLEWARE_CLASSES = ( 'YOURPATH.SQLLogMiddleware.SQLLogMiddleware', 'django.middleware.transaction.TransactionMiddleware', ...) The Django Debug Toolbar is better, except it does not show how many times a SQL statement was repeated in one request. """ # Python import os import time import datetime # Django from django.conf import settings from django.db import connection from django.template import Template, Context class SQLLogMiddleware: def process_request(self, request): request.sqllog_start=time.time() def process_response (self, request, response): # request.sqllog_start is empty if an append slash redirect happened. debug_sql=getattr(settings, "DEBUG_SQL", False) if (not request.sqllog_start) or not (settings.DEBUG and debug_sql): return response timesql=0.0 for q in connection.queries: timesql+=float(q['time']) seen={} duplicate=0 for q in connection.queries: sql=q["sql"] c=seen.get(sql, 0) if c: duplicate+=1 if c: q["seen"]=c+1 seen[sql]=c+1 t = Template('''

request.path: {{ request.path|escape }}
Total query count: {{ queries|length }}
Total duplicate query count: {{ duplicate }}
Total SQL execution time: {{ timesql }}
Total Request execution time: {{ timerequest }}

{% for sql in queries %} {% endfor %}
Time Seen SQL
{{ sql.time }} {{ sql.seen }} {{ sql.sql }}
''') timerequest=round(time.time()-request.sqllog_start, 3) queries=connection.queries html=t.render(Context(locals())) if debug_sql==True: if response.get("content-type", "").startswith("text/html"): response.write(html) del(response['ETag']) return response assert os.path.isdir(debug_sql), debug_sql outfile=os.path.join(debug_sql, "%s.html" % datetime.datetime.now().isoformat()) fd=open(outfile, "wt") html=u'''SQL Log %sDirectory
%s''' % ( request.path, html) fd.write(html.encode('utf8')) fd.close() return response