Getting all queries that django run on postgresql

Question

I am working on a django-postgresql project and I need to see every query that django run on database(so I can fine-tune queries). Is there a way to get those queries. Update: My development environment is on ubuntu linux

Answer 1

Check out this Question (and the two top most answers): django orm, how to view (or log) the executed query?

You can also have a look at the Djando documenation: https://docs.djangoproject.com/en/dev/faq/models/#how-can-i-see-the-raw-sql-queries-django-is-running

Hope this helps, Anton

Answer 2

You can decorate a request handler or other function with this and it will print the sql nicely formated with totals at the end.

from functools import wraps
from django.utils import termcolors
format_ok = termcolors.make_style(opts=('bold',), fg='green')
format_warning = termcolors.make_style(opts=('bold',), fg='yellow')
format_error = termcolors.make_style(opts=('bold',), fg='red')

try:
    from pygments import highlight
    from pygments.lexers import SqlLexer
    from pygments.formatters import TerminalFormatter
    pygments_sql_lexer = SqlLexer()
    pygments_terminal_formatter = TerminalFormatter()
    highlight_sql = lambda s: highlight(s, pygments_sql_lexer,
                               pygments_terminal_formatter)
except ImportError:
    highlight_sql = lambda s: s


def debug_sql(f):
    """
    Turn SQL statement debugging on for a test run.
    """
    @wraps(f)
    def wrapper(*a, **kw):
        from django.conf import settings
        from django.db import connection
        try:
            debug = settings.DEBUG
            settings.DEBUG = True
            connection.queries = []
            return f(*a, **kw)
        finally:
            total_time = 0
            for q in connection.queries:
                fmt = format_ok
                t = float(q['time'])
                total_time += t
                if t > 1:
                    fmt = format_error
                elif t > 0.3:
                    fmt = format_warning
                print '[%s] %s' % (fmt(q['time']), highlight_sql(q['sql']))
            print "total time =", total_time
            print "num queries =", len(connection.queries)
            settings.DEBUG = debug
    return wrapper

Answer 3

Try the django debug toolbar. It'll show you all the SQL executed over the request. When something is executing way too many queries, it becomes really slow, though. For that, I've been meaning to try out this profiler. However, I've rolled this middleware on a couple of projects:

try:
    from cStringIO import StringIO
except ImportError:
    import StringIO
from django.conf import settings
from django.db import connection

class DatabaseProfilerMiddleware(object):
    def can(self, request):
        return settings.DEBUG and 'dbprof' in request.GET

    def process_response(self, request, response):
        if self.can(request):
            out = StringIO()
            out.write('time     sql\n')
            total_time = 0
            for query in reversed(sorted(connection.queries, key=lambda x: x['time'])):
                total_time += float(query['time'])*1000
                out.write('%s %s\n' % (query['time'], query['sql']))
            response.content = '<pre style=&quot;white-space:pre-wrap&quot;>%d queries executed in %.3f seconds\n%s</pre>' \
                % (len(connection.queries), total_time/1000, out.getvalue())
        return response

Just go to the relevant URL for the request you are interested in and add a dbprof GET parameter, you'll see the profiling output instead of the normal response.

Answer 4

Well, you could just set the pgsql server to log every query. Or just to log the slow ones. Look in the postgresql.conf file, it's pretty close to self-documenting.