Thursday, February 25, 2016

Django query logging

Recently I had to optimise some piece of legacy code that worked slow. Among other refactoring procedures I wanted to check what queries gets invoked exactly. Having dug Django Docs and internets I came up with solution.

Using this, you'll get all executed queries in the console

For example:

2016-02-25 15:57:47 DEBUG    utils.py:89 (0.055) SET SQL_AUTO_IS_NULL = 0; args=None
2016-02-25 15:57:47 DEBUG    utils.py:89 (0.435) SELECT `table`.`id` FROM `table` ORDER BY `table`.`id` ASC LIMIT 10000; args=()

Add the code below to your local_settings.py (or whatever it's called in your project), make sure the include of this code is AFTER defining LOGGING variable.

DB_LOG = True

from settings import LOGGING
if DB_LOG:
    import logging, re

    class QueryFilter(logging.Filter):
        def filter(self, record):
            # exclude PyCharm debug window queries
            return 'LIMIT 21' not in getattr(record, 'sql', '')


    class SqlFormatter(logging.Formatter):
        def format(self, record):
            record.msg = re.sub(r"((?:u?')?\d+(?:u?')?)"
                                r"(?:,\s*(?:u?')?\d+(?:u?')?)+",
                                r'\1, ...', record.msg)
            return super(SqlFormatter, self).format(record)

    params = {
        'filters': {
            'db_query_filter': {
                '()': QueryFilter,
            },
        },
        'formatters': {
            'debugging': {
                '()': SqlFormatter,
                'format': '%(asctime)s %(levelname)-8s %(filename)s:%(lineno)d %(message)s',
                'datefmt' : '%Y-%m-%d %H:%M:%S',
            },
        },
        'handlers': {
            'db_console': {
                'level': 'DEBUG',
                'filters': ['require_debug_true', 'db_query_filter'],
                'class': 'logging.StreamHandler',
                'formatter': 'debugging',
            },
        },
        'loggers': {
            'django.db': {
                'level': 'DEBUG',
                'handlers': ['db_console'],
                'propagate': False,
            },
        }
    }
    for key, param in params.items():
        try:
            LOGGING[key].update(param)
        except KeyError:
            LOGGING[key] = params

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.