我正在使用
psycopg2
以Python 3访问PostgreSQL数据库,我试图进行查询,其中我想选择名字在列表中的所有用户,如果列表不为空。 如果提供的列表为空,则希望忽略该条件,即选择所有用户而不考虑其名称。
我已经尝试了以下三种方法:
# Using list
cursor.execute(
"SELECT age FROM user WHERE %(names) = '{}' OR user.name IN %(names)s",
{'names': []},
)
# Using tuple
cursor.execute(
"SELECT age FROM user WHERE %(names) = () OR user.name IN %(names)s",
{'names': ()},
)
# Using both list and tuple
cursor.execute(
"SELECT age FROM user WHERE %(names_l) = '{}' OR user.name IN %(names_t)s",
{'names_l': [], 'names_t': ()},
)
但是它们都会在某个地方引发无效语法错误:
# Using list
psycopg2.ProgrammingError: syntax error at or near "'{}'"
LINE 17: user.name IN '{}'
# Using tuple
psycopg2.ProgrammingError: syntax error at or near ")"
LINE 16: () == ()
# Using both list and tuple
psycopg2.ProgrammingError: syntax error at or near ")"
LINE 17: user.name IN ()