"""
$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 }}
| Time |
Seen |
SQL |
{% for sql in queries %}
| {{ sql.time }} |
{{ sql.seen }} |
{{ sql.sql }} |
{% endfor %}
''')
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