QuerySet.query.__str__() does not generate valid MySQL query with dates

[anonymous]

If you have a date parameter in a QuerySet, the string of the query is not valid SQL in MySQL and will generate a warning.

Example (the model Entry has a DateField "post_date"):

>>> from datetime import date
>>> from django.db import connection
>>> from myblog.models import Entry
>>> todays_entries = Entry.objects.filter(post_date=date.today()).values('id')
>>> str(todays_entries.query)
'SELECT `myblog_entry`.`id` FROM `myblog_entry` WHERE `myblog_entry`.`post_date` = 2012-02-21 '
>>> cursor = connection.cursor()
>>> cursor.execute(str(todays_entries.query))
DEBUG:django.db.backends:(0.121) SELECT `myblog_entry`.`id` FROM `myblog_entry` WHERE `myblog_entry`.`post_date` = 2012-02-21 ; args=()
Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/usr/local/lib/python2.7/dist-packages/django/db/backends/util.py", line 34, in execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python2.7/dist-packages/django/db/backends/mysql/base.py", line 86, in execute
    return self.cursor.execute(query, args)
  File "/usr/lib/pymodules/python2.7/MySQLdb/cursors.py", line 168, in execute
    if not self._defer_warnings: self._warning_check()
  File "/usr/lib/pymodules/python2.7/MySQLdb/cursors.py", line 82, in _warning_check
    warn(w[-1], self.Warning, 3)
Warning: Incorrect date value: '2012' for column 'post_date' at row 1

This query also fails with a warning in the MySQL command line:

> use myblog;
Database changed
> SELECT `myblog_entry`.`id` FROM `myblog_entry` WHERE `myblog_entry`.`post_date` = 2012-02-21;
Empty set, 1 warning (0.09 sec)

Putting quotes around the date solves the problem.

>>> from django.db import connection
>>> query_string = 'SELECT `myblog_entry`.`id` FROM `myblog_entry` WHERE `myblog_entry`.`post_date` = "2012-02-21" '
>>> cursor = connection.cursor()
>>> cursor.execute(str(todays_entries.query))
DEBUG:django.db.backends:(0.121) SELECT `myblog_entry`.`id` FROM `myblog_entry` WHERE `myblog_entry`.`post_date` = "2012-02-21" ; args=()
7L

[Alex Gaynor]

This isn't intended to give you valid SQL, just a basic representation of the query.

[anonymous]

Is there another way to get valid sql, without executing the query?

[Aymeric Augustin]

There isn't, because Django never actually interpolates the parameters: it sends the query and the parameters separately to the database adapter, which performs the appropriate operations.

[Zach Borboa]

If you need the correct parameter substitution, use EXPLAIN. Then remove the first 8 chars.

In [1]: import datetime

In [2]: from django.db import connection

In [3]: from myapp.models import Entry

In [4]: today_entry_list = Entry.objects.filter(post_date=datetime.date.today())

In [5]: sql, params = today_entry_list.query.sql_with_params()

In [6]: cursor = connection.cursor()

In [7]: cursor.execute('EXPLAIN ' + sql, params)
Out[7]: 1L

In [8]: print(cursor.db.ops.last_executed_query(cursor, sql, params))
EXPLAIN SELECT `myapp_entry`.`id`, `myapp_entry`.`title`, `myapp_entry`.`slug`, `myapp_entry`.`body`, `myapp_entry`.`post_date` FROM `myapp_entry` WHERE `myapp_entry`.`post_date` = '2016-04-04'