Logging Django sql queries

In writing complex software using the Django framework, we deal with it’s default object-relational mapping layer all the time. Known as Django ORM, it is used to interact with application data from various relational databases such as PostgreSQL, MySQL and others. Throughout our use, we have stumbled across a few key insights. Below are a couple of hints about working with Django ORM.

There may be times when you really want to see the exact query done by the Django engine and find out which parameters have been passed.
Here are three ways to do it:

1. Queyset.query –  This returns the structure of the SQL source code with already inserted parameters ( it is actually pseudo-SQL, and it cannot be executed without modifications), for example:

   q = User.objects.filter(email__icontains='igor').query
   	print(q)

   The result will be something like this:

   SELECT "users_user"."id", "users_user"."last_login", "users_user"."email" FROM "users_user" WHERE ("users_user"."is_registered" = True AND UPPER("users_user"."email"::text) LIKE UPPER(%igor%))

2. Django.db.connection.queries – Here Django stores the history of all executed database queries in the order of their execution. Therefore,immediately after executing the request you can get it from here:

   from django.db import connection
   u = User.objects.filter(email__icontains='igor').first()
   print(connection.queries[-1]["sql"])

   It will then give you:

   SELECT "users_user"."id", "users_user"."last_login", "users_user"."email" FROM "users_user" WHERE ("users_user"."is_registered" = true AND UPPER("users_user"."email"::text) LIKE UPPER('%igor%')) ORDER BY "users_user"."id" ASC LIMIT 1 

   As you can see, the 'real' SQL will be here, which you can copy and execute.

3. Log them all - Configure logger 'django.db.backends' and enter absolutely all queries into your log file, which you can do globally through main config Django:

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

   Alternatively you can do each log separately this way:

   import logging 
   l = logging.getLogger('django.db.backends') 
   l.setLevel(logging.DEBUG)
   l.addHandler(logging.StreamHandler()) 
   from blog.models import Entry 
   Entry.objects.all()

As you can see, delving inside Django ORM and looking at the parameters is not rocket science. However, if you happen to be stuck or have any software queries, don’t hesitate to contact us. At Edicasoft, we deal with all minor and major projects, with a wealth of expertise and professionalism that is hard to beat. Happy coding!