How can I see the raw SQL queries Django is running?

Question

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

Chronicles

https://mcmap.net/q/81366/-how-to-output-sql-generated-by-django-admin – Phototonus

Answer 1

See the docs FAQ: "How can I see the raw SQL queries Django is running?"

django.db.connection.queries contains a list of the SQL queries:

from django.db import connection
print(connection.queries)

Querysets also have a query attribute containing the query to be executed:

print(MyModel.objects.filter(name="my name").query)

Note that the output of the query is not valid SQL, because:

"Django never actually interpolates the parameters: it sends the query and the parameters separately to the database adapter, which performs the appropriate operations."

From Django bug report #17741.

Because of that, you should not send query output directly to a database.

If you need to reset the queries to, for example, see how many queries are running in a given period, you can use reset_queries from django.db:

from django.db import reset_queries
from django.db import connection

reset_queries()
# Run your query here
print(connection.queries)
>>> []

Reconstructionist

To future proof this answer you should rather link the current version of Django's documentation: docs.djangoproject.com/en/dev/faq/models/… – Goree

query attribute? what? where is that - i checked the link but it is a giant un-alphabetical (why would anyone make a list that isn't alphabetical?) list... – Narcis

Great answer. However, it is recommended to use the specified, builtin Pythonian str() function, which invokes the internal __str__() method. e.g. str(MyModel.objects.filter(name="my name").query) I would also recommend using IPython and the Django shell of your project. Tab completion then provides object introspection. As Django is known for its assertive naming schemes, this methodology tends to be very useful. – Sanderling

Note that the output of query is not valid SQL, because "Django never actually interpolates the parameters: it sends the query and the parameters separately to the database adapter, which performs the appropriate operations." Source: code.djangoproject.com/ticket/17741 – Unclean

@AndreMiller You should use stable, not dev, to link to the current version of Django, like this: docs.djangoproject.com/en/stable/faq/models/… – Kyser

django.db.connection.queries returns empty list – Exo

But does this work if you're using django rest framework and just hitting the API for a JS frontend, for example? Doesn't seem to for me. – Choctaw

Note that the DEBUG setting must be set to trueish for connection.queries to have anything in it, as stated in the Django documentation linked to the answer: "Make sure your Django DEBUG setting is set to True." – Inflammable

this does not work for and UPDATE statement, Django performs the update, returns a number of affected records and raises AttributeError: int object has no attribute query – Emaciated

@AndreMiller The answer should link to specific version at the time of answer. Pinning version makes it clear that the answer was written for that version. Also, sometimes documentation gets refactored and pages get deleted across versions, so “stable” or “dev” links result in 404. Even if version is pinned in the link, the reader can switch to a newer version if they want and the page still exists—Django docs make it exceedingly easy. – Lexi

I got "settings.DATABASES is improperly configured." error when using the code "from django.db import connection print(connection.queries)" in "settings.py" for "Django==3.1.7". – Dullard

Answer 2

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

Example:

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>

Manuscript

I am using it with --print-sql or with SHELL_PLUS_PRINT_SQL = True and it doesn't help - I still cannot see the queries. any idea why? django 1.8 – Craze

You need to set DEBUG = True in your settings.py to see queries – Godfrey

This answer is a godsend. – Riess

Yup, maybe not the answer OP needed but worked great for what I needed. – Roubaix

Answer 3

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

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

Chavannes

debug_toolbar is especially useful when you have a query that's failing with a SQL syntax error; it will display the last query that attempted to run (and failed), making it easier to debug. – Exuberant

The only thing is you see SQL queries on browser. If you run tests from terminal and wish to see it there, this is not a viable solution. Still great tho, I've been using it to this day. – Eye

Shows all queries as 'None' if run inside Docker. – Corps

@EugZol, take a look here, they mentioned Docker when setting up INTERNAL_IPS maybe that will help – Pithy

It doesn't seem to be showing any other queries than SELECT. Is there a way to enable those? – Apparent

Answer 4

The query is actually embedded in the models API:

q = Query.objects.values('val1','val2','val_etc')

print(q.query)

Bibby

Has this functionality been removed? It doesn't work when I do m = MyModel.objects.get(...) followed by m.query – Manx

That's because m is not a queryset anymore. Use q = MyModel.objects.filter(...), then q.query, then m = q.get(). – Castellated

Answer 5

No other answer covers this method, so:

I find by far the most useful, simple, and reliable method is to ask your database. For example on Linux for Postgres you might do:

sudo su postgres
tail -f /var/log/postgresql/postgresql-8.4-main.log

Each database will have slightly different procedure. In the database logs you'll see not only the raw SQL, but any connection setup or transaction overhead django is placing on the system.

Bister

don't forget to set log_statement='all' in postgresql.conf for this method. – Carpometacarpus

You can find your postgresql.conf by running psql -U postgres -c 'SHOW config_file' – Marcelinomarcell

Answer 6

This is a much late answer. But for whom the others came here by searching.

I want to introduce a logging method, which is very simple; add django.db.backends logger in settings.py

LOGGING = {
    'version': 1,
    'disable_existing_loggers': False,
    'handlers': {
        'console': {
            'class': 'logging.StreamHandler',
        },
    },
    'loggers': {
        'django.db.backends': {
            'handlers': ['console'],
            'level': 'DEBUG',
        },
    },
}

I am also using an environment variable to set the level. So when I want to see the SQL queries I just set the environment variable, and debug log shows the actual queries.

Blond

Answer 7

Though you can do it with the code supplied, I find that using the debug toolbar app is a great tool to show queries. You can download it from github here.

This gives you the option to show all the queries ran on a given page along with the time to query took. It also sums up the number of queries on a page along with total time for a quick review. This is a great tool, when you want to look at what the Django ORM does behind the scenes. It also have a lot of other nice features, that you can use if you like.

Reptant

Looks to me like this is the best version: github.com/django-debug-toolbar/django-debug-toolbar – Heisel

Answer 8

Another option, see logging options in settings.py described by this post

http://dabapps.com/blog/logging-sql-queries-django-13/

debug_toolbar slows down each page load on your dev server, logging does not so it's faster. Outputs can be dumped to console or file, so the UI is not as nice. But for views with lots of SQLs, it can take a long time to debug and optimize the SQLs through debug_toolbar since each page load is so slow.

Peruse

Excellent! While the toolbar looks great, I think this answer should be the accepted one. This is the solution I wanted because it lets "manage.py runserver" log SQL to the console and it works with "manage.py migrate". The latter let me see that "on delete cascade" was definitely not being set when my tables are created. It's worth noting that this answer is based on docs.djangoproject.com/en/1.9/topics/logging/… – Flavopurpurin

Answer 9

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 a 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 a 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: django-print-sql

Daytoday

I like this idea! I tried it out, but it doesn't add queries when you create or update an object, right? – Mev

yes, this is only for read quries for now, I haven't really maintained this project. The insert, update and delete are in a different compilers, django.db.models.sql.compiler.SQLInsertCompiler/SQLUpdateCompiler/SQLDeleteCompiler I might soon add the feature to count these too. Or you're welcome to contribute :D – Daytoday

Answer 10

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.

Undo

I just tried this, and (having removed the sql_time_sum part), got: No named cycles in template. 'odd,even' is not defined - what am I missing? – Octopus

Answer 11

Just to add, in Django, if you have a query like:

MyModel.objects.all()

do:

MyModel.objects.all().query.sql_with_params()

or:

str(MyModel.objects.all().query)

to get the SQL string.

Trinary

Answer 12

Django SQL Sniffer is another alternative for viewing (and seeing the stats of) raw executed queries coming out of any process utilising Django ORM. I've built it to satisfy a particular use-case that I had, which I haven't seen covered anywhere, namely:

• no changes to the source code that the target process is executing (no need to register a new app in django settings, import decorators all over the place etc.)
• no changes to logging configuration (e.g. because I'm interested in one particular process, and not the entire process fleet that the configuration applies to)
• no restarting of target process needed (e.g. because it's a vital component, and restarts may incur some downtime)

Therefore, Django SQL Sniffer can be used ad-hoc, and attached to an already running process. The tool then "sniffs" the executed queries and prints them to console as they are executed. When the tool is stopped a statistical summary is displayed with outlier queries based on some possible metric (count, max duration and total combined duration).

Here's a screenshot of an example where I attached to a Python shell

You can check out the live demo and more details on the github page.

Poetize

Simple and straightforward. Catches inserts and updates as well. – Broadtail

If you're on Ubuntu you'll need to run apt-get install gdb or this will fail. Additionally, I needed to attach the sniffer by PID before I did anything else in the shell or it wouldn't work. Other than those two caveats, it worked brilliantly. – Perfectible

Answer 13

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 '):]

Ecclesiastic

Answer 14

I put this function in a util file in one of the apps in my project:

import logging
import re

from django.db import connection

logger = logging.getLogger(__name__)

def sql_logger():
    logger.debug('TOTAL QUERIES: ' + str(len(connection.queries)))
    logger.debug('TOTAL TIME: ' + str(sum([float(q['time']) for q in connection.queries])))

    logger.debug('INDIVIDUAL QUERIES:')
    for i, query in enumerate(connection.queries):
        sql = re.split(r'(SELECT|FROM|WHERE|GROUP BY|ORDER BY|INNER JOIN|LIMIT)', query['sql'])
        if not sql[0]: sql = sql[1:]
        sql = [(' ' if i % 2 else '') + x for i, x in enumerate(sql)]
        logger.debug('\n### {} ({} seconds)\n\n{};\n'.format(i, query['time'], '\n'.join(sql)))

Then, when needed, I just import it and call it from whatever context (usually a view) is necessary, e.g.:

# ... other imports
from .utils import sql_logger

class IngredientListApiView(generics.ListAPIView):
    # ... class variables and such

    # Main function that gets called when view is accessed
    def list(self, request, *args, **kwargs):
        response = super(IngredientListApiView, self).list(request, *args, **kwargs)

        # Call our function
        sql_logger()

        return response

It's nice to do this outside the template because then if you have API views (usually Django Rest Framework), it's applicable there too.

Neckband

Answer 15

I believe this ought to work if you are using PostgreSQL:

from django.db import connections
from app_name import models
from django.utils import timezone

# Generate a queryset, use your favorite filter, QS objects, and whatnot.
qs=models.ThisDataModel.objects.filter(user='bob',date__lte=timezone.now())

# Get a cursor tied to the default database
cursor=connections['default'].cursor()

# Get the query SQL and parameters to be passed into psycopg2, then pass
# those into mogrify to get the query that would have been sent to the backend
# and print it out. Note F-strings require python 3.6 or later.
print(f'{cursor.mogrify(*qs.query.sql_with_params())}')

Stickney

This worked even in Python 2. Only a refactor like print(cursor.mogrify(*qs.query.sql_with_params())) is all it needs. – Nations

IIRC Cursor.mogrify returns a string, so I suppose the use of the f string for formatting is superfluous.. – Stickney

Answer 16

There's another way that's very useful if you need to reuse the query for some custom SQL. I've used this in an analytics app that goes far beyond what Django's ORM can do comfortably, so I'm including ORM-generated SQL as subqueries.

from django.db import connection
from myapp.models import SomeModel

queryset = SomeModel.objects.filter(foo='bar')

sql_query, params = queryset.query.as_sql(None, connection)

This will give you the SQL with placeholders, as well as a tuple with query params to use. You can pass this along to the DB directly:

with connection.connection.cursor(cursor_factory=DictCursor) as cursor:
    cursor.execute(sql_query, params)
    data = cursor.fetchall()

F

Answer 17

I've made a small snippet you can use:

from django.conf import settings
from django.db import connection


def sql_echo(method, *args, **kwargs):
    settings.DEBUG = True
    result = method(*args, **kwargs)
    for query in connection.queries:
        print(query)
    return result


# HOW TO USE EXAMPLE:
#
# result = sql_echo(my_method, 'whatever', show=True)

It takes as parameters a function (contains SQL queries) to inspect and args, kwargs needed to call that function. As the result, it returns what function returns and prints SQL queries in a console.

Eucharist

Answer 18

To get result query from django to database(with correct parameter substitution) you could use this function:

from django.db import connection

def print_database_query_formatted(query):
    sql, params = query.sql_with_params()
    cursor = connection.cursor()
    cursor.execute('EXPLAIN ' + sql, params)
    db_query = cursor.db.ops.last_executed_query(cursor, sql, params).replace('EXPLAIN ', '')

    parts = '{}'.format(db_query).split('FROM')
    print(parts[0])
    if len(parts) > 1:
        parts = parts[1].split('WHERE')
        print('FROM{}'.format(parts[0]))
        if len(parts) > 1:
            parts = parts[1].split('ORDER BY')
            print('WHERE{}'.format(parts[0]))
            if len(parts) > 1:
                print('ORDER BY{}'.format(parts[1]))

# USAGE
users = User.objects.filter(email='[email protected]').order_by('-id')
print_database_query_formatted(users.query)

Output example

SELECT "users_user"."password", "users_user"."last_login", "users_user"."is_superuser", "users_user"."deleted", "users_user"."id", "users_user"."phone", "users_user"."username", "users_user"."userlastname", "users_user"."email", "users_user"."is_staff", "users_user"."is_active", "users_user"."date_joined", "users_user"."latitude", "users_user"."longitude", "users_user"."point"::bytea, "users_user"."default_search_radius", "users_user"."notifications", "users_user"."admin_theme", "users_user"."address", "users_user"."is_notify_when_buildings_in_radius", "users_user"."active_campaign_id", "users_user"."is_unsubscribed", "users_user"."sf_contact_id", "users_user"."is_agree_terms_of_service", "users_user"."is_facebook_signup", "users_user"."type_signup" 
FROM "users_user" 
WHERE "users_user"."email" = '[email protected]' 
ORDER BY "users_user"."id" DESC

It based on this ticket comment: https://code.djangoproject.com/ticket/17741#comment:4

Bet

Answer 19

To generate SQL for CREATE / UPDATE / DELETE / commands, which are immediate in Django

from django.db.models import sql

def generate_update_sql(queryset, update_kwargs):
    """Converts queryset with update_kwargs
    like : queryset.update(**update_kwargs) to UPDATE SQL"""

    query = queryset.query.clone(sql.UpdateQuery)
    query.add_update_values(update_kwargs)
    compiler = query.get_compiler(queryset.db)
    sql, params = compiler.as_sql()
    return sql % params

from django.db.models import sql

def generate_delete_sql(queryset):
    """Converts select queryset to DELETE SQL """
    query = queryset.query.chain(sql.DeleteQuery)
    compiler = query.get_compiler(queryset.db)
    sql, params = compiler.as_sql()
    return sql % params

from django.db.models import sql

def generate_create_sql(model, model_data):
    """Converts queryset with create_kwargs
    like if was: queryset.create(**create_kwargs) to SQL CREATE"""
    
    not_saved_instance = model(**model_data)
    not_saved_instance._for_write = True

    query = sql.InsertQuery(model)

    fields = [f for f in model._meta.local_concrete_fields if not isinstance(f, AutoField)]
    query.insert_values(fields, [not_saved_instance], raw=False)

    compiler = query.get_compiler(model.objects.db)
    sql, params = compiler.as_sql()[0]
    return sql % params

Tests & usage

    def test_generate_update_sql_with_F(self):
        qs = Event.objects.all()
        update_kwargs = dict(description=F('slug'))
        result = generate_update_sql(qs, update_kwargs)
        sql = "UPDATE `api_event` SET `description` = `api_event`.`slug`"
        self.assertEqual(sql, result)

    def test_generate_create_sql(self):
        result = generate_create_sql(Event, dict(slug='a', app='b', model='c', action='e'))
        sql = "INSERT INTO `api_event` (`slug`, `app`, `model`, `action`, `action_type`, `description`) VALUES (a, b, c, e, , )"
        self.assertEqual(sql, result)

Preliminary

Answer 20

from django.db import reset_queries, connection
class ShowSQL(object):
    def __enter__(self):
        reset_queries()
        return self

    def __exit__(self, *args):
        for sql in connection.queries:
            print('Time: %s\nSQL: %s' % (sql['time'], sql['sql']))

Then you can use:

with ShowSQL() as t:
    some queries <select>|<annotate>|<update> or other 

it prints

• Time: %s
• SQL: %s

Repugn

Answer 21

You can use connection.queries to get the raw SQL queries running in Django as shown below:

# "store/views.py"

from django.db import transaction
from .models import Person
from django.db import connection
from django.http import HttpResponse

@transaction.atomic
def test(request):
    Person.objects.create(name="John") # INSERT
    
    qs = Person.objects.select_for_update().get(name="John") # SELECT FOR UPDATE
    qs.name = "Tom"
    qs.save() # UPDATE
    qs.delete() # DELETE
                 
    for query in connection.queries: # Here
        print(query)

    return HttpResponse("Test")

Then, the raw queries are printed on console as shown below:

{'sql': 'INSERT INTO "store_person" ("name") VALUES (\'John\') RETURNING "store_person"."id"', 'time': '0.000'}
{'sql': 'SELECT "store_person"."id", "store_person"."name" FROM "store_person" WHERE "store_person"."name" = \'John\' LIMIT 21 FOR UPDATE', 'time': '0.000'}      
{'sql': 'UPDATE "store_person" SET "name" = \'Tom\' WHERE "store_person"."id" = 179', 'time': '0.000'}
{'sql': 'DELETE FROM "store_person" WHERE "store_person"."id" IN (179)', 'time': '0.000'}
[24/Dec/2022 06:29:32] "GET /store/test/ HTTP/1.1" 200 9

Then, put reset_queries() after Person.objects.select_for_update() if you want to get only UPDATE and DELETE queries without INSERT and SELECT FOR UPDATE queries as shown below:

# "store/views.py"

from django.db import transaction
from .models import Person
from django.db import reset_queries
from django.db import connection
from django.http import HttpResponse

@transaction.atomic
def test(request):
    Person.objects.create(name="John") # INSERT
    
    qs = Person.objects.select_for_update().get(name="John") # SELECT FOR UPDATE
    reset_queries() # Here
    qs.name = "Tom"
    qs.save() # UPDATE
    qs.delete() # DELETE
                 
    for query in connection.queries: # Here
        print(query)

    return HttpResponse("Test")

Then, only UPDATE and DELETE queries are printed without INSERT and SELECT FOR UPDATE queries as shown below:

{'sql': 'UPDATE "store_person" SET "name" = \'Tom\' WHERE "store_person"."id" = 190', 'time': '0.000'}
{'sql': 'DELETE FROM "store_person" WHERE "store_person"."id" IN (190)', 'time': '0.000'}
[24/Dec/2022 07:00:01] "GET /store/test/ HTTP/1.1" 200 9

Dullard

Answer 22

Modifying your settings.py file:

# settings.py

LOGGING = {
    'version': 1,
    'formatters': {
        'verbose': {
            'format': '{levelname} {asctime} {module} {message}',
            'style': '{',
        },
    },
    'handlers': {
        'console': {
            'level': 'DEBUG',
            'class': 'logging.StreamHandler',
            'formatter': 'verbose',
        },
    },
    'loggers': {
        'django.db.backends': {
            'handlers': ['console'],
            'level': 'DEBUG',
            'propagate': True,
        },
    },
}

Then make sure that your application is running in DEBUG mode,

# settings.py

DEBUG = True

Run your app now and you will see SQL queries printed to the console.

Gangling

Answer 23

For those who are looking just the outcome from a query itself there is an easiest way:

Supposing we have a model called Musico:

class Musico(models.Model):
    INSTRUMENTOS = [
        ('violao', 'Violão'),
        ('piano', 'Piano'),
        ('cavaquinho', 'Cavaquinho'),
    ]
    usuario = models.OneToOneField(User, on_delete=models.DO_NOTHING, null=True)
    primeiro_nome = models.CharField(max_length=120)
    sobrenome = models.CharField(max_length=120, null=True, blank=True)
    tipo_instrumento = models.CharField(choices=INSTRUMENTOS, max_length=200)
    idade = models.IntegerField(null=True, blank=True)

    def __str__(self):
        return f"Musico: {self.primeiro_nome}"

To check the raw sql query would be like this:

>>> str(Musico.objects.all().query) 
'SELECT "model_lesson_app_musico"."id", "model_lesson_app_musico"."usuario_id", "model_lesson_app_musico"."primeiro_nome", "model_lesson_app_musico"."sobrenome", "model_lesson_app_musico"."tipo_instrumento", "model_lesson_app_musico"."idade" FROM "model_lesson_app_musico"'

Feuchtwanger

Answer 24

View Queries using django.db.connection.queries

from django.db import connection
print(connection.queries)

Access raw SQL query on QuerySet object

 qs = MyModel.objects.all()
 print(qs.query)

Interweave

Answer 25

For Django 2.2:

As most of the answers did not helped me much when using ./manage.py shell. Finally i found the answer. Hope this helps to someone.

To view all the queries:

from django.db import connection
connection.queries

To view query for a single query:

q=Query.objects.all()
q.query.__str__()

q.query just displaying the object for me. Using the __str__()(String representation) displayed the full query.

Togetherness

Answer 26

Several great answers here already.

One more way.

In test, do something like this:

with self.assertNumQueries(3):
    response = self.client.post(reverse('payments:pay_list'))
    # or whatever

If the number of queries is wrong, the test fails and prints all the raw SQL queries in the console.

Also, such tests help to control that the number of SQL queries does not grow as the code changes and the database load does not get excessive.

Staats