How can I see the raw SQL queries Django is running

Question

Is there a way to show the SQL that Django is running while performing a query

User · Answer

Take a look at debug toolbar  it s very useful for debugging   Documentation and source is available at http   django-debug-toolbar readthedocs io

User · Answer

Though you can do it with with the code supplied  I find that using the debug toolbar app is a great tool to show queries  You can download it from github here   This gives you the option to show all the queries ran on a given page along with the time to query took  It also sums up the number of queries on a page along with total time for a quick review  This is a great tool  when you want to look at what the Django ORM does behind the scenes  It also have a lot of other nice features  that you can use if you like

User · Answer

If you make sure your settings py file has    django core context processors debug listed in CONTEXT PROCESSORS DEBUG True your IP in the INTERNAL IPS tuple   Then you should have access to the sql queries variable   I append a footer to each page that looks like this     if sql queries       lt div class  footNav  gt       lt h2 gt Queries lt  h2 gt       lt p gt           sql queries length    Quer   sql queries pluralize  y ies        sql time sum   Time        ifnotequal sql queries length 0            lt span style  cursor  pointer   onclick  var s document getElementById  debugQueryTable   style s disp  lay s display   none      none  this innerHTML this innerHTML   Show   Hide   Show    gt Show lt  span gt          endifnotequal         lt  p gt       lt table id  debugQueryTable  style  display  none   gt         lt col width  1  gt  lt  col gt         lt col gt  lt  col gt         lt col width  1  gt  lt  col gt         lt thead gt           lt tr gt             lt th scope  col  gt   lt  th gt             lt th scope  col  gt SQL lt  th gt             lt th scope  col  gt Time lt  th gt           lt  tr gt         lt  thead gt         lt tbody gt             for query in sql queries               lt tr class     cycle odd even     gt               lt td gt    forloop counter    lt  td gt               lt td gt    query sql escape    lt  td gt               lt td gt    query time    lt  td gt             lt  tr gt             endfor           lt  tbody gt       lt  table gt     lt  div gt     endif      I got the variable sql time sum by adding the line   context extras  sql time sum     sum  float q  time    for q in connection queries     to the debug function in django src django core context processors py

User · Answer

Just to add  in django  if you have a query like  MyModel objects all    do  MyModel objects all   query sql with params    or  str MyModel objects all   query   to get the sql string

User · Answer

The query is actually embedded in the models API  q   Query objects values  val1   val2   val etc    print q query

User · Answer

Django-extensions have a command shell plus with a parameter print-sql    manage py shell plus --print-sql   In django-shell all executed queries will be printed  Ex    User objects get pk 1  SELECT  auth user   id           auth user   password           auth user   last login           auth user   is superuser           auth user   username           auth user   first name           auth user   last name           auth user   email           auth user   is staff           auth user   is active           auth user   date joined  FROM  auth user  WHERE  auth user   id    1  Execution time  0 002466s  Database  default    lt User  username gt

User · Answer

See the docs FAQ   How can I see the raw SQL queries Django is running    django db connection queries contains a list of the SQL queries   from django db import connection print connection queries    Querysets also have a query attribute containing the query to be executed   print MyModel objects filter name  my name   query    Note that the output of the query is not valid SQL  because       Django never actually interpolates the parameters  it sends the query and the parameters separately to the database adapter  which performs the appropriate operations     From Django bug report  17741   Because of that  you should not send query output directly to a database

User · Answer

I put this function in a util file in one of the apps in my project   import logging import re  from django db import connection  logger   logging getLogger   name     def sql logger        logger debug  TOTAL QUERIES      str len connection queries        logger debug  TOTAL TIME      str sum  float q  time    for q in connection queries          logger debug  INDIVIDUAL QUERIES        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           logger debug   n           seconds  n n    n  format i  query  time      n  join sql      Then  when needed  I just import it and call it from whatever context  usually a view  is necessary  e g          other imports from  utils import sql logger  class IngredientListApiView generics ListAPIView             class variables and such        Main function that gets called when view is accessed     def list self  request   args    kwargs           response   super IngredientListApiView  self  list request   args    kwargs             Call our function         sql logger            return response   It s nice to do this outside the template because then if you have API views  usually Django Rest Framework   it s applicable there too

User · Answer

The following returns the query as valid SQL  based on https   code djangoproject com ticket 17741   def str query qs               qs query returns something that isn t valid SQL  this returns the actual     valid SQL that s executed  https   code djangoproject com ticket 17741             cursor   connections qs db  cursor       query  params   qs query sql with params       cursor execute  EXPLAIN     query  params      res   str cursor db ops last executed query cursor  query  params       assert res startswith  EXPLAIN        return res len  EXPLAIN

User · Answer

