#10942: Using `select` and `select_params` fails, but using only `select` works,
and the SQL generated for both is identical.
---------------------------------------------------+------------------------
Reporter: mrmachine | Owner: nobody
Status: reopened | Milestone:
Component: Database layer (models, ORM) | Version: SVN
Resolution: | Keywords: extra
select select_params
Stage: Unreviewed | Has_patch: 0
Needs_docs: 0 | Needs_tests: 0
Needs_better_patch: 0 |
---------------------------------------------------+------------------------
Changes (by mrmachine):
* status: closed => reopened
* resolution: invalid =>
Comment:
After discussion on IRC with Alex, it looks like the problem is that you
cannot pass a string of comma separated values or an iterable (list of
PKs) as a param as neither will be properly escaped. The following works
(using an example taken from the Django source):
{{{
>>> from django.contrib.auth.models import User
>>> params = [1, 3]
>>> q1 = User.objects.extra(
... select={'featured': 'auth_user.id IN (%s)' % ', '.join(['%s'] *
len(params))},
... select_params=params
... ).values('featured', 'pk', 'username').order_by('-featured', 'pk',
'username')
>>> print list(q1)
[{'username': u'admin', 'pk': 1, 'featured': 1}, {'username': u'manager',
'pk': 2, 'featured': 0}]
}}}
I think that is a bit of a hack. It's not easy to read, and it requires
the user to flatten any iterators in their list of params and to know the
number of items in their iterator param, and add an appropriate number of
'%s' hooks in their SQL.
Django should know how to properly escape an iterator param, by wrapping
it in parenthesis, escaping each value and joining with ', '. E.g. the
following should work:
{{{
>>> from django.contrib.auth.models import User
>>> q1 = User.objects.extra(
... select={'featured': 'auth_user.id IN %s'},
... select_params=[[1, 3]]
... ).values('featured', 'pk', 'username').order_by('-featured', 'pk',
'username')
>>> print list(q1)
[{'username': u'admin', 'pk': 1, 'featured': 1}, {'username': u'manager',
'pk': 2, 'featured': 0}]
}}}
But instead, I get:
{{{
Traceback (most recent call last):
File "<console>", line 1, in ?
File "/path/to/django/db/models/query.py", line 163, in __len__
self._result_cache.extend(list(self._iter))
File "/path/to/django/db/models/query.py", line 740, in iterator
for row in self.query.results_iter():
File "/path/to/django/db/models/sql/query.py", line 244, in results_iter
for rows in self.execute_sql(MULTI):
File "/path/to/django/db/models/sql/query.py", line 1980, in execute_sql
cursor.execute(sql, params)
File "/path/to/django/db/backends/util.py", line 19, in execute
return self.cursor.execute(sql, params)
File "/path/to/django/db/backends/sqlite3/base.py", line 190, in execute
return Database.Cursor.execute(self, query, params)
OperationalError: near "?": syntax error
}}}
I'm re-opening this for further consideration as a feature enhancement or
documentation change. At the very least, the documentation should mention
that you cannot use an iterator as a param and must flatten your list of
params and hack the SQL
--
Ticket URL: <http://code.djangoproject.com/ticket/10942#comment:2>
Django <http://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To post to this group, send email to django-updates@googlegroups.com
To unsubscribe from this group, send email to
django-updates+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/django-updates?hl=en
-~----------~----~----~----~------~----~------~--~---
