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?

http://stackoverflow.com/questions/1074212/how-to-show-the-sql-django-is-running — Ciro Santilli OurBigBook.com, Apr 18 '17 at 06:25
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". — Super Kai - Kazuya Ito, Aug 17 '22 at 17:12
Also have a look at: [Log SQL queries even if DEBUG=False](https://stackoverflow.com/q/58396629) — djvg, Aug 25 '23 at 07:11

score 248 · Answer 1 · edited Aug 14 '16 at 07:41

248

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

edited Aug 14 '16 at 07:41

Community

1
1

answered Nov 23 '13 at 11:04

Gian Marco

22,140
8
55
44

3

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, },` – Don Grem Jan 20 '14 at 08:40
2

I also needed `'version': 1,` in the `LOGGING` dict. – Dan Aug 12 '16 at 08:38
20

Please note that [DEBUG must be TRUE](https://docs.djangoproject.com/en/1.11/topics/logging/#django-db-backends) for logs to actually be logged. Regardless of logging settings. – Janusz Skonieczny Jun 09 '17 at 17:50
9

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)` – Janusz Skonieczny Oct 19 '17 at 15:14
9

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. – Andrei-Niculae Petre Oct 20 '17 at 08:12

score 59 · Answer 2 · edited Jul 04 '13 at 18:19

59

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

edited Jul 04 '13 at 18:19

ionelmc

720
2
10
29

answered Aug 12 '12 at 14:20

cevaris

5,671
2
49
34

9

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. – M Somerville May 29 '13 at 10:30
1

I don't see any queries on the console running django 1.9. `DEBUG = True`. – Ciro Santilli OurBigBook.com May 19 '16 at 12:44
1

@CiroSantilli巴拿馬文件六四事件法轮功 This is a really old comment, very possibly Django 1.9 does not support this solution the same. – cevaris May 19 '16 at 17:32
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 – Mouscellaneous Feb 01 '17 at 16:15

score 26 · Answer 3 · edited Aug 25 '23 at 08:50

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

django.db.backends logger enabled and
@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()

score 23 · Accepted Answer · edited Mar 25 '13 at 13:12

23

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:

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

edited Mar 25 '13 at 13:12

alecxe

462,703
120
1,088
1,195

answered Dec 07 '10 at 11:35

John Montgomery

8,868
4
33
43

There's built-in logging / tracing capability without debugging: https://stackoverflow.com/questions/19556139/get-sql-query-count-during-a-django-shell-session# – Josiah Mar 04 '21 at 22:09

score 16 · Answer 5 · edited Nov 01 '22 at 14:42

16

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

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

edited Nov 01 '22 at 14:42

blueyed

27,102
4
75
71

answered Jun 12 '11 at 21:23

Tomasz Zieliński

16,136
7
59
83

score 2 · Answer 6 · answered Nov 12 '19 at 06:56

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!

score 1 · Answer 7 · answered Oct 28 '22 at 20:20

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.

score 1 · Answer 8 · answered Dec 24 '22 at 06:52

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

score 0 · Answer 9 · answered Dec 07 '10 at 16:16

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.

How to log all sql queries in Django?

9 Answers9

Linked