Django's str(queryset.query) returns invalid SQL if I compare datetimes

[Jo]

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?

    

[Cornelis Poppema]

What makes sense to me is the query builder (ORM) in Django "escapes"/quotes the values at the very last moment: whenever the query is to be executed in a database. Different databases can have different escape characters. When you print queryset.query it simply isn't at a stage where the escape characters have been added. No "default" quoting is happening because Django has no implementation for this: all escaping is off-loaded to the specific database packages if possible (e.g. MySQLdb, psycopg2, sqlite3). Simply put: queryset.query isn't meant to output valid sql as-is, nobody claims as much. In fact, checking the source code actually underlines my point:

https://github.com/django/django/blob/8a281aa7fe76a9da2284f943964a9413697cff1f/django/db/models/sql/query.py#L253-L262

def __str__(self):

    """

    Return the query as a string of SQL with the parameter values

    substituted in (use sql_with_params() to see the unsubstituted string).

    Parameter values won't necessarily be quoted correctly, since that is

    done by the database interface at execution time.

    """

    sql, params = self.sql_with_params()

    return sql % params

[wd]

QuerySet.query is not a plain string, it's an Query object, you can check the source code

def __str__(self):
    """
    Return the query as a string of SQL with the parameter values
    substituted in (use sql_with_params() to see the unsubstituted string).

    Parameter values won't necessarily be quoted correctly, since that is
    done by the database interface at execution time.
    """
    sql, params = self.sql_with_params()
    return sql % params

 

It will not quote the params. Here is a way to get the raw sql, https://code.djangoproject.com/ticket/17741#comment:4 , or if you can use django-extensions ,    set  SHELL_PLUS_PRINT_SQL = True  to print sql automatically.

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/3f7a7f2d-2e3d-4167-8b77-2b1c7da5446d%40googlegroups.com.

[Abdulrasheed Ibrahim]

What  is your model, is 'date' a DateField or a CharField? 

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CABexzmiKds1bK5Kdy5bhxwgJbovfvdbCCMtNLJMtPcW7XSGJ_w%40mail.gmail.com.