Login

Yet another SQL debugging facility

Author:
miracle2k
Posted:
August 16, 2007
Language:
Python
Version:
.96
Score:
3 (after 3 ratings)

Inspired by http://www.djangosnippets.org/snippets/159/

This context processor provides a new variable {{ sqldebug }}, which can be used as follows:

{% if sqldebug %}...{% endif %} {% if sqldebug.enabled %}...{% endif %}

This checks settings.SQL_DEBUG and settings.DEBUG. Both need to be True,
otherwise the above will evaluate to False and sql debugging is considered
to be disabled.

{{ sqldebug }}

This prints basic information like total number of queries and total time.

{{ sqldebug.time }}, {{ sqldebug.queries.count }}

Both pieces of data can be accessed manually as well.

{{ sqldebug.queries }}

Lists all queries as LI elements.

{% for q in sqldebug.queries %}
<li>{{ q.time }}: {{ q }}</li> {% endfor %}

Queries can be iterated as well. The query is automatically escaped and contains
<wbr> tags to improve display of long queries. You can use {{ q.sql }} to access
the unmodified, raw query string.

Here's a more complex example. It the snippet from: http://www.djangosnippets.org/snippets/93/ adjusted for this context processor.

{% if sqldebug %}
<div id="debug">
  <p>
    {{ sqldebug.queries.count }} Quer{{ sqldebug.queries|pluralize:"y,ies" }}, {{ sqldebug.time }} seconds
    {% ifnotequal sql_queries|length 0 %}
    (<span style="cursor: pointer;" onclick="var s=document.getElementById('debugQueryTable').style;s.display=s.display=='none'?'':'none';this.innerHTML=this.innerHTML=='Show'?'Hide':'Show';">Show</span>)
    {% endifnotequal %}
  </p>
  <table id="debugQueryTable" style="display: none;">
    <col width="1"></col>
    <col></col>
    <col width="1"></col>
    <thead>
    <tr>
      <th scope="col">#</th>
      <th scope="col">SQL</th>
      <th scope="col">Time</th>
    </tr>
    </thead>
    <tbody>
    {% for query in sqldebug.queries %}<tr class="{% cycle odd,even %}">
      <td>{{ forloop.counter }}</td>
      <td>{{ query }}</td>
      <td>{{ query.time }}</td>
    </tr>{% endfor %}
    </tbody>
  </table>
</div>
{% endif %}
 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
from django.conf import settings
import django.db as db
from django.utils.html import escape
import re

# sql queries can be quite long and don't contain spaces in their select clause,
# which makes them unwrappable in html.
# from: http://www.djangosnippets.org/snippets/93/
enable_linebreaks_regex = re.compile(",(?! )")
def enable_linebreaks(str):
    return enable_linebreaks_regex.sub(",<wbr>", str)

# wraps around a query dict as provided by django, to output the sql part per 
# default if accessed without an index.
class SqlQuery(object):
    def __init__(self, query):
        self.query = query
    def __getitem__(self, k):
        return self.query[k]
    # per default, return the sql query
    def __str__(self):
        return enable_linebreaks(escape(self['sql']))

# provides sqldebug.queries
class SqlQueries(object):
    def __iter__(self):
        for q in db.connection.queries:
            yield SqlQuery(q)

    def __len__(self):
        return len(db.connection.queries)
    def count(self):
        return len(self)
    
    # per default, output as list of LI elements
    def __str__(self):        
        result = ""
        for q in self:
            result += "<li>" + escape(q["sql"]) + "</li>\n"
        return result            

# main class for sql debugging info
class SqlDebug(object):
    def __init__(self):
        # allow access to database queries via attribute
        self.queries = SqlQueries()
        
    # per default, display some basic information
    def __str__(self):
        return "%d queries, %f seconds" % (self.queries.count(), self.time())
        
    # checks whether sql debugging has been enabled
    def enabled(self):        
        return getattr(settings, 'SQL_DEBUG', False) and \
               getattr(settings, 'DEBUG', False)
    # shortcurt to enabled()
    def __nonzero__(self):
        return self.enabled()
        
    # returns aggregate time for db operations as a double
    def time(self):
        secs = 0.0
        for s in self.queries:
            secs += float(s['time'])
        return secs

# context processor function: makes a SqlDebug instance available to templates.
def sqldebug(request):
    return {'sqldebug': SqlDebug()}

More like this

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

Comments

techiegurl (on May 5, 2008):

I love the idea that this is as simple as adding a new variable to the templates. Please forgive the newbie question though. To use this snippet, is adding it as a custom templatetag file enough?

Thank you in advance :)

#

Please login first before commenting.