Coder Social home page Coder Social logo

snapshot-queries's Introduction

snapshot-queries

Capture all SQL statements executed via Django and SqlAlchemy ORM queries

Use cases

  • See the exact query executed by a complex Django Queryset or SQLAlchemy query
  • Quickly see the slowest queries executed in a code block
  • Identify similar queries executed in a code block to help detect the N+1 query problem
  • See the exact line of code that triggered a query execution, including its full stacktrace

Examples

Display queries executed in a code block

from django.contrib.auth import get_user_model
from snapshot_queries import snapshot_queries

User = get_user_model()
with snapshot_queries() as queries_executed:
    User.objects.only('email').get(id=1)
    User.objects.only('email').get(id=7)

queries_executed.display()

Output:

Query 1
---------
2 ms

/path/to/module.py:5 in function_name

User.objects.only('email').get(id=1)

SELECT "auth_user"."id",
       "auth_user"."email"
FROM "auth_user"
WHERE "auth_user"."id" = 1


Query 2
---------
< 1 ms

/path/to/module.py:6 in function_name

User.objects.only('email').get(id=7)

SELECT "auth_user"."id",
       "auth_user"."email"
FROM "auth_user"
WHERE "auth_user"."id" = 7

Display specific attributes of each query

You can choose which attributes to display.

Supported attributes to display:

  • code (the python code that triggered the query)
  • duration (how long the query took to execute)
  • idx (the index of the query executed)
  • location (the location in our code where the query was executed)
  • stacktrace (the full stacktrace for each query)
  • sql (the sql statement of the query)
  • colored (display the sql statement colored)
  • formatted (display the sql statement formatted)

E.g. to display the stacktrace for each query, use queries_executed.display(stacktrace=True):

from django.contrib.auth import get_user_model
from snapshot_queries import snapshot_queries

User = get_user_model()

def main():
    with snapshot_queries() as queries_executed:
        User.objects.only('email').get(id=1)
        User.objects.only('is_staff').get(id=7)

    queries_executed.display(sql=True, stacktrace=True)

main()

Output:

Query 1
---------
./path/to/file.py:12 in <module>
    main()
./path/to/file.py:8 in main
    User.objects.only('email').get(id=1)

SELECT "auth_user"."id",
       "auth_user"."email"
FROM "auth_user"
WHERE "auth_user"."id" = 1


Query 2
---------
./path/to/file.py:13 in <module>
    main()
./path/to/file.py:9 in main
    User.objects.only('is_staff').get(id=7)

SELECT "auth_user"."id",
       "auth_user"."is_staff"
FROM "auth_user"
WHERE "auth_user"."id" = 7

Order queries by duration

fastest_queries = queries_executed.order_by('duration')[:3]
slowest_queries = queries_executed.order_by('-duration')[:3]
slowest_queries_executed.display()

Inspect the slowest query

slowest_query = queries_executed.order_by('-duration')[0]
slowest_query.display(code=True, location=True, sql=True)

Group queries with duplicate sql statements together

from django.contrib.auth import get_user_model
from snapshot_queries import snapshot_queries

User = get_user_model()

with snapshot_queries() as queries_executed:
    User.objects.only('email').get(id=1)
    User.objects.only('email').get(id=1)
    User.objects.only('email').get(id=1)
    User.objects.only('email').get(id=7)

duplicates = queries_executed.duplicates().display()

Output:

========================
3 duplicate queries
========================
Query 1
---------
1 ms

./path/to/file.py:9 in main

User.objects.only('email').get(id=1)

SELECT "auth_user"."id",
       "auth_user"."email"
FROM "auth_user"
WHERE "auth_user"."id" = 1


Query 2
---------
< 1 ms

./path/to/file.py:10 in main

User.objects.only('email').get(id=1)

SELECT "auth_user"."id",
       "auth_user"."email"
FROM "auth_user"
WHERE "auth_user"."id" = 1


Query 3
---------
< 1 ms

./path/to/file.py:11 in main

User.objects.only('email').get(id=1)

SELECT "auth_user"."id",
       "auth_user"."email"
FROM "auth_user"
WHERE "auth_user"."id" = 1

Group queries with similar sql statements together

from django.contrib.auth import get_user_model
from snapshot_queries import snapshot_queries

User = get_user_model()

with snapshot_queries() as queries_executed:
    User.objects.only('email').get(id=1)
    User.objects.only('email').get(id=1)
    User.objects.only('email').get(id=1)
    User.objects.only('email').get(id=7)

similar = queries_executed.similar().display()

Output

========================
4 similar queries
========================
Query 1
---------
2 ms

/path/to/file.py:6 in main

User.objects.only('email').get(id=1)

SELECT "auth_user"."id",
       "auth_user"."email"
FROM "auth_user"
WHERE "auth_user"."id" = 1


Query 2
---------
< 1 ms

/path/to/file.py:7 in main

User.objects.only('email').get(id=1)

SELECT "auth_user"."id",
       "auth_user"."email"
FROM "auth_user"
WHERE "auth_user"."id" = 1


Query 3
---------
< 1 ms

/path/to/file.py:8 in main

User.objects.only('email').get(id=1)

SELECT "auth_user"."id",
       "auth_user"."email"
FROM "auth_user"
WHERE "auth_user"."id" = 1


Query 4
---------
< 1 ms

/path/to/file.py:9 in main

User.objects.only('email').get(id=7)

SELECT "auth_user"."id",
       "auth_user"."email"
FROM "auth_user"
WHERE "auth_user"."id" = 7

snapshot-queries's People

Contributors

juanrgon avatar actions-user avatar taylor-cedar avatar

Recommend Projects

  • React photo React

    A declarative, efficient, and flexible JavaScript library for building user interfaces.

  • Vue.js photo Vue.js

    ๐Ÿ–– Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.

  • Typescript photo Typescript

    TypeScript is a superset of JavaScript that compiles to clean JavaScript output.

  • TensorFlow photo TensorFlow

    An Open Source Machine Learning Framework for Everyone

  • Django photo Django

    The Web framework for perfectionists with deadlines.

  • D3 photo D3

    Bring data to life with SVG, Canvas and HTML. ๐Ÿ“Š๐Ÿ“ˆ๐ŸŽ‰

Recommend Topics

  • javascript

    JavaScript (JS) is a lightweight interpreted programming language with first-class functions.

  • web

    Some thing interesting about web. New door for the world.

  • server

    A server is a program made to process requests and deliver data to clients.

  • Machine learning

    Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.

  • Game

    Some thing interesting about game, make everyone happy.

Recommend Org

  • Facebook photo Facebook

    We are working to build community through open source technology. NB: members must have two-factor auth.

  • Microsoft photo Microsoft

    Open source projects and samples from Microsoft.

  • Google photo Google

    Google โค๏ธ Open Source for everyone.

  • D3 photo D3

    Data-Driven Documents codes.