[Django]-How to log all sql queries in Django?

Merge the following snippet with the LOGGING field in your 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'],
        }
    }
}

Tweaked from @acardenas89 answer

Add the following bold statements in settings.py

if DEBUG:
    import logging
    l = logging.getLogger('django.db.backends')
    l.setLevel(logging.DEBUG)
    l.addHandler(logging.StreamHandler())


LOGGING = {
    'version': 1,
    'disable_existing_loggers': False,
    'filters': {
        'require_debug_false': {
            '()': 'django.utils.log.RequireDebugFalse'
        }
    },
    'handlers': {
        'mail_admins': {
            'level': 'ERROR',
            'filters': ['require_debug_false'],
            'class': 'django.utils.log.AdminEmailHandler'
        },'console': {
            'level': 'DEBUG',
            'class': 'logging.StreamHandler',
        },
    },
    'loggers': {
        'django.request': {
            'handlers': ['mail_admins'],
            'level': 'ERROR',
            'propagate': True,
        },'django.db.backends.sqlite3': {
            'level': 'DEBUG',
            'handlers': ['console'],
        },
    }
}
  

Resource/Credit

To log SQL queries during testing, you need two things:

1. django.db.backends logger enabled and
2. @override_settings(DEBUG=True) decorator.

Test runner will set DEBUG=False by default, ignoring what you may have set in DJANGO_SETTINGS_MODULE.

The minimum settings:

# https://docs.djangoproject.com/en/dev/ref/settings/#logging
LOGGING = {
    'version': 1,
    'handlers': {
        'console': {
            'class': 'logging.StreamHandler',
        },
    },
    'loggers': {
        'django.db.backends': {
            'level': 'DEBUG',
        },
    },
    'root': {
        'handlers': ['console'],
    }
}

The example test case:

from django.contrib.auth.models import User
from django.test import TestCase, override_settings


class UserTests(TestCase):

    # To log queries in tests you need to manually override DEBUG setting
    # because testing sets DEBUG=False by default

    @override_settings(DEBUG=True)
    def test_create_user(self):
        User.objects.create()

Django 1.3 logs all SQL statements to django.db.backends logger:

https://docs.djangoproject.com/en/dev/ref/logging/#django-db-backends

You only need:

@override_settings(DEBUG=True)

if you already have SQL debug statements being printed in runserver.

Add the decorator to your class TestA(TestCase) or test_function:

@override_settings(DEBUG=True)
class TestA(TestCase):
...

    @override_settings(DEBUG=True)
    def test_function(self):
    ...

Credits to @Janusz Skonieczny’s answer!

In modern Django we got native way to hook and instrument sql queries without need to install extra dependencies. The OP asked for all queries, but this also can be of a high situational use.

import time

class QueryLogger:
    def __init__(self):
        self.queries = []

    def __call__(self, execute, sql, params, many, context):
        current_query = {"sql": sql, "params": params, "many": many}
        start = time.monotonic()
        try:
            result = execute(sql, params, many, context)
        except Exception as e:
            current_query["status"] = "error"
            current_query["exception"] = e
            raise
        else:
            current_query["status"] = "ok"
            return result
        finally:
            duration = time.monotonic() - start
            current_query["duration"] = duration
            self.queries.append(current_query)
(...)

# say, in Django Shell we would like to see 
# whether .exists() is faster than .count() 
# and what raw SQL it actually runs

import pprint
from django.db import connection
    
ql = QueryLogger()
with connection.execute_wrapper(ql):
    Book.objects.filter(author="Uncle Bob").exists()
    Book.objects.filter(author="Uncle Bob").count()

pprint.pprint(ql.queries)

If you want to have this toggle-able by a setting, do something like the following in settings.py:

if LOG_DB_QUERIES:
    LOGGING["handlers"]["console"] = {
        "level": "DEBUG", "class": "logging.StreamHandler"
    }
    LOGGING["loggers"]["django.db.backends"] =  {
        "level": "DEBUG", "handlers": ["console"]
    }
    

Also, please note that this will only work if you have DEBUG = True in settings.py.

Thanks to @Gian Marco for the logging config that makes this work.

I don’t know how to log all SQL queries in Django to a file.

But, I know how to use the code below to get the part of the SQL queries in Django Admin. *You can also see my answer explaining how to get the part of the SQL queries in Django View:

from django.db import connection
connection.queries

For example, you can use connection.queries in overridden save_model() in Person admin to get the SQL queries as shown below:

# "store/admin.py"

from .models import Person
from django.db import connection

@admin.register(Person)
class PersonAdmin(admin.ModelAdmin):
    # Here
    def save_model(self, request, obj, form, change):
        obj.save()
        for query in connection.queries: # Here
            print(query)

Then, if you change a person as shown below:

The SQL queries are printed on console as shown below:

{'sql': 'SELECT "django_session"."session_key", "django_session"."session_data", "django_session"."expire_date" FROM "django_session" WHERE ("django_session"."expire_date" > \'2022-12-24T06:47:45.799803+00:00\'::timestamptz AND "django_session"."session_key" = \'7spdc2c5h3g2v5hjc898eqphf11g9eck\') LIMIT 21', 'time': '0.000'}
{'sql': 'SELECT "account_customuser"."id", "account_customuser"."password", "account_customuser"."last_login", "account_customuser"."is_superuser", "account_customuser"."first_name", "account_customuser"."last_name", "account_customuser"."is_staff", "account_customuser"."is_active", "account_customuser"."date_joined", "account_customuser"."email", "account_customuser"."phone", "account_customuser"."my_order" FROM "account_customuser" WHERE "account_customuser"."id" = 1 LIMIT 21', 'time': '0.000'}
{'sql': 'SELECT "store_person"."id", "store_person"."name" FROM "store_person" WHERE "store_person"."id" = 191 LIMIT 21', 'time': '0.000'}
{'sql': 'UPDATE "store_person" SET "name" = \'David\' WHERE "store_person"."id" = 191', 'time': '0.000'}
[24/Dec/2022 15:47:45] "POST /admin/store/person/191/change/ HTTP/1.1" 302 0
[24/Dec/2022 15:47:46] "GET /admin/store/person/ HTTP/1.1" 200 22584
[24/Dec/2022 15:47:46] "GET /admin/jsi18n/ HTTP/1.1" 200 3195

You need to put this in a middleware package. The middleware sits between the webserver/django core and all your views. It can do preprocessing before the request, and postprocessing after the request completed. For example, save the queries to a file.