Snaql. Raw SQL в Python-проектах

[KoVexPulThul]

Мне кажется такой подход будет не очень удобен для описания условий посложнее. Например, когда передается несколько независимых условий. Приведу в качестве примера генерацию такого запроса с использованием SQLAlchemy:

 def get_countries(ids=None, date_from=None, date_to=None):
    query = Country.query.order_by(Country.creation_date)
    if ids:
        query = query.filter(Country.id.in_(ids))
    if date_from:
        query = query.filter(Country.creation_date >= date_from)
    if date_to:
        query = query.filter(Country.creation_date <= date_to)
    return query.all()

[to_climb]

Что-то типа (не знаком с jinja для написания примера кода):

• Создаём массив условий WHERE (строки)
• Для каждого не is null параметра добавляем его строку в этот массив
• Если массив условий не пуст, склеиваем их по AND и добавляем в запрос WHERE-условие

[artifex]

Решение в лоб:

{% sql 'get_countries_by_conds', note='get countries by date conditions or ids' %}
    SELECT *
    FROM countries
    {% if ids %}
        WHERE id IN ({{ ids|join(', ') }})
    {% endif %}
    {% if date_from %}
        AND WHERE creation_date >= {{ date_from }}
    {% endif %}
    {% if date_to %}
        AND WHERE creation_date <= {{ date_to }}
    {% endif %}
    ORDER BY creation_date ASC
{% endsql %}

def get_countries(ids=None, date_from=None, date_to=None):
    sql_context = {}
    if ids:
        sql_context['ids'] = ids
    if date_from:
        sql_context['date_from'] = date_from  # + date format 'YYYY-MM-DD'
    if date_to:
        sql_context['date_to'] = date_to  # + date format 'YYYY-MM-DD'

    return country_queries.get_countries_by_conds(**sql_context)

[KoVexPulThul]

Мне кажется, здесь произойдет ошибка т.к. будет составлен некорректный запрос, когда будут переданы параметры date_from и date_to, но не ids

get_countries(date_from='2014-08-11', date_to='2015-01-01')

SELECT *
    FROM countries AND WHERE creation_date >= {{ date_from }} AND WHERE creation_date <= {{ date_to }} ORDER BY creation_date ASC

[artifex]

Ну это легко поправить. Опять же, в лоб.

{% sql 'get_countries_by_conds', note='get countries by date conditions or ids' %}
    SELECT *
    FROM countries
    {% if ids %}
        WHERE id IN ({{ ids|join(', ') }})
    {% endif %}
    {% if date_from %}
        {% if ids %} AND {% endif %} WHERE creation_date >= {{ date_from }}
    {% endif %}
    {% if date_to %}
        {% if ids or date_from %} AND {% endif %} WHERE creation_date <= {{ date_to }}
    {% endif %}
    ORDER BY creation_date ASC
{% endsql %}

Я бы разнёс conditions по своим блокам.

{% sql 'get_countries', note='get countries' %}
    SELECT *
    FROM countries
    {% if conditions %}
        {{ conditions|join(' AND ') }}
    {% endif %}
    ORDER BY creation_date ASC
{% endsql %}

{% sql 'cond_ids_in_countries' %}
    WHERE id IN ({{ ids|join(', ') }})
{% endsql %}

{% sql 'cond_date_from_countries' %}
    WHERE creation_date >= {{ date_from }}
{% endsql %}

{% sql 'cond_date_to_countries' %}
    WHERE creation_date <= {{ date_to }}
{% endsql %}

def get_countries(ids=None, date_from=None, date_to=None):
    sql_conditions = []
    if ids:
        cond = country_queries.cond_ids_in_countries(ids=ids)
        sql_conditions.append(cond)
    if date_from:
        cond = country_queries.cond_date_from_countries(date_from=date_from)
        sql_conditions.append(cond)
    if date_to:
        cond = country_queries.cond_date_to_countries(date_to=date_to)
        sql_conditions.append(cond)

    return country_queries.get_countries(conditions=sql_conditions)

Ну как-то так. Мысль, думаю, уловили.

[BlessMaster]

Имхо, Jinja — лишнее звено в данной схеме.

Jinja — очень узкое подмножество Python, дополненное средствами безопасности (песочница для кода от третьих лиц), кешированием фрагментов, средствами перевода и прочей обработки текста.

Мы не используем эти дополнительные возможности, поскольку они нам бесполезны, при этом сильно ограничиваемся подмножеством и спецификой работы с шаблонами. В то же время в самом Python вполне достаточно средств манипулирования строками для этой задачи и при этом вся мощь этого языка для комбинирования условий.

То же решение «в лоб» в чистом Python (ох плачет по нему напильник: делать работу драйвера по вставке данных и нарываться на SQL-injection — неблагодарное занятие)

# --- sqlutils.py -------------------------------


def wrap_int(num):
    assert isinstance(num, int)
    return str(int(num))


def wrap_dt(dt):
    assert isinstance(dt, datetime.datetime)
    return "'{}'".format(dt.isoformat())


def in_list(lst, wrap=wrap_int):
    return "IN ({})".format(", ".join(wrap(x) for x in lst))


