django-query-inspector 1.2.3

A collection of tools to render, export and inspect Django Querysets.

A collection of tools to:

• count and trace db queries for debugging purposes or to optimize them

• render a Queryset (or a list of dictionaries) in various formats

• export a Queryset to a spreadsheet

• inspect the SQL activity happening under the hood of a Django project

• and more …

Contents

• 1 Quick start

• 2 Does it work?

• 3 Query counting

• 4 Execute SQL statements

• 5 App settings

• 6 @query_debugger

• 7 Tracing queries in real-time

• 8 Inspecting queries in a unit test

• 9 Tracing

• 10 Inspect a queryset with qsdump

• 11 Queryset rendering

• 12 Custom rendering

• 13 Transposing resulting table

• 14 Download the queryset as CSV or Excel file (xlsx)

• 15 Generic helpers

• 16 Exporters

• 17 Helper management commands

  • 17.1 History

• 18 v1.2.3

• 19 v1.2.2

• 20 v1.2.1

• 21 v1.2.0

• 22 v1.1.12

• 23 v1.1.11

• 24 v1.1.10

• 25 v1.1.9

• 26 v1.1.8

• 27 v1.1.7

• 28 v1.1.6

• 29 v1.1.5

• 30 v1.1.4

• 31 v1.1.3

• 32 v1.1.2

• 33 v1.1.1

• 34 v1.1.0

• 35 v1.0.9

• 36 v1.0.8

• 37 v1.0.7

• 38 v1.0.6

• 39 v1.0.5

• 40 v1.0.4

• 41 v1.0.3

• 42 v1.0.2

• 43 v1.0.1

• 44 v1.0.0

• 45 v0.0.6

• 46 v0.0.5

• 47 v0.0.4

• 48 v0.0.3

• 49 v0.0.2

• 50 v0.0.1

1 Quick start

1. Installation:

   pip install django-query-inspector

2. Add “query_inspector” to your INSTALLED_APPS setting like this:

   INSTALLED_APPS = [
       ...
       'query_inspector',
   ]

3. Add “QueryCountMiddleware” to your MIDDLEWARE setting like this:

   MIDDLEWARE = [
       ...
       'query_inspector.middleware.QueryCountMiddleware',
   ]

4. Optionally, include styles in your base template:

   <link href="{% static 'query_inspector.css' %}" rel="stylesheet" />

5. Optional dependencies:

   • sqlparse

   • termcolor

   • pygments

   • tabulate

   • xlsxwriter

2 Does it work?

Running the unit tests from your project:

python manage.py test -v 2 query_inspector --settings=query_inspector.tests.test_settings

Running the unit tests from your local fork:

cd django-query-inspector
./runtests.py

or:

coverage run --source='.' runtests.py
coverage report

3 Query counting

A middleware that prints DB query counts in Django’s runserver console output (only in DEBUG mode).

Adapted from: Django Querycount

by Brad Montgomery

Setting	Meaning
IGNORE_ALL_REQUESTS	Disables query count
IGNORE_REQUEST_PATTERNS	A list of regexp patterns to bypass matching requests
IGNORE_SQL_PATTERNS	A list of regexp patterns to bypass matching queries
THRESHOLDS	How many queries are interpreted as high or medium (and the color-coded output)
DISPLAY_ALL	Trace all queries (even when not duplicated)
DISPLAY_PRETTIFIED	Use pygments and sqlparse for queries tracing
COLOR_FORMATTER_STYLE	Color formatter style for Pygments
RESPONSE_HEADER	Custom response header that contains the total number of queries executed (None = disabled)
DISPLAY_DUPLICATES	Controls how the most common duplicate queries are displayed (None = displayed)

Default settings (to be overridden in projects’ settings):

