Django: how to view raw SQL queries

Joined:
04/09/2007: Posts:
565

November 13, 2009 16:59:10 Last update: November 13, 2009 20:27:04

When you set DEBUG=True in settings.py, Django saves a copy of every SQL statement it has executed in django.db.connection.queries:

>>> from django.db import connection
>>> connection.queries
[{'sql': 'SELECT polls_polls.id,polls_polls.question,polls_polls.pub_date FROM polls_polls',
'time': '0.002'}]

You can clear the list of saved queries by calling django.db.reset_queries():

>>> from django import db
>>> db.reset_queries()
>>>

Example:

from django.db import models

class Author(models.Model):
    name = models.CharField(max_length=50)
    email = models.EmailField()

    def __unicode__(self):
        return self.name

class Blog(models.Model):
    author = models.ForeignKey(Author)
    name = models.CharField(max_length=100)

    def __unicode__(self):
        return self.name

class Entry(models.Model):
    blog = models.ForeignKey(Blog)
    title = models.CharField(max_length=255)
    text = models.TextField()

    def __unicode__(self):
        return self.title

>>> a = Author(name='author', email='a@domain.com')
>>> a.save()
>>> b = Blog(author=a, name='First Blog')
>>> b.save()
>>> e = Entry(blog=b, title='The First Article', text='simply a test')
>>> e.save()
>>> from django.db import connection
>>> connection.queries
[{'time': '0.001', 'sql': u'INSERT INTO `djangotest_author` (`name`, `email`) VALUES (author, a@domain.com)'}, 
{'time': '0.001', 'sql': u'INSERT INTO `djangotest_blog` (`author_id`, `name`) VALUES (1, First Blog)'}, 
{'time': '0.001', 'sql': u'INSERT INTO `djangotest_entry` (`blog_id`, `title`, `text`) VALUES (1, The First Article, simply a test)'}]
>>> from django import db
>>> db.reset_queries()
>>> e2 = Entry.objects.get(pk=1)
>>> e2.blog
<Blog: First Blog>
>>> connection.queries
[{'time': '0.007', 'sql': u'SELECT `djangotest_entry`.`id`, `djangotest_entry`.`blog_id`, `djangotest_entry`.`title`, `djangotest_entry`.`text` FROM `djangotest_entry` WHERE `djangotest_entry`.`id` = 1 '}, 
{'time': '0.001', 'sql': u'SELECT `djangotest_blog`.`id`, `djangotest_blog`.`author_id`, `djangotest_blog`.`name` FROM `djangotest_blog` WHERE `djangotest_blog`.`id` = 1 '}]

Use select_related to fetch all related objects in one query:

>>> db.reset_queries()
>>> e2 = Entry.objects.select_related().get(pk=1)
>>> e2.blog
<Blog: First Blog>
>>> connection.queries
[{'time': '0.001', 'sql': u'SELECT `djangotest_entry`.`id`, `djangotest_entry`.`blog_id`, `djangotest_entry`.`title`, `djangotest_entry`.`text`, `djangotest_blog`.`id`, `djangotest_blog`.`author_id`, `djangotest_blog`.`name`, `djangotest_author`.`id`, `djangotest_author`.`name`, `djangotest_author`.`email` 
FROM `djangotest_entry` 
INNER JOIN `djangotest_blog` ON (`djangotest_entry`.`blog_id` = `djangotest_blog`.`id`) 
INNER JOIN `djangotest_author` ON (`djangotest_blog`.`author_id` = `djangotest_author`.`id`) 
WHERE `djangotest_entry`.`id` = 1 '}]

By default, select_related with attribute name only goes one level up:

>>> db.reset_queries()
>>> e2 = Entry.objects.select_related('blog').get(pk=1)
>>> e2.blog
<Blog: First Blog>
>>> connection.queries
[{'time': '0.001', 'sql': u'SELECT `djangotest_entry`.`id`, `djangotest_entry`.`blog_id`, `djangotest_entry`.`title`, `djangotest_entry`.`text`, `djangotest_blog`.`id`, `djangotest_blog`.`author_id`, `djangotest_blog`.`name` 
FROM `djangotest_entry` 
INNER JOIN `djangotest_blog` ON (`djangotest_entry`.`blog_id` = `djangotest_blog`.`id`) 
WHERE `djangotest_entry`.`id` = 1 '}]

[ Comment | Tags ]

Easy email testing with http://www.ximailstop.com

Ξ Notes

Django: how to view raw SQL queries