django how to see sql query when running tests?

Question

One of my django application unit test fails with

DatabaseError: ORA-00942: table or view does not exist

I would like to see actual SQL query that caused this error. Do you know how to achieve that?

Answer 1

If you want to print/log all SQL queries from the tests, try subclassing TestCase like this:

from django.conf import settings
from django.template import Template, Context
import sys
from django.db import connection
from django.test import TestCase

class LoggingTestCase(TestCase):

  @staticmethod
  def setUpClass():
    # The test runner sets DEBUG to False. Set to True to enable SQL logging.
    settings.DEBUG = True
    super(LoggingTestCase, LoggingTestCase).setUpClass()

  @staticmethod
  def tearDownClass():
    super(LoggingTestCase, LoggingTestCase).tearDownClass()

    time = sum([float(q['time']) for q in connection.queries])
    t = Template("{{count}} quer{{count|pluralize:\"y,ies\"}} in {{time}} seconds:\n\n{% for sql in sqllog %}[{{forloop.counter}}] {{sql.time}}s: {{sql.sql|safe}}{% if not forloop.last %}\n\n{% endif %}{% endfor %}")
    print >> sys.stderr, t.render(Context({'sqllog': connection.queries, 'count': len(connection.queries), 'time': time}))

    # Empty the query list between TestCases.    
    connection.queries = []

Then use LoggingTestCase instead of TestCase as the base class in your tests. Just remember to call this tearDownClass if you override it.

Answer 2

Another option is to use CaptureQueriesContext (tested with pytest).

from django.db import connection
from django.test.utils import CaptureQueriesContext


def test_foo():
    with CaptureQueriesContext(connection) as ctx:
        # code that runs SQL queries
        print(ctx.captured_queries)

Sources:

• https://blog.ploetzli.ch/2019/showing-sql-queries-with-pytest-and-django/
• How to force django to print each executed sql query

Answer 3

You can also do the following to get the queries (and then for instance print it or evaluate it in your test).

Actually you shouldn't alter django.conf.settings nowadays, therefore I use override_settings.

from django.db import connection, reset_queries
from django.test import override_settings, TransactionTestCase

class TransactionTests(TransactionTestCase):

    @override_settings(DEBUG=True)
    def test_sql(self):
        reset_queries()
        try:
            # Code that uses the ORM goes here
        except Exception as e:
            pass
        self.assertEqual(connection.queries, [])

TestCase might also be suitable, see the differences in this answer.

See the Django documentation for details for SQL output.

Answer 4

Another option is to use connection.execute_wrapper() in your test as follows:

from django.db import connection

def logger(execute, sql, params, many, context):
    print(sql, params)
    return execute(sql, params, many, context)

class GizmoTest(TestCase):

    def test_with_sql_logging(self):
        with connection.execute_wrapper(logger):
            code_that_uses_database()

Tested with Django 2.2.

Answer 5

Its not the cleanest solution but if you just quickly want to debug without installing additional packages you could look for the execute() method in django/db.

For Oracle I guess it is in:

django/db/backends/oracle/base.py and look for:

def execute

For PostgreSQL it is in:

django/db/backends/postgresql_psycopg2/base.py

In CursorWrapper there is a execute() method.

Both are catching IntegrityError and DatabaseError, you can add a print statement there.

For ppl who want to see all sql queries, put the print statement right after the function call.