How can I see the raw SQL queries Django is runnin

Question

Is there a way to show the SQL that Django is running while performing a query?

Answer 1

The following returns the query as valid SQL, based on https://code.djangoproject.com/ticket/17741:

def str_query(qs):
    """
    qs.query returns something that isn't valid SQL, this returns the actual
    valid SQL that's executed: https://code.djangoproject.com/ticket/17741
    """
    cursor = connections[qs.db].cursor()
    query, params = qs.query.sql_with_params()
    cursor.execute('EXPLAIN ' + query, params)
    res = str(cursor.db.ops.last_executed_query(cursor, query, params))
    assert res.startswith('EXPLAIN ')
    return res[len('EXPLAIN '):]

Answer 2

If you make sure your settings.py file has:

1. django.core.context_processors.debug listed in CONTEXT_PROCESSORS
2. DEBUG=True
3. your IP in the INTERNAL_IPS tuple

Then you should have access to the sql_queries variable. I append a footer to each page that looks like this:

{%if sql_queries %}
  <div class="footNav">
    <h2>Queries</h2>
    <p>
      {{ sql_queries|length }} Quer{{ sql_queries|pluralize:"y,ies" }}, {{sql_time_sum}} Time
    {% ifnotequal sql_queries|length 0 %}
      (<span style="cursor: pointer;" onclick="var s=document.getElementById('debugQueryTable').style;s.disp\
lay=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 sql_queries %}
          <tr class="{% cycle odd,even %}">
            <td>{{ forloop.counter }}</td>
            <td>{{ query.sql|escape }}</td>
            <td>{{ query.time }}</td>
          </tr>
        {% endfor %}
      </tbody>
    </table>
  </div>
{% endif %}

I got the variable sql_time_sum by adding the line

context_extras['sql_time_sum'] = sum([float(q['time']) for q in connection.queries])

to the debug function in django_src/django/core/context_processors.py.

Answer 3

I developed an extension for this purpose, so you can easily put a decorator on your view function and see how many queries are executed.

To install:

$ pip install django-print-sql

To use as context manager:

from django_print_sql import print_sql

# set `count_only` to `True` will print the number of executed SQL statements only
with print_sql(count_only=False):

  # write the code you want to analyze in here,
  # e.g. some complex foreign key lookup,
  # or analyzing a DRF serializer's performance

  for user in User.objects.all()[:10]:
      user.groups.first()

To use as decorator:

from django_print_sql import print_sql_decorator


@print_sql_decorator(count_only=False)  # this works on class-based views as well
def get(request):
    # your view code here

Github: https://github.com/rabbit-aaron/django-print-sql

Answer 4

Take a look at debug_toolbar, it's very useful for debugging.

Documentation and source is available at http://django-debug-toolbar.readthedocs.io/.

Answer 5

I was able to see the failed queries simply by doing:

tail -f /var/log/postgresql/*

This assumed that postgres 10.6, ubuntu 18.04+, python3+, django2+, and that logging has been enabled in postgres.

Answer 6

Django-extensions have a command shell_plus with a parameter print-sql

./manage.py shell_plus --print-sql

In django-shell all executed queries will be printed

Ex.:

User.objects.get(pk=1)
SELECT "auth_user"."id",
       "auth_user"."password",
       "auth_user"."last_login",
       "auth_user"."is_superuser",
       "auth_user"."username",
       "auth_user"."first_name",
       "auth_user"."last_name",
       "auth_user"."email",
       "auth_user"."is_staff",
       "auth_user"."is_active",
       "auth_user"."date_joined"
FROM "auth_user"
WHERE "auth_user"."id" = 1

Execution time: 0.002466s [Database: default]

<User: username>