Inspecting Django ORM Queries

As Django developers, we work mostly with the object-oriented Django ORM interface when querying data from our databases. However, under the hood, the Django ORM constructs SQL queries to interact with the database.

It can be useful to view the raw SQL for debugging performance issues or gaining a deeper understanding of how the ORM works. The Django ORM provides straightforward methods for this SQL introspection.

Examining Queries with str()

The simplest way to peek at the SQL is by casting the ORM’s QuerySet object to a string. For example:

str(MyModel.objects.filter(name__icontains="foo")) 

This will display the full text of the query that would be run, including parameters bound to the query.

However, a string representation of the query contains parameterized values rather than the actual data. So it won’t show the real values used in the query. Still, str() gives a quick overview of the query structure.

Inspecting SQL with query

The QuerySet class also provides a query attribute that holds a django.db.connection.queries.Query object representing the current query.

We can access this Query instance directly:

query = MyModel.objects.filter(name__icontains="foo").query
print(query)

The Query class has numerous attributes that give detailed metadata about the state of the current query. Some useful attributes include:

• sql – The actual SQL string that will be sent to the database
• params – The parameterized parameters that will be bound to the query
• tables – A list of tables involved
• where – The parsed WHERE clause of the query

Overall, query provides lower-level programmatic access to introspect the query.

Logging Queries during Requests

Writing print() statements each time can be verbose. Instead, Django can automatically log all SQL queries run during a request for easy debugging.

In settings.py, add:

LOGGING = {
    'loggers': {
        'django.db.backends': {
            'level': 'DEBUG',
            'handlers': ['console']
        }
    }
}

This will output all SQL queries to the console as they execute. The log output will contain the full text of the query, exact parameters used, and query duration.

When to Inspect SQL Queries

Here are some common use cases for inspecting SQL queries:

• Finding slow queries for optimization using EXPLAIN
• Verifying table joins are as expected
• Inspecting the exact SQL syntax dialect emitted for a database
• Learning more about advanced ORM behavior

Being able to inspect the raw SQL can deepen your grasp of Django DB operations. Both str() and query are easy enough to use anytime curiosity strikes while debugging or developing!