Getting the SQL from a Django Queryset

Getting the SQL from a Django QuerySet

You print the queryset's query attribute.

>>> queryset = MyModel.objects.all()
>>> print(queryset.query)
SELECT "myapp_mymodel"."id", ... FROM "myapp_mymodel"

How to see the raw SQL queries Django is running?

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.

If you need to reset the queries to, for example, see how many queries are running in a given period, you can use reset_queries from django.db:

from django.db import reset_queries
from django.db import connection

reset_queries()
# Run your query here
print(connection.queries)
>>> []

Queryset for a sql query

You should be able to do this without a window expression, using a SubQuery

First create a queryset for the subquery that orders by the Column_B=UserA match and then time_created

from django.db.models import Case, When, Q, Subquery, OuterRef

tables_ordered = Table.objects.filter(
    Column_C=OuterRef('Column_C')
).annotate(
    user_match=Case(When(Column_B=UserA, then=0), default=1)
).order_by('user_match', 'time_created')

Then this subquery returns the first pk for the matched Column_C from the OuterRef, similar to selecting the first row from your window function

first_pk_for_each_column_c = Subquery(tables_ordered.values('pk')[:1])

Then use two Q objects to create an OR that selects the row if Column_C is NULL or the pk matches the first pk from the subquery

Table.objects.filter(
    Q(Column_C__isnull=True) | Q(pk=first_pk_for_each_column_c)
)

How to view corresponding SQL query of the Django ORM's queryset?

Each QuerySet object has a query attribute that you can log or print to stdout for debugging purposes.

qs = Model.objects.filter(name='test')
print(qs.query)

Note that in pdb, using p qs.query will not work as desired, but print(qs.query) will.

If that doesn't work, for old Django versions, try:

print str(qs.query)

Edit

I've also used custom template tags (as outlined in this snippet) to inject the queries in the scope of a single request as HTML comments.

Django getting executable raw sql for a QuerySet

Django never creates the raw sql, so no. To prevent SQL injection, django passes the parameters separately to the database drivers at the last step. The best way to get the actual SQL is to look at your query log, which you cannot do before you execute the query.

Django get query from queryset. Returning invalid params

After a lot of digging, I found the answer to my own question.

Posting here so others won't have to search as much as I did.

Turns out that queryset.query doesn't return valid SQL. We need to use .sql_with_params() to get the query and parameters separately.

The final code looked something like this:

qs = Sample.objects.filter(data_date__range=[start_date, end_date])
query, params = qs.query.sql_with_params()

# pyodbc uses "?" vs django returns "%s" as placeholder
query = query.replace("%s", "?")

df = pd.read_sql(query, pyodbc_conn, params=params)

Querying using pyodbc like this took <1 second vs using Django ORM taking 5 seconds for 56000 rows x 5 columns.

Big help from this Django ticket.

---

Related Topics