Как сделать конструкцию IN %(param_name)s необязательным параметром в строке sql если в качестве значения параметра был предоставлен пустой массив?

Question

[ArtiomK]

Я хочу сделать IN %(param_name)s необязательно частью запроса PostgreSQL (деактевировать фильтр) если в качестве значения параметра был предоставлен пустой массив, получаем все значения, как если бы фильтра для данного параметра не было. То есть выражение

"SELECT * FROM table_name WHERE cam_id IN %(param_name)s;"

будет тождественно "SELECT * FROM table_name; "

Если возможных значений для param_name в Питоне мало, я могу обойти проблему передавая дефолтный список вместо пустого направленного а АПИ. Но я хочу узнать как это делать верно (без костылей).

Пример:

query ="""
SELECT 
 id, cam_id
 FROM sometable
 WHERE id > %(_id)s
 // if use = instead of IN, it works well (of course if cameras is just one value, not array)
 AND (%(camera)s is NULL OR cam_id IN %(camera)s);"""

values = {"_id": 10, camera: tuple(1, 2, 3)]}
curs.execute(query, values)

Какие я получаю ошибки если tuple пустой:

psycopg2.errors.SyntaxError: syntax error at or near ")" LINE 6: WHERE (() is NULL OR cam_id IN ()

Примечание, если заменить IN на = и передавать одиночные значения (не массив), то все отлично работает.

Ранее задавал вопрос здесь

Answer 1

[Melkij]

Не надо так делать.
https://use-the-index-luke.com/sql/where-clause/ob...

Не включайте в запрос условие, если его там не должно быть.

Comments

[ArtiomK]

Melkij ну в ссылке описано как раз что я упомянул в своем примере в вопросе, там строгое = которое прекрасно работаем, но например мне нужен фильр где входным параметр может принимать несколько значений, например ANY, IN, тогда выражение либо не будет работать, либо будет работать неверно - давать 0 строк в ответе, а мне нужно чтобы оно именно отключало фильтр - дает все значение как `SELECT * FROM tablename;`

В моем примере:

// if use = instead of IN, it works well (of course if cameras is just one value, not array)
 AND (%(camera)s is NULL OR cam_id IN %(camera)s);

[Melkij]

ArtiomK, а вот если присмотреться, то по ссылке развёрнутое объяснение почему не надо делать так как вы хотите.

[ArtiomK]

Melkij, то есть другими словами, оптимальное решение не пытаться использоваться красивый синтакис, а смотреть какой набор параметров для фильтров пришел в АПИ от клиента и собирать строку sql средствами языка http сервера (в данном случае Питон), а потом направлять туда полученные от клиента праметры?

[Melkij]

Весьма не соглашусь, что "красивый синтаксис" в вашей идее, а не наоборот, в рядовом построении динамичного sql. Клиент запросил фильтр? Отлично, добавили его к sql запросу и сохранили параметр для bind. Просто, понятно, легко отслеживается, нет магических констант, которые как бы передаются параметрами, но игнорируются, легко планируется базой без фокусов, налагаемых ограничениями prepared stmt.

Да, собирать запрос по частям оптимально. Всевозможные query builder есть даже.

[galaxy]

Melkij,

Не включайте в запрос условие, если его там не должно быть.

не вижу ничего плохого. Драйвер подставит литерал, условие будет использовать индекс

[Melkij]

galaxy, если подставит литерал - то, в основном, такое только замедлит планировщик ненужной никому работой. А вот если там extended protocol с parse/bind/execute последовательностью - то запросто будут фокусы. Особенно вокруг переключения custom или generic планов.
Не надо пытаться запутывать планировщик. Он умный, но он тупой. Потому что ему нужно работать быстро, а не пытаться угадывать что же хотел сказать автор. Довольно много "оптимизаций" планировщика отклоняется именно по этой причине - мы не хотим замедлять абсолютно все запросы (некоторые и так планируются дольше чем собственно исполняются) чтобы исправлять один изначально странно написанный запрос.

[galaxy]

Melkij,

А вот если там extended protocol с parse/bind/execute

нет, psycopg не умеет.

Answer 2

[galaxy]

Если не хотите руками собирать запросы (или с помощью модуля sql), можно использовать списки:

v = {'_id': 10, 'param_name': None}
c.mogrify("SELECT * FROM table_name WHERE %(param_name)s IS NULL OR id = ANY(%(param_name)s)", v)
# b'SELECT * FROM table_name WHERE NULL IS NULL OR id = ANY(NULL)'

v = {"_id": 10, "param_name": [1, 2, 3]}
# b'SELECT * FROM table_name WHERE ARRAY[1,2,3] IS NULL OR id = ANY(ARRAY[1,2,3])'

Вместо None можно и под пустой список подогнать (немного больше возни):

v = {"_id": 10, "param_name": []}
c.mogrify("SELECT * FROM table_name WHERE cardinality(%(param_name)s::int[]) = 0 OR id = ANY(%(param_name)s)", v)
# b"SELECT * FROM table_name WHERE cardinality('{}'::int[]) = 0 OR id = ANY('{}')"

Comments

[galaxy]

С tuple тоже можно, но из-за проблем с синтаксисом нужен небольшой костыль - добавить несуществующий элемент (например None):

v = {'_id': 10, 'param_name': tuple(None,)}
# b'SELECT * FROM table_name WHERE (NULL) IS NULL OR id IN (NULL)'

[ArtiomK]

galaxy, если я правильно понял ваш пример, то это почти равнозначно результату который я сейчас получаю с IN:

SELECT * FROM vworker_tracks WHERE cam_id IS NULL OR id = ANY(NULL);  // ничего

SELECT * FROM vworker_tracks WHERE cam_id IS NULL OR id = ANY('{}'); // тоже ничего

У меня цель получать все строки, как будто бы фильтра не было.

[galaxy]

ArtiomK, в варианте 1 (... WHERE %(param_name)s IS NULL ...) вы должны передавать в param_name - None (не пустой список). В варианте 2 (... WHERE cardinality(%(param_name)s::int[]) = 0 ...) - пустой список.