[Django] #17741: QuerySet.query.__str__() does not generate valid MySQL query with dates
Django
----------------------------------------------+--------------------
Reporter: anonymous | Owner: nobody
Type: Bug | Status: new
Component: Database layer (models, ORM) | Version: 1.3
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------------------+--------------------
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
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/17741>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Reporter: anonymous | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 1.3
(models, ORM) | Resolution: invalid
Severity: Normal | Triage Stage:
Keywords: | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Changes (by Alex):
* status: new => closed
* needs_better_patch: => 0
* resolution: => invalid
* needs_tests: => 0
* needs_docs: => 0
Comment:
This isn't intended to give you valid SQL, just a basic representation of
the query.
--
Ticket URL: <https://code.djangoproject.com/ticket/17741#comment:1>
Django
Reporter: anonymous | Owner: nobody
Component: Database layer | Version: 1.3
(models, ORM) | Resolution: invalid
Severity: Normal | Triage Stage:
Keywords: | Unreviewed
Has patch: 0 | Needs documentation: 0
Comment (by anonymous):
Is there another way to get valid sql, without executing the query?
--
Ticket URL: <https://code.djangoproject.com/ticket/17741#comment:2>
Django
Reporter: anonymous | Owner: nobody
Component: Database layer | Version: 1.3
(models, ORM) | Resolution: invalid
Severity: Normal | Triage Stage:
Keywords: | Unreviewed
Has patch: 0 | Needs documentation: 0
Comment (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.
--
Ticket URL: <https://code.djangoproject.com/ticket/17741#comment:3>
Django
Reporter: anonymous | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 1.3
(models, ORM) |
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by zachborboa):
If you need the correct parameter substitution, use `EXPLAIN`. Then remove
the first 8 chars.
{{{#!python
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'
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/17741#comment:4>