Login

SQL Log Middleware + duplicates

Author:
guettli
Posted:
July 27, 2007
Language:
Python
Version:
1.3
Score:
2 (after 4 ratings)

This is based on Snippet 161 It marks duplicated SQL queries.

To avoid duplicates read:

Caching and Queryset

Sept. 07: Updated for current trunk: 'response' behaves like 'response.header' 22. October '07: Log into directory.

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
"""
$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

More like this

  1. Template tag - list punctuation for a list of items by shapiromatron 10 months, 1 week ago
  2. JSONRequestMiddleware adds a .json() method to your HttpRequests by cdcarter 10 months, 2 weeks ago
  3. Serializer factory with Django Rest Framework by julio 1 year, 5 months ago
  4. Image compression before saving the new model / work with JPG, PNG by Schleidens 1 year, 6 months ago
  5. Help text hyperlinks by sa2812 1 year, 6 months ago

Comments

daevaorn (on November 15, 2007):

Hmmm...As I know middleware instance shared between working threads, so start attribute may not be correspond to this request. I think that start must be assigned to request object.

#

guettli (on November 15, 2007):

Yes, you are right. Saving state information in the middleware objects is not good. This flag should be set on the request objects. I will update this snippet sometime.

#

Please login first before commenting.