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
– thefourtheye, Commented Jun 2, 2015 at 6:41
What's wrong with "select * from my_table where id in (%d,%d)", [10,2]) ? — Burhan Khalid
– Burhan Khalid, Commented Jun 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
– clay, Commented Jun 2, 2015 at 6:48
Why do you use an array, and not a tuple, like this : ([10,2],) ? — FinalState
– FinalState, Commented Jun 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])]))

edited Jun 2, 2015 at 10:54

answered Jun 2, 2015 at 7:06

f43d65

2,30213 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Richard Huxton Over a year ago

The reason being that the IN (1,2,3) doesn't feature an array anywhere but rather a literal set of values.

Anytoe Over a year ago

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/…

Collectives™ on Stack Overflow

Passing Array Parameter to SQL command

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related