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

Question

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 1

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.

Answer 2

Updated Answer Sorry about the older answer. This should work as you want, but might not be the best solution. I tried it with both, params as a list of strings and a list of integers.

from django.db import connection

params = [1, 2, 3, '4', '5']
placeholders = ('%s,'*len(params)).rstrip(',')  # Having a comma at the end will give a syntax error

with connection.cursor() as cursor:
    cursor.execute('SELECT * FROM mytable where id IN ({})'.format(placeholders), params)
    #  Use the cursor here 

End updated answer

---

In SQL the value for IN needs to be a comma separated list

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...);

So the simplest approach for you, assuming that params is an iterable would be

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

The join will convert your iterable params into a comma separated string which will then replace the %s.

Generally you will want your query params to be escaped. Even the data you stored should be escaped.

Edit: Also notice that I have moved your cursor into a with block. You should always close your connections. Read up more here.

Answer 3

Solution is rather simple:

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

See similar topic: imploding a list for use in a python MySQLDB IN clause