How to get SQL from Django QuerySet with filter parameters properly quoted?

Question

MyModel.objects.filter(name="my name", date__lte=datetime.now()).query

Outputs:

SELECT "mymodel"."id", "mymodel"."name", "mymodel"."date" 
FROM "mymodel" 
WHERE ("mymodel"."name" = my name AND "mymodel"."date" <= 2016-02-24 20:24:00.456974+00:00)

Which is not a valid SQL (filter parameters are unquoted).

How can I get the exact SQL that will be executed, which is:

SELECT "mymodel"."id", "mymodel"."name", "mymodel"."date" 
FROM "mymodel" 
WHERE ("mymodel"."name" = 'my name' AND "mymodel"."date" <= '2016-02-24 20:24:00.456974+00:00'::timestamptz)

serg · Accepted Answer · 2016-02-24 22:33:48Z

12

This isn't fully answering it, but for my purposes I wanted to be able to rerun the query as raw sql. I was able to get sql and params using:

queryset = MyModel.objects.filter(name="my name", date__lte=datetime.now())
sql, sql_params = queryset.query.get_compiler(using=queryset.db).as_sql()

Then I could use these values to rerun the query as raw:

MyModel.objects.raw(sql, sql_params)

answered Feb 24, 2016 at 22:33

serg

112k78 gold badges326 silver badges337 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dr Manhattan Over a year ago

Dude you saved me hours of torture, I did not know about the get_compiler method, i was queryset.query.__str__() and going to the dark side of regex since Django was not escaping the parameters when you invoice str like that

Alireza Farahani Over a year ago

I don't know from which Django version, but it could be more simplified to queryset.query.sql_with_params()

Collectives™ on Stack Overflow

How to get SQL from Django QuerySet with filter parameters properly quoted?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related