Как формировать сырые SQL запросы максимально эффективно?

Question

[asker123123]

Всем привет, возник вопрос. Вот у меня есть функция которая возвращает данные из бд, в эту функцию опционально приходят от 1 до 5(Условно) аргументов, по которым(при наличии) должна формироваться фильтрация. Как правыильно сделать подстановку аргументов, чтобы не мучаться с бесконечными проверками и прорабатыванием всех вариантов. (ОРМ не подходит, по некоторым причинам).

Пример кода

def get_user(*, username: str = None, email: str = None, id: int = None, is_blocked: bool = None):
    SQL = "SELECT * FROM USERS {} LIMIT %s OFFSET %s"
    more = []
    if username:
        more.append("WHERE username = '%s'" % username)
    if email:
        if any([username]):
            more.append("& email = '%s'" % email)
        else:
            more.append("WHERE email = '%s'" % email)
    if id:
        if any([username, email]):
            more.append("& id = '%s'" % id)
        else:
            more.append("WHERE id = '%s'" % id)
    ...
    cursor.execute(SQL.format(" ".join(more)))

Comments

[Speakermen]

Забыл я python, но думаю что то типа

function orm() {
  let value;
  let sql;

  return {
    table: function (input) {
      value = input;
      return this;
    },
    findAll: function () {
      sql = `SELECT * FROM ${value}`;
      return this;
    },
    where: function (key, value) {
      sql += ` WHERE ${key} = ${value}`;
      return this;
    },
    create() {
      return sql;
    },
  };
}

bilder = orm();

console.log(
  bilder.table("Customers").findAll().where("city", "London").create()
);

//SELECT * FROM Customers WHERE city = London

Answer 1

[mayton2019]

Я в самодельных билдерах запросов добавлял фейковый предикат 1=1 который всегда был. Тогда добавление новых предикатов делается проще. Вот как-то так.

def get_user(*, username: str = None, email: str = None, id: int = None, is_blocked: bool = None):
    SQL = "SELECT * FROM USERS {} LIMIT %s OFFSET %s WHERE 1=1 "
    more = []
    if username:
        more.append("AND username = '%s'" % username)
    if email:
        if any([username]):
            more.append("AND email = '%s'" % email)
    if id:
        if any([username, email]):
            more.append("AND id = '%s'" % id)

Я проверял этот код на валидность. Это лишь идея как сделать. Поэтому исправляй дальше сам.

Answer 2

[Akina]

Обычно если в запрос может быть передано значение, а может и нет, делают так:

... AND (column = {parameter} OR {parameter} IS NULL) ..

При подстановке параметра сервер уже на этапе построения плана выполнения запроса, зная значение {parameter}, получит либо
... AND (column = {parameter} OR FALSE) .. ==> ... AND (column = {parameter}) ..
либо
... AND (column = {parameter} OR TRUE) .. ==> ... AND (TRUE) ...

PS. Чтобы не геморроиться с определением, когда ставить WHERE, начальный шаблон делают такой:

SQL = "SELECT * FROM USERS WHERE 1=1 {} LIMIT %s OFFSET %s"

и все дополнительные условия формируют в виде
AND {условие}
Тогда не надо оглядываться, первое это условие или нет.

Само же условие 1=1 будет обращено в TRUE и отброшено опять-таки на этапе построения плана.

Comments

[asker123123]

Как я понял - ваш вариант идеально подходит для меня. Только не совсем понял зачем 1=1, если выражение

... AND (column = {parameter} OR {parameter} IS NULL) ..

само по себе опционально и решит всё за меня. Я бы сразу написал полный запрос, со всеми возможными параметрами, и передавал аргументы, а postgres сам бы все порешал остальное.

SQL = "SELECT * FROM users WHERE (username=%s OR %s IS NULL) AND(id=%s OR %s IS NULL) ... LIMIT 10 OFFSET 10 "
cursor.execute(SQL, [username, username, id, id, ...,...])

+В этом случае курсор занимается безопасностью от инъекций как я понял.

[Akina]

asker123123,

не совсем понял зачем 1=1

А чтобы не городить кучу IF и IF ANY(), как в приведённом в вопросе коде, стараясь вставить в вопрос строго один WHERE.

Answer 3

[Dimonchik]

params = {"id": "Fuck",   "email":"You",   "username": "Spielberg"}

paramkeys = list(params.keys())

where_clause = "WHERE " + " AND ".join([f"{col} = %s" for col in paramkeys])

sql = f"SELECT * FROM users {where_clause} LIMIT %s OFFSET %s"

Answer 4

[Сергей delphinpro]

Нафига столько лишних условий. Просто закидываем все условия в массив и объедияем его в строку

where = []
if username:
    where.append("username = '%s'" % username)
if email:
    where.append("email = '%s'" % email)
if id:
    where.append("id = '%s'" % id)
...
whereString = "WHERE " + " AND ".join(where);

Ну там еще добавить проверку в конце на пустоту where и тогда whereString оставить пустым.
Или как выше отметили, добавить условие 1=1

where = []
where.append("1 = 1")
if username:
...

Comments

[Jack444]

уязвимый код, если в юзернейм или емаил воткнуть "'; SELECT 'Привет';" то ему нечего не мешает выполнится))

[Сергей delphinpro]

Jack444, ну так sql инъекции находятся за рамками этого вопроса.

Answer 5

[Stalker_RED]

Отвечая на вопрос из заголовка - никак.

План исполнения запроса делается самим движком СУБД. Вы можете оптимизировать конкретный запрос проиграв с индексами, джоинами, оконными функциями и т.п.
Но вы не можете написать "универсальный автоматический построитель оптимизированных запросов на все случаи жизни".

ОРМ даже не пытается в производительность, они пытаются сделать "удобненько для людей слабо знакомых с sql".

Comments

[Сергей delphinpro]

Не думаю, что автор спрашивает об оптимизации и эффективности выполнения запроса. Скорее о чистоте и краткости кода своего импровизированного построителя запроса.

[asker123123]

Сергей delphinpro, Да, я в основном про это. До оптимизации запросов по эффективности выполнения мне еще очень далеко