How to log all sql queries in Django?

Question

How can I log all SQL queries that my django application performed?

I want to log everything, including SQLs from admin site. I saw this question and a FAQ answer but I still can't figure out where should I put

from django.db import connection
connection.queries

to log everything to one file?

So my question is - what should I do to have a file (say all-sql.log) where all SQL statements are logged?

Beane

#1074712 – Warehouse

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". – Conny

Also have a look at: Log SQL queries even if DEBUG=False – Unlimited

Answer 1

Maybe check out https://github.com/django-debug-toolbar/django-debug-toolbar

It'll let you see all the queries generated by a given page. As well as stacktraces of where they occur etc.

EDIT: to log all SQL queries to a file etc, then you will want to create some middleware. Middleware gets run on every request. There are several Django snippets out there for this sort of thing:

• http://djangosnippets.org/snippets/290/
• http://djangosnippets.org/snippets/264/

Those are concerned with printing to the terminal, but it wouldn't be hard to adapt them to use python's logging library.

Hamza

There's built-in logging / tracing capability without debugging: #19556639 – Idiot

Answer 2

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

Surcease

you may need to add the following to the handlers section in case you get Unable to add handler 'console': 'console' error: 'console':{ 'level':'DEBUG', 'class':'logging.StreamHandler', 'formatter': 'verbose', 'stream': sys.stderr, }, – Cecilececiley

I also needed 'version': 1, in the LOGGING dict. – Flyaway

Please note that DEBUG must be TRUE for logs to actually be logged. Regardless of logging settings. – Failure

Oh, and one more thing to in django test runner ignores settings and overrides DEBUG to False, so in test you must @override_settings(DEBUG=True) – Failure

I would also add 'propagate': False after the 'handlers': ['console'], line, in case you have a root logger enabled and don't know why this prints twice. Took me a bit to realize. – Monadelphous

so complicated just for such basic and common thing – Damalus

Answer 3

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

Vouge

You don't need both the if statement at the top and the LOGGING changes. The if statement is for if you want to add logging whilst e.g. in the shell, to turn it on immediately - all you need in settings.py is the LOGGING changes - and you might well want django.db.backends, not the sqlite3 specific one. – Conjugation

I don't see any queries on the console running django 1.9. DEBUG = True. – Warehouse

@CiroSantilli巴拿馬文件六四事件法轮功 This is a really old comment, very possibly Django 1.9 does not support this solution the same. – Vouge

In Django 1.9, the DEBUG setting is forced to be False when running tests. A workaround is to re-enable it in the test – Belshin

Answer 4

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

Failure

Answer 5

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

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

Moment

Answer 6

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!

Jewish

Answer 7

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)

Fraud

Answer 8

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.

Bindle

Answer 9

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

Conny

Answer 10

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.

Appledorf