ups ups - 3 months ago 9
SQL Question

passing many arguments to one placeholder of django cursor (placeholer in IN)

I mean something like this:

from django.db import connection
cursor=connection.cursor()
cursor.execute('SELECT * FROM mytable where id IN (%s)', [params])


Parameters can not be just iterable - it doesn't work.
Cannot be also in CSV format due escaping value by db handler.

How to use placeholder within
IN
?




By CSV is wrong, I mean that for
params=['1','2','3','4','5']


c.execute('select * from mytable where id in (%s)', [','.join(params)])


will produce:

select * from mytable where id in ('1,2,3,4,5')


but correct sql is:

select * from mytable where id in (1,2,3,4,5)


and it seems to be hard to achieve with placeholders.

Answer

You cannot use IN for this since indeed, in expects a sequence of ints, but the ORM translates a list to an ARRAY, and if you use join you'll end up with a string.

The solution is to use the equivalent ANY. The formatting is slightly different, but this should work for you:

c.execute('select * from mytable where id = ANY(%s)', [params])

Given params = [1, 2, 3, 4, 5], the resulting SQL will be:

SELECT * FROM mytable where id = ANY(ARRAY[1, 2, 3, 4, 5])

Note that this requires the list of ids to be made up of ints, so if you have a list of strings be sure to convert them first.