如何为PyGreSQL查询正确格式化多类型参数？

问题背景 #

使用PyGreSQL执行数据库查询时，需适配三种参数场景：列表值、单值、通配符（匹配整列），原查询及参数如下：

查询语句：

SELECT *
FROM Database
WHERE job_level = ANY(:job_level)
AND job_family = ANY(:job_family)
AND cost_center = ANY(:cost_center)

参数：

{
    'cost_center': '*',
    'job_family': 'SDE',
    'job_level': ['5', '6', '4', '7'],
}

此前尝试的两种方式均失败：

1. 改用IN运算符：

ERROR: syntax error at or near ":"
LINE 4: WHERE job_level IN :job_level

2. 使用%(job_level)s占位符：

ProgrammingError: ERROR: syntax error at or near "ANY"
LINE 4: WHERE job_level IN ANY(ARRAY['5','6','4','7'...

正确解决方案 #

1. 核心原理 #

PyGreSQL会自动将Python列表转换为PostgreSQL数组，因此=ANY(:param)语法对列表和单值均有效（单值会被转为单元素数组，ANY可正常匹配）。问题核心在于通配符*的处理——PostgreSQL中*不是匹配所有的通配符，需要单独处理逻辑。

2. 分场景处理方案 #

方式一：动态构建查询条件（推荐）

根据参数是否为*，决定是否添加对应WHERE条件，避免无效匹配逻辑：

# 基础查询模板
base_query = "SELECT * FROM Database"
conditions = []
params = {}

# 处理job_level（列表值）
conditions.append("job_level = ANY(:job_level)")
params['job_level'] = ['5', '6', '4', '7']

# 处理job_family（单值）
conditions.append("job_family = ANY(:job_family)")
params['job_family'] = 'SDE'

# 处理cost_center（通配符匹配整列）
cost_center_param = '*'
if cost_center_param != '*':
    conditions.append("cost_center = ANY(:cost_center)")
    params['cost_center'] = cost_center_param

# 拼接最终查询
final_query = f"{base_query} WHERE {' AND '.join(conditions)}" if conditions else base_query

# 执行查询
cursor.execute(final_query, params)

方式二：将通配符转换为全匹配逻辑

若不想动态构建查询，可在SQL中直接处理通配符逻辑：

SELECT *
FROM Database
WHERE 
    job_level = ANY(:job_level)
    AND job_family = ANY(:job_family)
    AND (:cost_center = '*' OR cost_center = ANY(:cost_center))

参数保持原结构即可，当cost_center为*时，:cost_center = '*'为真，该条件自动匹配所有行。

3. 常见错误规避 #

• 不要混用IN和占位符：IN需要括号包裹，PyGreSQL不支持直接将列表绑定到IN (:param)，必须用=ANY(:param)替代。
• 禁止手动拼接参数：避免SQL注入风险，始终使用参数绑定方式传递值。
• 明确通配符语义：PostgreSQL中*不是模糊匹配符（模糊匹配用LIKE '%xxx%'），此处的*是自定义的"匹配整列"标识，需单独处理。

内容的提问来源于stack exchange，提问作者bigred_bluejay