1

I have a Django queryset that I prepare with queryset.filter(date__gte=datetime(2011,1,1))

If I then call str(queryset.query) I see this in the string:

... WHERE "App_table"."date" >= 2011-1-1

However, this is invalid SQL code as if I run this in Postgresql I get this error:

... WHERE "App_table"."date" >= 2011-1-1
ERROR:  operator does not exist: date >= integer
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

Why is this happening and how can I ask Django to output proper SQL code that I can work on?

Saturnix
  • 10,130
  • 17
  • 64
  • 120

3 Answers3

2

You can use the query. sql_with_params() method. 

print(q.query. sql_with_params())

This will print a parameterized version of the query.

Daniel Roseman
  • 588,541
  • 66
  • 880
  • 895
  • This returns a tuple where `[0]` is the query with `%s` in the place of parameters, and [1] are the parameters. If I do `queryTuple[0] % queryTuple[1]` it gives me the same invalid string as `str(q.query)`. So I guess I should use `sql_with_params`, manually search for datetimes in [1] and convert them to strings surrounded by quotes. No way for Django to do that? – Saturnix Aug 20 '19 at 16:30
  • Here's a possible answer to my comment: https://stackoverflow.com/a/22828674/1307020 – Saturnix Aug 20 '19 at 16:33
  • Yes the point is that this is now the responsibility of the db adapter rather than Django. – Daniel Roseman Aug 20 '19 at 18:47
0

Adding to Danel Roseman's answer, how I got the valid SQL statement is using this:

sql, params = unioned_qs.query.sql_with_params()

params_with_quotes = []
for x in params:
   params_with_quotes.append("'%s'" % x)
print(sql % tuple(params_with_quotes))

What makes the Django SQL query invalid is because of missing quotes and by replacing all the values with a quoted string, the SQL should be valid. Works in one of my edge cases but haven't tested this theory widely enough.

Tested on Django 3.2 and Python 3.9

AlvinT
  • 1
  • 1
-2

This is how I filter out dates if my model field is models.DateField:

  queryset.filter(date__gte=datetime.date(2011,1,1))

I guess that if you have declared the field date as DateTimeField, the comparison would work.

double-beep
  • 5,031
  • 17
  • 33
  • 41
jeph
  • 72
  • 5
  • Take `str(queryset.query)`, and copypaste it into your database. It won't work, regardless of wether the column is there or not. – Saturnix Aug 20 '19 at 15:59
  • In the expression "WHERE "App_table"."date" >= 2011-1-1" Postgres interposes the operand 2011-1-1 as "integer-integer-integer" evaluating in this case to the integer 2009. So the statement effectively becomes "date >= integer", and that is an invalid comparison. If you an expression to be a date you must tell Postgres that's what you want. Try WHERE "App_table"."date" >= '2011-1-1'::date. – Belayer Aug 20 '19 at 16:34