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

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

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.