How to get SQL from Django

Sometimes developers complain that Django’s ORM is “too high-level” and that they don’t understand which ORM query will result in which SQL. Django allows to examine underlying SQL, but some developers still do not know how to use provided mechanisms.

First, let’s define model:

from django.db import models


class Question(models.Model):
    title = models.CharField(max_length=200)
    some_field = models.IntegerField(db_index=True)

    class Meta:
        app_label = 'myapp'

    def __unicode__(self):
        return self.title

Now we can execute simple query:

>>> qs = Question.objects.order_by('some_field')
>>> qs
[]

django.db.models.query.QuerySet has a query attribute, which is an instance of django.db.models.sql.query.Query class. The latter has __str__ method that returns actual SQL query as a string:

>>> qs.query
<django.db.models.sql.query.Query at 0x...>
>>> str(qs.query)
'SELECT "myapp_question"."id", "myapp_question"."title", "myapp_question"."some_field" FROM "myapp_question" ORDER BY "myapp_question"."some_field" ASC'

Let's try this on, say, count():

>>> str(Question.objects.count().query)
---------------------------------------------------------------------------
AttributeError                            Traceback (most recent call last)
django/core/management/commands/shell.pyc in <module>()
----> 1 str(Question.objects.count().query)

AttributeError: 'int' object has no attribute 'query'

What happened? count method returns integer (or long integer), and, as you can see, returned value has no Django-related methods:

>>> dir(Question.objects.count())
[
    '__abs__',
    '__add__',
    '__and__',
    '__class__',
    '__cmp__',
    '__coerce__',
    '__delattr__',
    '__div__',
    '__divmod__',
    '__doc__',
    '__float__',
    '__floordiv__',
    '__format__',
    '__getattribute__',
    '__getnewargs__',
    '__hash__',
    '__hex__',
    '__index__',
    '__init__',
    '__int__',
    '__invert__',
    '__long__',
    '__lshift__',
    '__mod__',
    '__mul__',
    '__neg__',
    '__new__',
    '__nonzero__',
    '__oct__',
    '__or__',
    '__pos__',
    '__pow__',
    '__radd__',
    '__rand__',
    '__rdiv__',
    '__rdivmod__',
    '__reduce__',
    '__reduce_ex__',
    '__repr__',
    '__rfloordiv__',
    '__rlshift__',
    '__rmod__',
    '__rmul__',
    '__ror__',
    '__rpow__',
    '__rrshift__',
    '__rshift__',
    '__rsub__',
    '__rtruediv__',
    '__rxor__',
    '__setattr__',
    '__sizeof__',
    '__str__',
    '__sub__',
    '__subclasshook__',
    '__truediv__',
    '__trunc__',
    '__xor__',
    'bit_length',
    'conjugate',
    'denominator',
    'imag',
    'numerator',
    'real',
]

But how do we get SQL for this query? Again, Django helps you:

>>> Question.objects.count()
0
>>> from django import db
>>> db.connection.queries
[{'sql': 'SELECT COUNT(*) FROM "myapp_question"', 'time': '0.000'}]

Same if you use multiple databases:

>>> Question.objects.count()
0
>>> from django import db
>>> db.connections['dbname'].queries
[{'sql': 'SELECT COUNT(*) FROM "myapp_question"', 'time': '0.000'}]

When you have DEBUG = True in your settings, Django puts every SQL statement into corresponding connection.queries list in order of query execution. I guess you noticed time key, it is how long it took to execute statement (in seconds). Such helpful behaviour led to myth that Django leaks memory.

When you experiment in shell, it’s okay to use this. But what about regular request-response scenario? Well, you can use prints, logging, but most developers use either Django debug toolbar or add something like this into templates:

{% if debug %}
  <table>
    <thead>
      <tr>
        <th>Time</th>
        <th>Query</th>
      </tr>
    </thead>
    <tbody>
      {% for q in sql_queries %}
        <tr>
          <td>{{ q.time }}</td>
          <td>{{ q.sql }}</td>
        </tr>
      {% endfor %}
    </tbody>
  </table>
{% endif %}

The latter works because of django.core.context_processors.debug context processor which adds debug and sql_queries context variables (if settings.DEBUG and request.META.get('REMOTE_ADDR') in settings.INTERNAL_IPS).

What about DDL? Simple: use management commands. It is much easier to change model’s code, generate SQL via command and create migration just by comparing old DB schema and a new one (of course, you’ll need to add ALTER TABLE, etc.) than to try to satisfy South or other similar tool.