"""
$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('''
<p>
<em>request.path:</em> {{ request.path|escape }}<br />
<em>Total query count:</em> {{ queries|length }}<br/>
<em>Total duplicate query count:</em> {{ duplicate }}<br/>
<em>Total SQL execution time:</em> {{ timesql }}<br/>
<em>Total Request execution time:</em> {{ timerequest }}<br/>
</p>
<table class="sqllog">
<tr>
<th>Time</th>
<th>Seen</th>
<th>SQL</th>
</tr>
{% for sql in queries %}
<tr>
<td>{{ sql.time }}</td>
<td align="right">{{ sql.seen }}</td>
<td>{{ sql.sql }}</td>
</tr>
{% endfor %}
</table>
''')
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'''<html><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8"><title>SQL Log %s</title></head><body><a href="./">Directory</a><br>%s</body></html>''' % (
request.path, html)
fd.write(html.encode('utf8'))
fd.close()
return response