QUERYCOUNT = {
    'IGNORE_ALL_REQUESTS': True,
    'IGNORE_REQUEST_PATTERNS': [],
    'IGNORE_SQL_PATTERNS': [],
    'THRESHOLDS': {
        'MEDIUM': 50,
        'HIGH': 200,
        'MIN_TIME_TO_LOG': 0,
        'MIN_QUERY_COUNT_TO_LOG': 0
    },
    'DISPLAY_ALL': True,
    'DISPLAY_PRETTIFIED': True,
    'COLOR_FORMATTER_STYLE': 'monokai',
    'RESPONSE_HEADER': 'X-DjangoQueryCount-Count',
    'DISPLAY_DUPLICATES': 0,
}

4 Execute SQL statements

It is possible to execute a SQL statements against the default db connection using the following helper:

query_inspector.sql.perform_query(sql, params, log=False, validate=True)

The resulting recordset will be returned as a list of dictionaries.

Or, you can save it in the Django admin (model query_inspector.Query), then click the “Preview” button.

If the query contains named parameters (such as %(name)s), a form will be displayed to collect the actual values before execution.

Inspired by:

• django-sql-dashboard

• django-sql-explorer

query_preview

5 App settings

QUERY_INSPECTOR_QUERY_SUPERUSER_ONLY = True
QUERY_INSPECTOR_QUERY_DEFAULT_LIMIT = 0
QUERY_INSPECTOR_QUERY_STOCK_QUERIES = []
QUERY_INSPECTOR_QUERY_STOCK_VIEWS = None
DEFAULT_CSV_FIELD_DELIMITER = ';'
QUERY_INSPECTOR_SQL_BLACKLIST = (
    'ALTER',
    'RENAME ',
    ...
QUERY_INSPECTOR_SQL_WHITELIST = (
    'CREATED',
    'UPDATED',
    ...

key	example
SITECOPY_REMOTE_HOST	project.somewhere.com”
SITECOPY_REMOTE_PROJECT_INSTANCE	project”
SITECOPY_REMOTE_MEDIA_FOLDER	/home/project/public/media/”
SITESYNC_WEBSERVER_PROCESS_NAME	project_gunicorn’
SITESYNC_SUPERVISOR_URL	http://admin:PASSWORD@localhost:9090/RPC2’
DUMP_LOCAL_DATA_TARGET_FOLDER	BASE_DIR/dumps/localhost’

6 @query_debugger

Decorator to check how many queries are executed when rendering a specific view.

Adapted from:

Django select_related and prefetch_related: Checking how many queries reduce using these methods with an example

by Goutom Roy

Examples:

from query_inspector import query_debugger

@query_debugger
def tracks_list_view(request):
    ...

class TrackAjaxDatatableView(AjaxDatatableView):

    ...

    @query_debugger
    def dispatch(self, request, *args, **kwargs):
        ...

Result:

query_debugger

7 Tracing queries in real-time

On rare occasions, you might want to trace queries immediately as they happen while stepping through the code.

For that aim, configure the ‘django.db.backends’ logger in your settings; to print formatted and colored queries, provided pygments and sqlparse have been installed, use the query_inspector.log.QueryLogHandler handler:

LOGGING = {
    'version': 1,
    'disable_existing_loggers': False,
    'handlers': {
        'db_console': {
            'level': 'DEBUG',
            #'class': 'logging.StreamHandler',
            'class': 'query_inspector.log.QueryLogHandler',
        },
    },
    'loggers': {
        'django.db.backends': {
            'handlers': ['db_console', ],
            'level': 'DEBUG',
        },
    },
}

8 Inspecting queries in a unit test

This is not obvious, since unit tests are run with DEBUG disabled.

Django provides a convenient CaptureQueriesContext for this:

import pprint
from django import db
from django.test.utils import CaptureQueriesContext
from query_inspector import prettyprint_query

def text_whatever(self):

    db.reset_queries()
    with CaptureQueriesContext(db.connection) as context:

        ... do your stuff ...

    num_queries = context.final_queries - context.initial_queries
    print('num_queries: %d' % num_queries)
    #pprint.pprint(context.captured_queries)

    for row in context.captured_queries:
        prettyprint_query(row['sql'])
        print('time: ' + row['time'])

More examples are available here:

Python django.test.utils.CaptureQueriesContext() Examples

9 Tracing

Some helper functions are available to print formatted and colored text in the console.

Optional requirements:

• sqlparse

• termcolor

• pygments

• tabulate

Functions:

def trace(message, color=’yellow’, on_color=None, attrs=None, prompt=’’, prettify=False)

Display ‘message’, optionally preceed by ‘prompt’; If ‘prettify’ is True, format message with pprint

Color support provided by: https://pypi.org/project/termcolor/

def prettyprint_query(query, params=None, colorize=True, prettify=True, reindent=True)

Display the specified SQL statement

def prettyprint_queryset(qs, colorize=True, prettify=True, reindent=True)

Display the SQL statement implied by the given queryset

def trace_func(fn):

Decorator to detect: function call, input parameters and return value

def qsdump(* fields, queryset, max_rows=None, render_with_tabulate=True, title=””)

See below

def qsdump2(queryset, include, exclude, max_rows=None, render_with_tabulate=True, title=””)

Calls qsdump() building the field list from either “include” or “exclude” parameter

Results:

prettyprint_queryset

trace_func

10 Inspect a queryset with qsdump

With qsdump you can:

• display the formatted SQL statement

• display the content of the queryset

Parameters:

fields:

one or more field names; ‘*’ means ‘all’

queryset:

the queryset to be inspected

max_rows:

optionally limit the numer of rows

render_with_tabulate=True

use “tabulate” when available

title=””

optional title

Example:

qsdump('*', queryset=tracks, max_rows=10)

qsdump

11 Queryset rendering

A few templatetags are available to render either a queryset or a list of dictionaries:

def render_queryset_as_table(* fields, queryset, options={})
def render_queryset_as_csv(* fields, queryset, options={})
def render_queryset_as_text(* fields, queryset, options={})

Sample usage:

{% load static query_inspector_tags %}

<link href="{% static 'query_inspector.css' %}" rel="stylesheet" />

<table class="simpletable smarttable">
    {% render_queryset_as_table "id" "last_name|Cognome" "first_name|Nome" ... queryset=operatori %}
</table>

Parameters:

queryset: a queryset of a list of dictionaries with data to rendered

options:

• max_rows: max n. of rows to be rendered (None=all)

• format_date: date formatting string; see:

  • https://docs.djangoproject.com/en/dev/ref/settings/#date-format

  • https://docs.djangoproject.com/en/dev/ref/templates/builtins/#date

• add_totals: computes column totals and append results as bottom row

• transpose: flag to transpose the resulting table

fields: a list of field specifiers, espressed as:

• “fieldname”, or

• “fieldname|title”, or

• “fieldname|title|extra_classes”

Field “extra classes” with special styles:

• “percentage”: render column as %

• “enhanced”

• “debug-only”

render_queryset

More templatetags:

def pdb(element)
def ipdb(element)
def format_datetime(dt, include_time=True, include_seconds=False, exclude_date=False)
def format_date(dt)
def format_datetime_with_seconds(dt)
def format_time(t, include_seconds=False)
def format_time_with_seconds(t)
def format_timedelta(td_object, include_seconds=True)
def format_timediff(t1, t2, include_seconds=True)
def timeformat_seconds(seconds)
def timeformat(seconds)
# def format_number(value, decimals, grouping )
def queryset_as_json(qs)
def object_as_dict(instance, fields=None, exclude=None)
def object_as_json(instance, fields=None, exclude=None, indent=0)

12 Custom rendering

For greated control of the final rendering, you can retrieve headers and data rows separately (as lists) using:

def render_queryset_as_table(* fields, queryset, options={})

For example, the equivalent of:

print(render_queryset_as_text(*fields, queryset=queryset, options=options))

can be reproduced as follows:

headers, rows = render_queryset_as_data(*fields, queryset=queryset, options=options)

print('|'.join(headers))
for row in rows:
    print('|'.join(row))
print("")

13 Transposing resulting table

Occasionally, you might need to switch columns and rows in the resulting table; this can be obtained by adding a ‘transpose’: True to the options.

Currently available for render_queryset_as_data() and render_queryset_as_table().

Alternatively, you can transpose a queryset with django-pandas as follows:

import pandas as pd
from django_pandas.io import read_frame

df = read_frame(queryset)
table_html = df.transpose().to_html()
print(table_html)

14 Download the queryset as CSV or Excel file (xlsx)

For historical reasons, we provide two different approaches to export the queryset as a spreadsheet:

1. with the class SpreadsheetQuerysetExporter (see Exporters below)

2. parsing the queryset with the aid of render_queryset_as_table

The first requires a proper Queryset, while the second should work with either a Queryset or a list of dictionares.

In both cases, two helper view functions are available to build the HTTP response required for attachment download:

export_any_queryset(request, queryset, filename, excluded_fields=[], included_fields=[], csv_field_delimiter = ";")

export_any_dataset(request, *fields, queryset, filename, csv_field_delimiter = ";")

The helper function normalized_export_filename(title, extension) might be used to build filenames consistently.

Sample usage:

from django.utils import timezone
from query_inspector.views import normalized_export_filename
from query_inspector.views import export_any_dataset


def export_tracks_queryset(request, file_format='csv'):
    queryset = Track.objects.select_related('album', 'album__artist', )
    filename = normalized_export_filename('tracks', file_format)
    return export_any_queryset(
        request,
        queryset,
        filename,
        excluded_fields=[],
        included_fields=[],
        csv_field_delimiter = ";"
    )


def export_tracks_dataset(request, file_format='csv'):
    queryset = Track.objects.select_related('album', 'album__artist', )
    filename = '%s_%s.%s' % (
        timezone.localtime().strftime('%Y-%m-%d_%H-%M-%S'),
        "tracks",
        file_format,
    )
    fields = [
        "id",
        "name|Track",
        "album|Album",
    ]

    return export_any_dataset(request, *fields, queryset=queryset, filename=filename)

then in your template:

<div style="text-align: right;">
    <div class="toolbar">
        <label>Export Tracks queryset:</label>
        <a href="/tracks/download_queryset/xlsx/" class="button">Download (Excel)</a>
        <a href="/tracks/download_queryset/csv/" class="button">Download (CSV)</a>
    </div>
    <br />
    <div class="toolbar">
        <label>Export Tracks dataset:</label>
        <a href="/tracks/download_dataset/xlsx/" class="button">Download (Excel)</a>
        <a href="/tracks/download_dataset/csv/" class="button">Download (CSV)</a>
    </div>
</div>

where:

urlpatterns = [
    ...
    path('tracks/download_queryset/csv/', views.export_tracks_queryset, {'file_format': 'csv', }),
    path('tracks/download_queryset/xlsx/', views.export_tracks_queryset, {'file_format': 'xlsx', }),
    path('tracks/download_dataset/csv/', views.export_tracks_dataset, {'file_format': 'csv', }),
    path('tracks/download_dataset/xlsx/', views.export_tracks_dataset, {'file_format': 'xlsx', }),
    ...
]

15 Generic helpers

def get_object_by_uuid_or_404(model, uuid_pk)

Calls get_object_or_404(model, pk=uuid_pk) but also prevents “badly formed hexadecimal UUID string” unhandled exception

def prettify_json(data)

Given a JSON string, returns it as a safe formatted HTML Sample usage in Model:

def summary_prettified(self):
    return prettify_json(self.summary)

then add it to the list of readonly_fields in the ModelAdmin

def cleanup_queryset(queryset)

Remove multiple joins on the same table, if any

WARNING: can alter the origin queryset order

16 Exporters

class XslxFile(object)

XSLX writer

Requires: xlsxwriter

def open_xlsx_file(filepath, mode=”rb”)

Utility to open an archive supporting the “with” statement

Sample usage:

with open_xlsx_file(filepath) as writer:
    self.export_queryset(writer, fields, queryset)
assert writer.is_closed()

class SpreadsheetQuerysetExporter(object)

Helper class to export a queryset to a spreadsheet.

Sample usage:

writer = csv.writer(output, delimiter=field_delimiter, quoting=csv.QUOTE_MINIMAL)
exporter = SpreadsheetQuerysetExporter(writer, file_format='csv')
exporter.export_queryset(
    queryset,
    included_fields=[
        'id',
        'description',
        'category__id',
        'created_by__id',
    ]
)

See also: Download the queryset as CSV or Excel file (xlsx)

17 Helper management commands

A few management commands are provided to:

• quickly download database and/or media file from a remote project’s instance

• save/restore a backup copy of database and/or media files to/from a local backup folder

Database actions require Postrgresql; downloading from remote instance requires read access via SSH.

You’re advised to double-check implied actions by dry-running these commands before proceeding.

sitecopy: Syncs database and media files from remote project “project” running on remote server “project.somewhere.com”

Usage:

usage: manage.py sitecopy [-h] [--dry-run] [--quiet] [--host HOST] [-v {0,1,2,3}] [--settings SETTINGS]

Syncs database and media files for project "gallery" from remote server "gallery.brainstorm.it"

optional arguments:
  -h, --help            show this help message and exit
  --dry-run, -d         Dry run (simulate actions)
  --quiet, -q           do not require user confirmation before executing commands
  --host HOST           Default: "gallery.brainstorm.it"
  -v {0,1,2,3}, --verbosity {0,1,2,3}
                        Verbosity level; 0=minimal output, 1=normal output, 2=verbose output, 3=very verbose output
  --settings SETTINGS   The Python path to a settings module, e.g. "myproject.settings.main". If this isn't provided, the
                        DJANGO_SETTINGS_MODULE environment variable will be used.

dump_local_data: Dump local db and media for backup purposes (and optionally remove old backup files)

Settings:

DUMP_LOCAL_DATA_TARGET_FOLDER = getattr(settings, 'DUMP_LOCAL_DATA_TARGET_FOLDER', os.path.join(settings.BASE_DIR, '..', 'dumps', 'localhost'))

Usage:

usage: manage.py dump_local_data [-h] [--target target] [--dry-run] [--max-age MAX_AGE] [--no-gzip] [--legacy]
                                 [-v {0,1,2,3}] [--settings SETTINGS]

Dump local db and media for backup purposes (and optionally remove old backup files)

optional arguments:
  -h, --help            show this help message and exit
  --target target, -t target
                        choices: db, media, all; default: db
  --dry-run, -d         Dry run (simulation)
  --max-age MAX_AGE, -m MAX_AGE
                        If > 0, remove backup files old "MAX_AGE days" or more
  --no-gzip             Do not compress result
  --legacy              use legacy Postgresql command syntax
  -v {0,1,2,3}, --verbosity {0,1,2,3}
                        Verbosity level; 0=minimal output, 1=normal output, 2=verbose output, 3=very verbose output
  --settings SETTINGS   The Python path to a settings module, e.g. "myproject.settings.main". If this isn't provided, the
                        DJANGO_SETTINGS_MODULE environment variable will be used.

restore_from_local_data: Restore db and media from local backups

Settings:

DUMP_LOCAL_DATA_TARGET_FOLDER = getattr(settings, 'DUMP_LOCAL_DATA_TARGET_FOLDER', os.path.join(settings.BASE_DIR, '..', 'dumps', 'localhost'))

Usage:

usage: manage.py restore_from_local_data [-h] [--target target] [--dry-run] [--no-gzip] [--source-subfolder SOURCE_SUBFOLDER]
                                         [-v {0,1,2,3}] [--settings SETTINGS]
                                         prefix

Restore db and media from local backups; source folder is "/Volumes/VMS3/django_storage/gallery/dumps/localhost"

positional arguments:
  prefix                Initial substring to match the filename to restore from; provide enough characters to match a single file

optional arguments:
  -h, --help            show this help message and exit
  --target target, -t target
                        choices: db, media, all; default: db
  --dry-run, -d         Dry run (simulation)
  --no-gzip             Do not compress result
  --source-subfolder SOURCE_SUBFOLDER, -s SOURCE_SUBFOLDER
                        replaces "localhost" in DUMP_LOCAL_DATA_TARGET_FOLDER
  -v {0,1,2,3}, --verbosity {0,1,2,3}
                        Verbosity level; 0=minimal output, 1=normal output, 2=verbose output, 3=very verbose output
  --settings SETTINGS   The Python path to a settings module, e.g. "myproject.settings.main". If this isn't provided, the
                        DJANGO_SETTINGS_MODULE environment variable will be used.

load_stock_queries: Load stock (readonly) queries from settings.QUERY_INSPECTOR_QUERY_STOCK_QUERIES list

Application should provide a list of stock queries as follows:

SQL_QUERIES = [{
    'slug': '...',
    'title': '...',
    'sql': """
        select
        ...
        """,
    'notes': "...",
}, {
    ...
}]

Ideally, you should run this command at deployment time, to make sure that stock queries are always updated with sources.

During development, a “Reload stock queries” button is also available in the changelist.

You can optionally specify in settings.QUERY_INSPECTOR_QUERY_STOCK_VIEWS a callable to list the sql views Models to be included in Stock queries

17.1 History

18 v1.2.3

• Add sql_views in stock queries

19 v1.2.2

• export preview as CSV, JSONL or XLSX

20 v1.2.1

• comment out “signal” from sitecopy management command

21 v1.2.0

• sitecopy refactored to support sitesync

22 v1.1.12

• Fix normalized_export_filename()

23 v1.1.11

• Specify default_auto_field to avoid unexpected migrations

24 v1.1.10

• Examples for “sitecopy” settings

25 v1.1.9

• Prepare for Django 4.0

• Support for Sqlite3 params

26 v1.1.8

• Added missing migration

27 v1.1.7

• Stock queries support

28 v1.1.6

• Fix: remove duplicates from extract_named_parameters()

29 v1.1.5

• Fix readme

30 v1.1.4

• qsdump2() helper added

• Query: persist parameters and duplicate

31 v1.1.3

• fix preview styles again

32 v1.1.2

• fix styles

33 v1.1.1

• Query preview: add horizontal scrolling to results table

34 v1.1.0

• SQL query preview from admin

• fix format_datetime() for naive datetimes

35 v1.0.9

• added “params” and “reindend” parameters to prettyprint_query()

• added “reindend” to prettyprint_queryset()

36 v1.0.8

• [fix] remove division by zero when computing average for and empty list of rows

37 v1.0.7

• QueryCountMiddleware can be used as standalone

38 v1.0.6

• optionally Transpose rendered tables

• slugify “field-…” class in rendered HTML tables

• support “field1__field2” syntax to span relationships

39 v1.0.5

• “dump_local_data” management command now supports sqlite and Windows platform

40 v1.0.4

• fix syntax error due to wrong indentation

41 v1.0.3

• render_value_as_text() optionally preserves numeric formats

42 v1.0.2

• use apply_autofit() in export_any_queryset()

43 v1.0.1

• fix unprettified duplicate_queries dump

44 v1.0.0

• fix format_datetime

45 v0.0.6

• normalized_export_filename() helper

• improved documentation

46 v0.0.5

• Tracing queries in real-time

• Inspecting queries in a unit test

• Helper management commands

47 v0.0.4

• render_queryset_as_data added for greated control of the final rendering

• qsdump supports tabulate

• download the queryset as a spreadsheet

48 v0.0.3

• querycounter middleware

• query_debugger decorator

• tracing helpers

• templetags helpers

• export a Queryset to a spreadsheet

49 v0.0.2

• unit tests reorganized

50 v0.0.1

• Initial setup