How to see the raw SQL queries Django is running?

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

reset_queries()
print(connection.queries)
>>> []

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

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

Though you can do it with 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.

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.

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

This is a much late answer but for the others are came here by searching.

I want to introduce a logging method, which is very simple; add django.db.backends logger in settins.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.

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.

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.

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())}')

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 function (contains sql queryies) 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.

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='admin@admin.com').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" = 'admin@admin.com' 
ORDER BY "users_user"."id" DESC

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

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()

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)

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.

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)

For Django, the best way to check the exact SQL queries is checking the logs of specific databases themselves.

For example, when developing Django website, I wanted to check the atomic transaction queries "BEGIN" and "COMMIT" with PostgreSQL(Version 14).

So, to generate the logs of exact PostgreSQL queries, I added the code below to the end of "postgresql.conf" which is "C:\Program Files\PostgreSQL\14\data\postgresql.conf" in my windows 11 machine:

log_min_duration_statement = 0

As shown below, I added the code above to the end of "postgresql.conf":

#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------

# Add settings for extensions here
log_min_duration_statement = 0

Then, after querying PostgreSQL queries with Django Admin, I could check the atomic transaction queries "BEGIN" and "COMMIT" with PostgreSQL as shown below by opening "postgresql-2022-08-20_000000.log" which is "C:\Program Files\PostgreSQL\14\data\log\postgresql-2022-08-20_000000.log" in my windows 11 machine:

2022-08-20 22:09:12.549 JST [26756] LOG:  duration: 0.025 ms  statement: BEGIN
2022-08-20 22:09:12.550 JST [26756] LOG:  duration: 1.156 ms  statement: SELECT "store_person"."id", "store_person"."first_name", "store_person"."last_name" FROM "store_person" WHERE "store_person"."id" = 33 LIMIT 21
2022-08-20 22:09:12.552 JST [26756] LOG:  duration: 0.178 ms  statement: UPDATE "store_person" SET "first_name" = 'Bill', "last_name" = 'Gates' WHERE "store_person"."id" = 33
2022-08-20 22:09:12.554 JST [26756] LOG:  duration: 0.784 ms  statement: INSERT INTO "django_admin_log" ("action_time", "user_id", "content_type_id", "object_id", "object_repr", "action_flag", "change_message") VALUES ('2022-08-20T13:09:12.553273+00:00'::timestamptz, 1, 20, '33', 'Bill Gates', 2, '[]') RETURNING "django_admin_log"."id"
2022-08-20 22:09:12.557 JST [26756] LOG:  duration: 1.799 ms  statement: COMMIT

In addition, I could also check the logs of exact MSSQL(Microsoft SQL Server) queries with MSSQL but I couldn't check the logs of exact SQLite queries with SQLite because it seems like SQLite doesn't have the function to save the logs of exact SQLite queries.

In addition again, I used @GianMarco's answer(solution) by adding the code below to "settings.py" to show the logs of exact SQL queries on console:

"settings.py"

LOGGING = {
    'version': 1,
    'filters': {
        'require_debug_true': {
            '()': 'django.utils.log.RequireDebugTrue',
        }
    },
    'handlers': {
        'console': {
            'level': 'DEBUG',
            'filters': ['require_debug_true'],
            'class': 'logging.StreamHandler',
        }
    },
    'loggers': {
        'django.db.backends': {
            'level': 'DEBUG',
            'handlers': ['console'],
        }
    }
}

But, it only showed the SQL queries "SELECT", "UPDATE", "INSERT" and so on without the atomic transaction queries "BEGIN" and "COMMIT" as shown below:

(0.000) SELECT "store_person"."id", "store_person"."first_name", "store_person"."last_name" FROM "store_person" WHERE "store_person"."id" = 33 LIMIT 21; args=(33,)
(0.000) UPDATE "store_person" SET "first_name" = 'Bill', "last_name" = 'Gates' WHERE "store_person"."id" = 33; args=('Bill', 'Gates', 33)
(0.000) INSERT INTO "django_admin_log" ("action_time", "user_id", "content_type_id", "object_id", "object_repr", "action_flag", "change_message") VALUES ('2022-08-20T13:55:19.226541+00:00'::timestamptz, 1, 20, '33', 'Bill Gates', 2, '[]') RETURNING "django_admin_log"."id"; args=(datetime.datetime(2022, 8, 20, 13, 55, 19, 226541, tzinfo=<UTC>), 1, 20, '33', 'Bill Gates', 2, '[]')

And, I used @geowa4's answer(solution) by adding the code below to "settings.py" to show the logs of exact SQL queries on console. *I used "Django==3.1.7":

# "settings.py"

from django.db import connection 

print(connection.queries) # Causes error

But, I got the error below:

django.core.exceptions.ImproperlyConfigured: settings.DATABASES is improperly configured. Please supply the ENGINE value. Check settings documentation for more details.

So, I removed "print(connection.queries)" as shown below, then the error was solved but I couldn't show any logs of exact SQL queries on console at all:

# "settings.py"

from django.db import connection 

# print(connection.queries) # Causes error