Another option  see logging options in settings py described by this post  http   dabapps com blog logging-sql-queries-django-13   debug toolbar slows down each page load on your dev server  logging does not so it s faster  Outputs can be dumped to console or file  so the UI is not as nice  But for views with lots of SQLs  it can take a long time to debug and optimize the SQLs through debug toolbar since each page load is so slow

User · Answer

I believe this ought to work if you are using PostgreSQL   from django db import connections from app name import models from django utils import timezone    Generate a queryset  use your favorite filter  QS objects  and whatnot  qs models ThisDataModel objects filter user  bob  date  lte timezone now       Get a cursor tied to the default database cursor connections  default   cursor      Get the query SQL and parameters to be passed into psycopg2  then pass   those into mogrify to get the query that would have been sent to the backend   and print it out  Note F-strings require python 3 6 or later  print f  cursor mogrify  qs query sql with params

User · Answer

I developed an extension for this purpose  so you can easily put a decorator on your view function and see how many queries are executed   To install     pip install django-print-sql   To use as context manager   from django print sql import print sql    set  count only  to  True  will print the number of executed SQL statements only with print sql count only False        write the code you want to analyze in here      e g  some complex foreign key lookup      or analyzing a DRF serializer s performance    for user in User objects all    10         user groups first     To use as decorator   from django print sql import print sql decorator    print sql decorator count only False     this works on class-based views as well def get request         your view code here   Github  https   github com rabbit-aaron django-print-sql

User · Answer

View Queries using django db connection queries  from django db import connection print connection queries    Access raw SQL query on QuerySet object   qs   MyModel objects all    print qs query

User · Answer

No other answer covers this method  so   I find by far the most useful  simple  and reliable method is to ask your database   For example on Linux for Postgres you might do   sudo su postgres tail -f  var log postgresql postgresql-8 4-main log   Each database will have slightly different procedure   In the database logs you ll see not only the raw SQL  but any connection setup or transaction overhead django is placing on the system

User · Answer

For Django 2 2   As most of the answers did not helped me much when using   manage py shell  Finally i found the answer  Hope this helps to someone   To view all the queries   from django db import connection connection queries   To view query for a single query   q Query objects all   q query   str       q query just displaying the object for me   Using the   str     String representation  displayed the full query

User · Answer

To get result query from django to database with correct parameter substitution  you could use this function  from django db import connection  def print database query formatted query       sql  params   query sql with params       cursor   connection cursor       cursor execute  EXPLAIN     sql  params      db query   cursor db ops last executed query cursor  sql  params  replace  EXPLAIN             parts        format db query  split  FROM       print parts 0       if len parts   gt  1          parts   parts 1  split  WHERE           print  FROM    format parts 0            if len parts   gt  1              parts   parts 1  split  ORDER BY               print  WHERE    format parts 0                if len parts   gt  1                  print  ORDER BY    format parts 1       USAGE users   User objects filter email  admin admin com   order by  -id   print database query formatted users query   Output example SELECT  quot users user quot   quot password quot    quot users user quot   quot last login quot    quot users user quot   quot is superuser quot    quot users user quot   quot deleted quot    quot users user quot   quot id quot    quot users user quot   quot phone quot    quot users user quot   quot username quot    quot users user quot   quot userlastname quot    quot users user quot   quot email quot    quot users user quot   quot is staff quot    quot users user quot   quot is active quot    quot users user quot   quot date joined quot    quot users user quot   quot latitude quot    quot users user quot   quot longitude quot    quot users user quot   quot point quot   bytea   quot users user quot   quot default search radius quot    quot users user quot   quot notifications quot    quot users user quot   quot admin theme quot    quot users user quot   quot address quot    quot users user quot   quot is notify when buildings in radius quot    quot users user quot   quot active campaign id quot    quot users user quot   quot is unsubscribed quot    quot users user quot   quot sf contact id quot    quot users user quot   quot is agree terms of service quot    quot users user quot   quot is facebook signup quot    quot users user quot   quot type signup quot   FROM  quot users user quot   WHERE  quot users user quot   quot email quot     admin admin com   ORDER BY  quot users user quot   quot id quot  DESC  It based on this ticket comment  https   code djangoproject com ticket 17741 comment 4

User · Answer

I ve made a small snippet you can use   from django conf import settings from django db import connection   def sql echo method   args    kwargs       settings DEBUG   True     result   method  args    kwargs      for query in connection queries          print query      return result     HOW TO USE EXAMPLE       result   sql echo my method   whatever   show True    It takes as parameters function  contains sql queryies  to inspect and args  kwargs needed to call that function  As the result it returns what function returns and prints SQL queries in a console

[sql] How can I see the raw SQL queries Django is running?

Examples related to sql

Examples related to django