Passing Array Parameter to SQL command

Name: python - Passing Array Parameter to SQL command - Stack Overflow
Rating: 4.4 (2644 reviews)

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, CommentedJun 2, 2015 at 6:41
What's wrong with "select * from my_table where id in (%d,%d)", [10,2]) ? — Burhan Khalid, CommentedJun 2, 2015 at 6:44
the list isn't fixed size. also %d works with regular python string formatting, but it doesn't work in this context. — clay, CommentedJun 2, 2015 at 6:48
Why do you use an array, and not a tuple, like this : ([10,2],) ? — FinalState, CommentedJun 2, 2015 at 6:54

f43d65 · Accepted Answer · 2015-06-02 10:54:00Z

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])]))

answered Jun 2, 2015 at 7:06

2,30212 silver badges15 bronze badges

The reason being that the IN (1,2,3) doesn't feature an array anywhere but rather a literal set of values.
– Richard Huxton
CommentedJun 2, 2015 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: stackoverflow.com/questions/8671702/…
– Anytoe
CommentedFeb 1, 2023 at 17:24

1 Answer 1