Django 1.11 - how to print SQL statements with substituted variables

Question

This question is different than log all sql queries

I tried logging configurations from the answers above they are not working as I would like them to work, so please read on.

What I want to do, is to make Django (1.11.x) debug server log SQL queries in such a way, that I can redirect them to *.sql file and immediately execute.

For this I need a SQL statements where all the variables are already substituted, so I DON'T want this:

WHERE some_column in (:arg1, :arg2, ...)

but I want this instead:

WHERE some_column in ('actual_value_1', 'actual_value2', ...)

Can you please help me figure out how to do this?

Please note, that I don't want the SQL query to be printed in the browser (in some debug app like django_debug_toolbar) but printed to the console.
Please note, that I don't want to type Django QuerySet queries in console - I want to type URL in browser, so make an actual HTTP request to Django debug server and see it printing a SQL query in such a way I can execute it later using SQL Plus or any other console tools.

Possible duplicate of [log all sql queries](https://stackoverflow.com/questions/4375784/log-all-sql-queries) — Iain Shelvington, Jun 09 '17 at 14:29
Sorry @IainShelvington, I read both above threads and they don't answer my question. — mnowotka, Jun 09 '17 at 16:17

rabbit.aaron · Answer 1 · 2018-03-09T05:13:45.673

2

I think the tool I just made would be perfect for you.

https://pypi.python.org/pypi/django-print-sql

https://github.com/rabbit-aaron/django-print-sql

Install like this:

pip install --upgrade django-print-sql

Use like this in your view function:

from django_print_sql import print_sql

Class MyView(View):
    def get(self, request):
        with print_sql():
            User.objects.get(id=request.user.id)
            # and more....

edited Mar 09 '18 at 05:13

answered Mar 09 '18 at 05:08

rabbit.aaron

2,369
16
25

note: the count might not be accurate if multiple queries are executing across multiple threads. – rabbit.aaron Mar 09 '18 at 05:10
Looks great! I like a context manager idea. Do you have decorators as well? Would be handy when you want to get all SQLs printed from a function. – mnowotka Mar 10 '18 at 11:15
I might add it on the next version – rabbit.aaron Mar 14 '18 at 05:04
1

I've added the decorator in the 2018.3.6 version – rabbit.aaron Mar 15 '18 at 04:31

Iain Shelvington · Answer 2 · 2017-06-12T10:57:46.240

This is a bit of a hack, because we have to strip the default django.db.backends log messages of the time taken and the args. After making these changes you should have a file of pure SQL that you are free to run as you wish...

First you want to set up your logging settings to reference our new log handler

Settings:

LOGGING = {
    'version': 1,
    'handlers': {
        'sql': {
            'level': 'DEBUG',
            'class': 'my_project.loggers.DjangoSQLFileHandler',
            'filename': '/path/to/sql.log'
        },
    },
    'loggers': {
        'django.db.backends': {
            'handlers': ['sql'],
            'level': 'DEBUG'
        }
    }

}

Then you need to define your new handler and strip out the unwanted text from the message.

my_project.loggers.py:

from logging import FileHandler


class DjangoSQLFileHandler(FileHandler):

    def getMessage(self):
        msg = super(DjangoSQLFileHandler, self).getMessage()
        return record.msg.split(') ', 1)[1].split(';', 1)[0] + ';'

It doesn't really work, I'm getting an exception: https://gist.github.com/mnowotka/9edfbc040c91c91bc04a2f18a074d7ed — mnowotka, Jun 12 '17 at 10:33
I have updated my answer. My hack for changing the message was in the wrong place — Iain Shelvington, Jun 12 '17 at 10:58
I think you hack only works when you have a single place in SQL statement which refers to params. — mnowotka, Jun 12 '17 at 12:45

Django 1.11 - how to print SQL statements with substituted variables

2 Answers2