Opened 3 years ago

Closed 3 years ago

Last modified 3 years 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

Description

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

Change History (3)

comment:1 Changed 3 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 3 years ago by anonymous

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

comment:3 Changed 3 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.

Note: See TracTickets for help on using tickets.
Back to Top