Use python list IN postgresql query

Question

My question is very similar to this but I have strings instead of integers in my list.

My python list:

list = ['a', 'b'] #number of items varies from 1 to 6

I want to use this list in Postgres query like this

select * from sample where sub in ('a', 'b');

I can use tuple(list) to get ('a', 'b'), this is not useful when length of my list became one. I am struggling to find a way to convert

['a'] to ('a')
['a', 'b'] to ('a', 'b')

I tried

In[91]: myquery = "select * from sample where sub in (%s)" % ",".join(map(str,list))
In[92]: myquery
Out[92]: 'select * from sample where sub in (a,b,c)'

But postgres expects

select * from sample where sub in ('a', 'b');

Postgres expects single quotes for strings: select * from sample where sub in ('a','b','c'). Double quotes are for identifiers. — Erwin Brandstetter
– Erwin Brandstetter, Commented Sep 13, 2016 at 0:27
Thanks for that, I just copy pasted here. I will correct that. — ramesh
– ramesh, Commented Sep 13, 2016 at 0:28
"select * from sample where sub in (%s)" % ",".join(map(repr, lst)) or q = "select * from sample where sub in (%s)" % ",".join(map("'{}'".format, lst)) — Padraic Cunningham
– Padraic Cunningham, Commented Sep 13, 2016 at 0:43

Paul Becotte · Accepted Answer · 2016-09-13 01:36:47Z

5

Use psycopg2 and it will handle this for you correctly for all sorts of edge cases you haven't thought of yet. For your specific problem see http://initd.org/psycopg/docs/usage.html#adapt-tuple

answered Sep 13, 2016 at 1:36

Paul Becotte

9,9973 gold badges36 silver badges43 bronze badges

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

1 Comment

ramesh Over a year ago

Thanks! cur.execute("SELECT * FROM sample WHERE sub = ANY(%s);", (list,)) worked :) Man page: initd.org/psycopg/docs/usage.html#lists-adaptation

redneb · Accepted Answer · 2016-09-13 01:27:04Z

3

I haven't used python's bindings to postgresql so I don't know if it is possible to bind a python list (or a tuple) to a placeholder in a query, but if it is, then you could use the ANY operator as follows:

SELECT * FROM sample WHERE sub = ANY (%s);

and bind the list the only parameter.

answered Sep 13, 2016 at 1:27

redneb

24.3k7 gold badges46 silver badges55 bronze badges

Comments

AMiller78 · Accepted Answer · 2016-09-13 00:31:38Z

-4

Try ",".join(["'{0}'".format(s) for s in list]).

answered Sep 13, 2016 at 0:31

AMiller78

1

1 Comment

ramesh Over a year ago

Thanks! It gives "'a','b'", not ('a', 'b')

Collectives™ on Stack Overflow

Use python list IN postgresql query

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related