Django — Log SQL queries

Photo by Campaign Creators on Unsplash

We all know that Django uses ORM for executing queries instead of direct SQL queries. Behind the scenes, the ORM queries are converted to equivalent SQL queries and then executed. If you have no idea what an ORM is, let me just explain it quickly with an example.

What is an ORM?

The Django web framework includes a default object-relational mapping layer (ORM) that can be used to interact with application data from various relational databases such as SQLite, PostgreSQL, and MySQL.

Example:

Let us say if we want to query all the students from a database. For this purpose with the help of ORM, we would do something like this.

>>> from student.models import Student
>>> Student.objects.all()
<QuerySet [<Student: John>]>

The equivalent SQL syntax for this is (The above Django ORM query will be converted to the following syntax before communicating with the database)

SELECT "student_student"."id", "student_student"."name", "student_student"."school_id" FROM "student_student" LIMIT 21; args=()

I hope now you have an idea about the ORM concept.

Logging SQL queries

Suppose if you would like to log these SQL queries Django provides a way to do that. First, let me show you how to log the queries and then explain why you should log in the latter part of this article.

Simply add the following code to the settings.py file in your Django project.

LOGGING = {
    'version': 1,
    'disable_existing_loggers': False,
    'handlers': {
        'file': {
            'level': 'DEBUG',
            'class': 'logging.FileHandler',
            'filename': 'sql.log',
        },
    },
    'loggers': {
        'django.db.backends': {
            'handlers': ['file'],
            'level': 'DEBUG',
            'propagate': True,
        },
    },
}

This code will log your executed SQL queries in a file called ‘sql.log’. Every time you perform any operations on the database, it will be logged here.

This is how my sql.log file looks like:

sql.log

The first value within the braces(0.001) is the execution time for the corresponding SQL statement.

Advantages of logging

Few advantages of logging your SQL queries are,

1. Find the execution time for each queries.
2. Check if your ORM query is executing exactly the way you want it to be.
3. We can find ways to optimise the ORM queries and statements.

Conclusion

Hope this article is helpful. Follow me for more python and Django articles. Happy coding!