Opened 6 years ago

Closed 6 years ago

#10942 closed (invalid)

Cannot pass a string of comma separated values or an iterable to `params` or `select_params` (e.g. for use with IN lookup).

Reported by: mrmachine Owned by: nobody
Component: Database layer (models, ORM) Version: master
Severity: Keywords: extra params select select_params iterable sequence
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description

As per the google groups discussion, I'm seeing inconsistent results when I use extra() with select and select_params arguments compared to injecting my params directly into the select argument.

As per Malcolm's suggestion, I've now also tried using django.db.connection and pysqlite2 to execute the raw SQL, as well as copy & pasting the generated SQL directly into the sqlite3 prompt.

Everything works except using extra() with select and select_params arguments.

>>> from django.contrib.auth.models import User

>>> # using `select_params` fails, with both the specified users being returned with `featured` as 0.
>>> q1 = User.objects.extra(
...     select={'featured': 'auth_user.id IN (%s)'},
...     select_params=['1,2']
... ).values('featured', 'pk', 'username').order_by('-featured', 'pk', 'username')
>>> print list(q1)
[{'username': u'admin', 'pk': 1, 'featured': 0}, {'username': u'manager', 'pk': 2, 'featured': 0}]

>>> # using only `select` works, with both the specified users being returned with `featured` as 1.
>>> q2 = User.objects.extra(
...     select={'featured': 'auth_user.id IN (%s)' % '1,2'}
... ).values('featured', 'pk', 'username').order_by('-featured', 'pk', 'username')
>>> print list(q2)
[{'username': u'admin', 'pk': 1, 'featured': 1}, {'username': u'manager', 'pk': 2, 'featured': 1}]

>>> # the sql generated for both querysets is identical.
>>> str(q1.query) == str(q2.query)
True

>>> # ths sql generated looks correct.
>>> print q1.query
SELECT (auth_user.id IN (1,2)) AS "featured", "auth_user"."id", "auth_user"."username" FROM "auth_user" ORDER BY "featured" DESC, "auth_user"."id" ASC, "auth_user"."username" ASC

>>> # using django to execute raw sql works as expected.
>>> from django.db import connection
>>> cursor = connection.cursor()
>>> cursor.execute(str(q1.query)).fetchall()
[(1, 1, u'admin'), (1, 2, u'manager')]

>>> # using pysqlite2 to execute raw sql works as expected.
>>> from django.conf import settings
>>> from pysqlite2 import dbapi2
>>> connection = dbapi2.Connection(settings.DATABASE_NAME)
>>> cursor = dbapi2.Cursor(connection)
>>> cursor.execute(str(q1.query)).fetchall()
[(1, 1, u'admin'), (1, 2, u'manager')]

# using sqlite3 to execute raw sql works as expected.
sqlite> SELECT (auth_user.id IN (1,2)) AS "featured", "auth_user"."id", "auth_user"."username" FROM "auth_user" ORDER BY "featured" DESC, "auth_user"."id" ASC, "auth_user"."username" ASC
1|1|admin
1|2|manager

Change History (4)

comment:1 Changed 6 years ago by Alex

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

The issue is your passing a string in the first one, doing str(query) isn't guarnteed to return the exact SQL because of quoting issues. Using query.as_sql() to see the difference.

comment:2 Changed 6 years ago by mrmachine

  • Resolution invalid deleted
  • Status changed from closed to reopened

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

comment:3 Changed 6 years ago by mrmachine

  • Keywords params iterable sequence added
  • Summary changed from Using `select` and `select_params` fails, but using only `select` works, and the SQL generated for both is identical. to Cannot pass a string of comma separated values or an iterable to `params` or `select_params` (e.g. for use with IN lookup).

comment:4 Changed 6 years ago by Alex

  • Resolution set to invalid
  • Status changed from reopened to closed

I'm closing this again, since these methods work the exact same way as the raw API does.

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