3

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');
Community
  • 1
  • 1
ramesh
  • 1,187
  • 7
  • 19
  • 42

3 Answers3

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

Paul Becotte
  • 9,767
  • 3
  • 34
  • 42
  • 1
    Thanks! `cur.execute("SELECT * FROM sample WHERE sub = ANY(%s);", (list,))` worked :) Man page: http://initd.org/psycopg/docs/usage.html#lists-adaptation – ramesh Sep 13 '16 at 01:53
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.

redneb
  • 21,794
  • 6
  • 42
  • 54
-4

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

AMiller78
  • 1