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');

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

Answer 1

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

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

Answer 2

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.

Answer 3

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

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