我正在使用psycopg2
通过Python 3访问PostgreSQL数据库的方法,并且尝试进行查询,如果该列表不为null,那么我希望在其中选择名称在列表中的所有用户。如果提供的列表为空,那么我想忽略该条件,即选择所有用户,而不管其名称如何。
我已经尝试了以下三个电话:
# 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 ()
对于可选参数,您需要一个SQLwhere
子句,例如:
where column = :parameter or :parameter is null
使用上面的参数时,is null
将返回所有行,否则仅返回那些符合条件的行。
Psycopg使Python适用list
于Postgresql array
。要检查任何Postgresqlarray
值是否等于某个值:
where column = any (array[value1, value2])
要从空Python获取None
适合于Postgresqlnull
的Python list
:
parameter = [] or None
将a传递dictionary
给该cursor.execute
方法可避免在parameters参数中重复参数:
names = ['John','Mary']
query = """
select age
from user
where user.name = any (%(names)s) or %(names)s is null
"""
print (cursor.mogrify(query, {'names': names or None}).decode('utf8'))
#cursor.execute(query, {'names': names or None})
输出:
select age
from user
where user.name = any (ARRAY['John', 'Mary']) or ARRAY['John', 'Mary'] is null
当列表为空时:
select age
from user
where user.name = any (NULL) or NULL is null
http://initd.org/psycopg/docs/usage.html#passing-parameters-to-sql-queries
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句