sql - 从 Django 传递可选列表参数以在原始 SQL 中进行过滤

Question

当使用像 Integer 这样的基本类型时，我可以毫无问题地进行这样的查询:

with connection.cursor() as cursor:
    cursor.execute(sql='''SELECT count(*) FROM account 
        WHERE %(pk)s ISNULL OR id %(pk)s''', params={'pk': 1})

它将返回 id = 1 的行，或者如果 pk 参数等于 None<，它将返回 所有 行.

但是，当尝试使用类似的方法传递 ID 的列表/元组时，我总是在传递空/无元组时产生 SQL 语法错误，例如尝试:

with connection.cursor() as cursor:
    cursor.execute(sql='''SELECT count(*) FROM account 
        WHERE %(ids)s ISNULL OR id IN %(ids)s''', params={'ids': (1,2,3)})

有效，但传递 () 会产生 SQL 语法错误:

psycopg2.ProgrammingError: syntax error at or near ")"
LINE 1: SELECT count(*) FROM account WHERE () ISNULL OR id IN ()

或者如果我通过 None 我得到:

django.db.utils.ProgrammingError: syntax error at or near "NULL"
LINE 1: ...LECT count(*) FROM account WHERE NULL ISNULL OR id IN NULL

我尝试将参数放入 SQL 中的 () - (%(ids)s) - 但这总是会破坏一个或另一个条件。我还尝试使用 pg_typeof 或转换参数，但没有结果。

注意事项:

• 实际的 SQL 要复杂得多，这里的 SQL 是为了说明目的而进行的简化
• 作为最后的手段 - 我可以根据参数更改 Python 中的 SQL，但我真的想避免这种情况。)

Answer 1

起初我有一个想法，只使用 1 个参数，但将其替换为虚拟值 [-1]，然后像这样使用它

cursor.execute(sql='''SELECT ... WHERE -1 = any(%(ids)s) OR id = ANY(%(ids)s)''', params={'ids': ids if ids else [-1]})

但这对非空列表进行了全表扫描，这很不幸，所以不行。

然后我想我可以在 python 中做一些预处理并发送 2 个参数，而不仅仅是一个列表——实际列表和一个空列表 bool 指示符。也就是

cursor.execute(sql='''SELECT ... WHERE %(empty_ids)s = TRUE OR id = ANY(%(ids)s)''', params={'empty_ids': not ids, 'ids': ids})

不是最优雅的解决方案，但它执行得很好(非空列表的索引扫描，空列表的全表扫描 - 但无论如何都会返回整个表，所以没关系)

最后我想出了最简单且相当优雅的解决方案:

cursor.execute(sql='''SELECT ... WHERE '{}' = %(ids)s OR id = ANY(%(ids)s)''', params={'ids': ids})

这个也对非空列表执行索引扫描，所以速度非常快。