def select(*args):
    if args:
        return "SELECT {}".format(", ".join(args))
    else:
        return "SELECT *"


def where(*args, glue="AND"):
    args = [x for x in args if x]
    if args:
        return "WHERE {}".format(" {} ".format(glue).join(args))
    else:
        return ""


def order_by(column, asc=True):
    return "ORDER BY {} {}".format(column, asc and "ASC" or "DESC")


# --- model.py ----------------------------------


def sql_countries_by_conds(ids=None, date_from=None, date_to=None, asc=True):
    """ get countries by date conditions or ids """

    return """

SELECT *
FROM "countries"
{where}
ORDER BY "creation_date" {asc}

""".format(
        where=where(
            ids is not None and "id {}".format(in_list(ids)),
            date_from and "creation_date >= {}".format(wrap_dt(date_from)),
            date_to and "creation_date <= {}".format(wrap_dt(date_to))
        ),
        asc=asc and "ASC" or "DESC"
    )

Какие преимущества перед Jinja?

1. Всё компактно — не нужно продираться через дебри разметки, не нужно бегать по коду и файлам, не нужно держать в памяти (и ошибаться) из чего же устроен наш запрос.
2. Работает навигация IDE и анализатор кода с рефакторингами и автодополнениями — в любую функцию можно прыгнуть, ошибки и подозрительные места подсвечиваются.
3. Это поддаётся пошаговой отладке, сюда можно поставить точку остановки и проинспектировать состояние.

Возможно я отстал от жизни и последние два пункта с Jinja тоже не проблема в передовых IDE, но первый пункт — никуда не денешь. Ну и всю мощь «питона с батарейками» в противовес «песочнице» я уже упоминал (можно, конечно, перенастроить песочницу в швейцарский нож, благо она это позволяет, но создавалась она явно с противоположной целью и умолчания — против нас).

Как-то так.

[BlessMaster]

P.S. справедливости ради: функции из sqlutils также можно встроить и в окружение Jinja и сильно упростить пример из комментариев выше.

{% sql 'get_countries_by_conds', note='get countries by date conditions or ids' %}
    SELECT *
    FROM "countries"
    {{ where(
            ids != None and "id {}".format(in_list(ids)),
            date_from and "creation_date >= {}".format(wrap_dt(date_from)),
            date_to and "creation_date <= {}".format(wrap_dt(date_to))
        ) }}
    ORDER BY "creation_date" ASC
{% endsql %}

Но, к сожалению, останется сбивающая с толку специфика вроде 'ids != None' вместо 'ids is not None', отсутствие списочных выражений и тому подобные «мелочи», вместо которых придётся писать по старинке map/filter.

[ef_end_y]

Я поступаю проще:

WHERE TRUE и дальше AND condition… AND condition…

[cinic]

Тестировал в одном проекте >=|<= и BETWEEN для аналогичной выборки. Использование BETWEEN отработало быстрее.

[Joes]

Очень неплохая идея, но вот синтаксис не очень.

Было бы отлично если бы тег работал аналогично {% macro foo() %} — т.е. определялся как функция, был список аргументов и т.д. При этом macro уже есть и получить список доступных макросов из шаблона можно используя доступный API.

[artifex]

Напишите развёрнутый пример, пожалуйста. Мне кажется я не до конца понял вашу мысль.

[Joes]

У Jinja2 есть конструкция macro, которая выглядит как функция. Определяется так:

{% macro hello_world(a, b, c=None, d='test') %}
Hello World {{ a }} {{ b }} {{ c }} {{ d }}
{% endmacro %}

Далее мы ее можем вызвать:

