3

How to Log SQL Queries in Django

Posted June 20th, 2011 (Updated 29 Oct 2011) in Database, Python

Earlier today I completed my first project in Django and it came time to do some database optimization. I wanted to get a list of SQL queries executed for each page and a bit of Googling let me to this script on DjangoSnippets. It did everything I needed it to do, however I noticed it interfered with dynamically generated binary file outputs (such as the images made with django-simple-captcha). For this I needed to check if the output was in binary, and if so just return it without attempting to print the SQL log. I found what I was looking for here and after combining the two had the perfect SQL logger! Below is my finished code.

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
from django.db import connection
from django.template import Template, Context
import string 
#http://djangosnippets.org/snippets/161/class SQLLogMiddleware:
    def process_response ( self, request, response ):
        #Don't print SQL queries for binary outputs!        if istext(response.content) == 0:            return response 
        time = 0.0
        for q in connection.queries:
            time += float(q['time'])
 
        t = Template('''
            <p><em>Total query count:</em> {{ count }}<br/>
            <em>Total execution time:</em> {{ time }}</p>
            <ul class="sqllog">
                {% for sql in sqllog %}
                    <li>{{ sql.time }}: {{ sql.sql }}</li>
                {% endfor %}
            </ul>
        ''')
 
        response.content = "%s%s" % ( response.content, t.render(Context({'sqllog':connection.queries,'count':len(connection.queries),'time':time})))
        return response
 
#http://code.activestate.com/recipes/173220-test-if-a-file-or-string-is-text-or-binary/def istext(s):
    if "" in s:
        return 0
 
    if not s:  # Empty files are considered text
        return 1
 
    # Get the non-text characters (maps a character to itself then
    # use the 'remove' option to get rid of the text characters.)
    t = s.translate(string.maketrans("", ""), "".join(map(chr, range(32, 127)) + list("nrtb"))) 
    # If more than 30% non-text characters, then
    # this is considered a binary file
    if float(len(t))/len(s) >= 0.30:        return 0

To get this working on your site just add it to your MIDDLEWARE_CLASSES in settings.py and make DEBUG is set to True.

  • Aaron Bassett

    > sql_queries — A list of {‘sql’: …, ‘time’: …} dictionaries, representing every SQL query that has happened so far during the request and how long it took. The list is in order by query.

    https://docs.djangoproject.com/en/1.3/ref/templates/api/#django-core-context-processors-debug

  • chris

    Hey, I would like to add this to my development site. I have a few questions.

    1. How do i add this to my middleware classes? I put your code in a file in a folder in my project folder. ProjectFolder>Middleware>querystats.py Then I added ‘Projectfolder.middleware.querystats.SQLLogMiddleware’ to the middleware class list in settings.py. Is that correct?

    2. How do i call the code and get it to display in my template at the base of each page? Do i call it from my views and pass the dictionary over to the template?

    Thanks!

    • Flynsarmy

      Hey chris,

      1. If you put it in the folder Middleware you’ll need to use a capital M in your settings.py. ProjectFolder.Middleware.querystats.SQLLogMiddleware
      2. It’ll automatically appear at the bottom of every template