Python psycog2 | Use a tuple or array in where clause
I have a tuple like as shown below
vilist = (1,2,3,4)
I am trying to use them in Psycopg2 query like as shown below
sql = "select * from temp.table1 where ids in {}"
cur.execute(sql,vilist)
Which should parse a SQL string like
SELECT * FROM temp.table1 WHERE ids IDS (1,2,3,4);
However, I get an error like as shown below
SyntaxError Traceback (most recent call last)
<ipython-input-91-64f840fa2abe> in <module>
1 sql = "select * from temp.table1 where ids in {}"
----> 2 cur.execute(sql,vilist)
SyntaxError: syntax error at or near "{"
LINE 1: ...rom temp.table1 where ids in {}
Can help me resolve this error please.
Use SQL string composition to pass identifiers or literals to a query text.
import psycopg2
import psycopg2.sql as sql
# ...
vilist = (1,2,3,4)
query = sql.SQL("select * from temp.table1 where ids in {}").format(sql.Literal(vilist))
cur.execute(query)