{{ hello_world(1, 2, 3, d='Hi')

У Jinja2 есть API для получения и вызова макросов как функций. Например так: github.com/mitsuhiko/jinja2/blob/master/jinja2/environment.py#L1036

Вроде еще какой-то способ был, но я так сразу не вспомню.

Почему это лучше:
1. Формальное определение функции с соответствующей проверкой аргументов, документацией и т.п.
2. На самом деле прослойка не нужна — Jinja2 уже умеет все из коробки. Разве что надо шаблон загрузить, получить модуль из него и вызывать макросы как обычные функции

[batment]

Выглядит очень интересно, но в коммерческом проекте, который нужно поддерживать другим людям, я бы такое не применял, разве что для каких-то специфических задач, где ORM действительно будет палкой в колесах.

[DmitryKoterov]

Посмотрите синтаксис и идеологию конструирования запросов из блоков (часть из которых опциональна) в старинной библиотеке DbSimpe.

Jinja тут вообще никаким боком, не того уровня абстракция совсем — вот и получатся sql injection и т.д. с ней. И параметры хорошо бы у каждого запроса явно описывать, а не втобы они брались по именам из середины запросов и непредсказуемо.

А вообще, описанный подход — в чистом виде хранимые процедуры в СУБД, только их нельзя джойнить и использовать внутри других процедур.

[mgremlin]

А мне лень. Я тупо sql пишу, да и все. И пока не видел ни одного решения, которое было бы удобнее — включая любые ормы.

[artifex]

Это решение и есть тупо SQL, просто вне Python-кода. Разделение сущностей и слабая связанность, все дела.

[mgremlin]

Если сравнить посимвольно — это решение никак не подходит под определение «тупо» 8-)

[ebt]

Алхимия чудесным алхимическим образом убивает всю асинхронность выполнения обработчика блокирующими вызовами в базу

Вы уверены, что правильно используете Tornado.gen.coroutine и алхимические scoped_session?

[bosha]

Я не автор поста, но всё же.
Насколько мне известно, одно использование gen.coroutine не делает магическим образом код асинхронным, выполнение так же происходит в одном треде. Он просто ожидает генератор на выходе. Чтобы код был асинхронным, необходимо всё таки написать его используя торнадовский eventloop (ioloop в терминологии tornado) и генераторы. Поправьте, если ошибаюсь.

Относительно scoped_session, ребята сделавшие SQLA пишут, что для каждого треда нужно нужна отдельная Session(), которую нельзя забывать возвращать в Pool используя Session.remove():

As discussed in the section When do I construct a Session, when do I commit it, and when do I close it?, a web application is architected around the concept of a web request, and integrating such an application with the Session usually implies that the Session will be associated with that request. As it turns out, most Python web frameworks, with notable exceptions such as the asynchronous frameworks Twisted and Tornado, use threads in a simple way, such that a particular web request is received, processed, and completed within the scope of a single worker thread. When the request ends, the worker thread is released to a pool of workers where it is available to handle another request.

Но к tornado это не относится никак, т.к. он работает в одном треде. В остальных же случаях SQLA «thread-local» и, соответственно, блокирует tornado до выполнения запроса.

Единственный способ подружить tornado с SQLAlchemy который я нашёл — это aiopg, но он поддерживает только postgres :(

[artifex]

Спасибо, что ответили за меня.

[bosha]

Честно говоря, как раз сегодня встала задача выполнить долгую, блокирующую операцию в tornado. Сделать её неблокирующей не выходило никак. Решением оказалось выделить выполнение этой операции в отдельный тред. При таком подоходе весь смысл использования tornado теряется. Проще сразу на cherrypy писать :)

[ebt]

Попробуйте вот так.

[bosha]

Я как раз выше об этом написал. Какой в этом случае смысл использовать торнадо?

[ebt]

Мощь Tornado в переиспользовании долгоживущих соединений. С другой стороны, базе данных этот факт совершенно безразличен, и встраивать её в парадигму Tornado является задачей для разработчика, которую он волен решать руководствуясь своей фантазией.

[ebt]

Вы здесь совершенно правы, упомянутые мной Tornado.gen.coroutine и scoped_session не решают задачу стыковки Tornado и SQLA в общем смысле. Корректное решение — Thread pool.

[artifex]

Не люблю Mako именно за это. Слишком много свободы в шаблонах, этим часто злоупотребляют. Вплоть до того, что пихают туда половину бизнес-логики, а то и всю. Логика представления должна оставаться таковой.

[CJay]

К сожалению, в БД как и в ORM глубоких познаний нет. Описанный подход мне понравился… Подумываю, не заменить ли таким подходом в своём проекте PeeWee. Но… в силу недостаточности знаний… имеется какое-то неосязаемое опасение… ряд вопросов…

• А безопасно-ли с точки зрения sql-инъекций? (хотя с чего я взял, что ORM безопасны, если и в них, по-сути, происходит конкатенация и форматирование строк)
• А есть ли выйгрыш в скорости при генерации sql-запроса шаблонизатором (Jinja2) и ORM (Sql-Alchemy)?
• А на сколько это удобно с точки зрения рефакторинга?! Если мы переименуем сущность (таблицу), это ж менять по всем шаблонам вручную?!

[ffsdmad]

а что если сделать вот такой декоратор (Flask)

def _sql(fn):
    def wrapped(*args):
        return db.session.execute( fn(*args) )
    return wrapped

а затем декорировать sql код моделей

@_sql
def page_count(self):
    return "select count(*) from pages"

а затем можно этот sql код можно легко вызывать прямо из шаблонов типа:

{{ page.page_count() }}

[nwalker]

Я, честно говоря, не понимаю восторга от yesql, особенно в среде Clojure. SQL же не композабелен вообще, они идеологически несовместимы.

[MyGodIsHe]

ORM хорош не запросами, а менеджментом БД — создание таблиц и связей, модификация, версионность, всякие эвенты, миграции и бог знает что ещё. Допилите всё это к своей либе, тогда посмотрим порог вхождения и сложность. ) А так, обычный import sql.

[devel787]

Хотел бы задать вопрос автору статьи по поводу:

>> Sphinxit
>
> I don't support this product any more.
> If you need some fixes or new features — fork and play with that, please.
> Snaql with native SphinxQL are recommended now.
( github.com/semirook/sphinxit/commit/13bc1228a0a30a01e04f0cf5e5037db4e4140017 )

У вас есть в планах написать раздел документации,
аналогичный sphinxit.readthedocs.org/en/latest/usage.html,
только с примерами на Snaql?