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

[Tai Lee]

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

[Alex Gaynor]

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.

[Tai Lee]

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

[Alex Gaynor]

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