Opened 4 years ago

Closed 4 years ago

Last modified 5 weeks ago

#17741 closed Bug (invalid)

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

Reported by: anonymous Owned by: nobody
Component: Database layer (models, ORM) Version: 1.3
Severity: Normal Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no


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('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/", line 34, in execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python2.7/dist-packages/django/db/backends/mysql/", line 86, in execute
    return self.cursor.execute(query, args)
  File "/usr/lib/pymodules/python2.7/MySQLdb/", line 168, in execute
    if not self._defer_warnings: self._warning_check()
  File "/usr/lib/pymodules/python2.7/MySQLdb/", 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=()

Change History (4)

comment:1 Changed 4 years ago by Alex

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Resolution set to invalid
  • Status changed from new to closed

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

comment:2 Changed 4 years ago by anonymous

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

comment:3 Changed 4 years ago by aaugustin

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.

comment:4 Changed 5 weeks ago by zachborboa

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(

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'
Note: See TracTickets for help on using tickets.
Back to Top