Python psycog2 |在where子句中使用元组或数组

Question

我有一个如下所示的元组

vilist = (1,2,3,4)

我试图在Psycopg2查询中使用它们，如下所示

sql = "select * from temp.table1 where ids in {}"
cur.execute(sql,vilist)

应该解析像

这样的SQL字符串

SELECT * FROM temp.table1 WHERE ids IDS (1,2,3,4);

但是，我得到如下所示的错误

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 {}

请帮我解决这个错误。

Answer 1

使用SQL字符串组合将标识符或文字传递给查询文本。

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)