28 December 2016

How to view Django ORM SQL queries

Copy-paste this into your Python 3 interpreter to see a human-readable version of the raw SQL queries that your Django code is running.

from django.db import connection; import re
for i, query in enumerate(connection.queries):
    sql = re.split(r'(SELECT|FROM|WHERE|GROUP BY|ORDER BY|INNER JOIN|LIMIT)', query['sql'])
    if not sql[0]: sql = sql[1:]
    sql = [(' ' if i % 2 else '') + x for i, x in enumerate(sql)]
    print('\n### {} ({} seconds)\n\n{};\n'.format(i, query['time'], '\n'.join(sql)))

This is just a spruced up version of what is in the Django docs:

from django.db import connection

I kept mine short and a bit obfuscated looking so it takes up fewer lines and is easier to copy-paste.

Being able to view raw database queries is very important.

ORM’s (aka object relational mappers) allow one to write database code without having to actually touch the underlying SQL or whatever query language their database is using. This creates a nice decoupling between your code and database of choice and can speed up development. However, it also creates tons of risks that you’ll write terribly inefficient SQL, since you’re so removed from what is really happening.

Django’s ORM has a lot of abstracted ways to write intelligent queries (prefetch_related, select_related, only, deferred, values_list, remembering to actually index relevant columns, etc.), but it’s easy to screw it up.

There are some nice tools, like Django Debug Toolbar, to debug the queries that are used to render a page, but sometimes you want to just check from your interpreter—so copy-paste the above code into your python interpreter whenever you want to see what’s going on with your SQL queries.

*NOTE: the above example assume Python 3, hence the print-statement with parenthesis. Also, I do realize that if a query has any of my split tokens, e.g., "WHERE" inside a string, it will line break on that—and I’m OK with that for a quick and dirty debug tool.

Did you find this helpful or fun? paypal.me/mrcoles
comments powered by Disqus

Peter Coles

Peter Coles

is a software engineer living in NYC who is building Superset 💪 and also created GoFullPage 📸
more »

github · soundcloud · @lethys · rss