Get SQL query count during a Django shell session

Unpacking the SQL query bag

When you're aiming to become a performance guru, understanding how many and what kind of SQL queries your app is running is critical. Let's dive deep into Django query tracking.

Inspecting connection.queries

You can print every SQL query executed:

for query in connection.queries:
    # Here lies an SQL query #DatabaseTombstone
    print(query['sql'])


This gives insights into the kinds and rates of your database interactions. It's like having your own database detective.

Resetting queries: fresh start

To maintain good performance, clean the slate with reset_queries():

from django.db import reset_queries

reset_queries()  # back to zero, let's go again!


This ensures your session won't chew up unnecessary memory resources, putting a leash on that memory-hogging monster.

Multi-database? No problem!

For applications using multiple databases, you need to count queries across all:

from django.db import connections

for conn in connections.all():
    print(f"DB alias: {conn.alias}: Queries: {len(conn.queries)}")


This helps effectively track queries across your database landscape. Remember, more databases mean more responsibilities.

Django shell extensions: icing on the cake

django_extensions can supercharge your shell session. Use shell_plus for auto-imports and query tracking:

python manage.py shell_plus --print-sql


Your shell session will start with your models imported and ready, just like a well-set stage for a SQL drama.

Counting queries: a function for the ages

A dedicated function will make your life easier:

def count_queries():
    # Fingers crossed it's not over 9000!
    print(f'Total queries: {len(connection.queries)}')


You can call this nifty function anytime.

Python version specifics

Python 3 users can load scripts into the shell with:

exec(open('extra_imports.py').read())


For the Python 2 folks:

execfile('extra_imports.py')


This way, everyone can enjoy automated imports or setups.

Building accuracy with best practices

Accurate tracking is not trivial. Here are a few best practices:

Consistent development settings

Toggle settings.DEBUG mindfully. Inconsistent use can affect database query caching and skew your counts.

Watch out for automated queries

Django middleware and other processes can launch unsolicited queries. Filter these out for a focus on your application's queries:

relevant_queries = [q for q in connection.queries if 'FROM relevant_table' in q['sql']]
print(f'App-specific queries: {len(relevant_queries)}')


Tread lightly in production

In production environments, opt for dedicated monitoring services, like Datadog. These maintain performance while tracking metrics.

The bigger picture

Monitoring query counts is a stepping stone towards improving performance, scaling your application, and managing server costs effectively. This data aids in diagnosing performance issues and implementing architectural decisions for database read replicas, caching mechanisms, and more.