Passing Array Parameter to SQL command

Question

In Python 2.7, I can do this pass a parameter to an sql command like this:

cursor.execute("select * from my_table where id = %s", [2])

I can not get the array equivalent working like this:

cursor.execute("select * from my_table where id in %s", [[10,2]])

Obviously, I can just do string formatting, but I would like to do a proper parameter if possible. I'm using a postgresql database if that matters.

`I would like to do a proper parameter if possible` - what do you mean by that? — thefourtheye, Jun 02 '15 at 06:41
What's wrong with `"select * from my_table where id in (%d,%d)", [10,2]) `? — Burhan Khalid, Jun 02 '15 at 06:44
the list isn't fixed size. also %d works with regular python string formatting, but it doesn't work in this context. — clay, Jun 02 '15 at 06:48
Why do you use an array, and not a tuple, like this : `([10,2],)` ? — FinalState, Jun 02 '15 at 06:54

f43d65 · Answer 1 · 2015-06-02T10:54:00.297

7

cursor.execute("select * from my_table where id = ANY(%s);", [[10, 20]])

See note. To use IN see section below.

cursor.execute(cursor.mogrify("select * from my_table where id in %s",
                              [tuple([10, 20])]))

edited Jun 02 '15 at 10:54

answered Jun 02 '15 at 07:06

f43d65

The reason being that the `IN (1,2,3)` doesn't feature an array anywhere but rather a literal set of values. – Richard Huxton Jun 02 '15 at 10:42
For whoever happens to land here: I actually needed to add a comma after the list `...[tuple([10, 20],)...` to make it work. See also accepted answer here: https://stackoverflow.com/questions/8671702/passing-list-of-parameters-to-sql-in-psycopg2 – Anytoe Feb 01 '23 at 17:24

1 Answers1