12.09.2015 13:53
artifex 33 10642 Источник
В последний год у меня появилось новое правило — каждые 3 месяца изучать новый язык программирования и его экосистему. На это есть несколько причин: новые парадигмы, концепции, инструменты, да и просто интересно что там, по ту сторону набившего с годами оскомину Python. Это простое правило позволило изучить за текущий год современные хипстерские Go, Clojure и Rust, проникнуться их идеями и best practices, что, кстати, очень положительно влияет на стиль и качество кода, когда я пишу на своём основном языке.

Рассматривая стек Luminus, я наткнулся на простую и в то же время шикарную, на мой вкус, библиотеку Yesql для организации SQL-запросов в проекте на Clojure и я не увидел чего-то похожего для Python (может плохо искал). Идея этой библиотеки простая — не морочьте себе голову, используйте обычные SQL-запросы, у вас есть возможность именования этих запросов и мапинга на соответствующие динамические функции. Всё это выглядит как набор микро-шаблонов с SQL и их рендер по какому-то контексту. Просто, эффективно, хочу такое у себя в проекте на Python.



Вообще в последнее время мне импонирует мысль, что ORM не нужны. Они переусложняют, на самом деле, работу с реляционными БД, скрывают «адский» SQL за ширмой сложных конструкций собственных объектов, а зачастую выдают и крайне неэффективный результат. Наверняка кто-то поспорит с этим выводом, но моя практика показала, что Django ORM ужасающе простой чуть более чем всегда (и доступен только если вы используете Django, конечно), SQLAlchemy ужасающе сложный, Peewee — ни разу не встречал в дикой природе, к тому же ещё немного и он станет как Alchemy по своему порогу вхождения. SQL — сам по себе мощный и выразительный DSL, вам не нужен ещё один уровень абстракции над ним, серьёзно. Под другим углом я задумался о целесообразности ORM во время очередного проекта на Tornado. Алхимия чудесным алхимическим образом убивает всю асинхронность выполнения обработчика блокирующими вызовами в базу. И вариантов кроме как использовать тот же Momoko с сырыми запросами я не увидел.

Всё, что нам нужно для полного счастья — это разведение SQL-строк и Python-кода по разным углам и некоторая гибкость в построении конструкций по условиям или контексту. Ну и перестать бояться писать SQL, конечно. Изучить SQL до необходимого уровня реально проще чем все нюансы Алхимии для того же результата.

Попробовав и немного переосмыслив Yesql у меня родилась крохотная библиотека Snaql, которая решает описанную выше проблему, хоть и немного по-своему. Я решил вообще не завязываться на клиенты к базам и использовать Jinja2 в качестве движка для парсинга и рендеринга шаблонов с SQL-блоками (со всеми вытекающими возможностями использовать её шаблонную логику). Вот как это выглядит.

1. Ставим Snaql.

$ pip install snaql


2. Создаём в своём проекте папку, куда будем складывать файлы с SQL-блоками. Или несколько таких папок.

/queries
    users.sql


3. В users.sql у нас, например, все запросы, связанные с сущностью пользователя.

{% sql 'users_by_country', note='counts users' %}
    SELECT count(*) AS count
    FROM user
    WHERE country_code = ?
{% endsql %}


Как можно догадаться, SQL помещается внутри блока {%sql%}{%endsql%}, «users_by_country» это название функции, на которую навешивается данный SQL (создаётся динамически), а «note» — это docstring к этой функции, он опционален.

Таких блоков в одном файле может быть сколь угодно много. Главное, чтобы их имена были уникальны.

4. Теперь нам нужна фабрика, которая распарсит такие файлы и создаст набор одноимённых функций.

from snaql.factory import Snaql

# корень проекта
root_location = os.path.abspath(os.path.dirname(__file__))

# регистрация директории с шаблонами
snaql_factory = Snaql(root_location, 'queries')

# регистрация шаблона с SQL-блоками
# users_queries = snaql_factory.load_queries('users.sql')


Извлечь в коде необходимый SQL теперь можно просто вызвав

your_sql = users_queries.users_by_country()

# SELECT count(*) AS count
# FROM user
# WHERE country_code = ?


На самом деле уже этого может быть достаточно. Но не в случае с генерируемыми условиями запроса. В этом случае можно добавить в шаблон всю то логику, которую предоставляет Jinja. Например:

{% sql 'users_select_cond', note='select users with condition' %}
    SELECT *
    FROM user
    {% if users_ids %}
        WHERE user_id IN ({{ users_ids|join(', ') }})
    {% endif %}
{% endsql %}


Если вызвать функцию без контекста:

your_sql = users_queries.users_select_cond()

# SELECT *
# FROM user


И если с контекстом:

your_sql = users_queries.users_select_cond(users_ids=[1, 2, 3])

# SELECT *
# FROM user 
# WHERE user_id IN (1, 2, 3)


Получив сформированный SQL, остальное — дело техники. Вроде неплохо, да? В любом случае пишите свои «за» и «против» в комментариях, мне интересно мнение сообщества, насколько это может быть удобным кому-то кроме меня.

GitHub, PyPi

UPD: Спасибо за конструктивные комментарии. Теперь у меня есть с чего формировать roadmap на 0.2. Не стесняйтесь присылать issues и requests на GitHub.

UPD2: Благодаря вашим конструктивным замечаниям, я обновил Snaql до версии 0.2, там теперь есть guards и conditions blocks, расширена поддержка версий интерпретатора до 2.6, 2.7, 3.3, 3.4, 3.5.

Комментарии (33)


  1. KoVexPulThul
    12.09.2015 22:01
    #8570515

    Мне кажется такой подход будет не очень удобен для описания условий посложнее. Например, когда передается несколько независимых условий. Приведу в качестве примера генерацию такого запроса с использованием 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()


    1. to_climb
      13.09.2015 00:28
      #8570567

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

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


    1. artifex
      13.09.2015 00:31
      #8570569

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

      {% 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)


      1. KoVexPulThul
        13.09.2015 00:49
        #8570579
        +4

        Мне кажется, здесь произойдет ошибка т.к. будет составлен некорректный запрос, когда будут переданы параметры 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


        1. artifex
          13.09.2015 01:17
          #8570589

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

          {% 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)


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


          1. BlessMaster
            13.09.2015 06:13
            #8570637
            +5

            Имхо, 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, но первый пункт — никуда не денешь. Ну и всю мощь «питона с батарейками» в противовес «песочнице» я уже упоминал (можно, конечно, перенастроить песочницу в швейцарский нож, благо она это позволяет, но создавалась она явно с противоположной целью и умолчания — против нас).

            Как-то так.


            1. BlessMaster
              13.09.2015 06:44
              #8570641
              +1

              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.


              1. ef_end_y
                13.09.2015 10:02
                #8570673
                +2

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

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


        1. cinic
          13.09.2015 10:33
          #8570685

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


  1. Joes
    13.09.2015 09:06
    #8570659
    +2

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

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


    1. artifex
      13.09.2015 22:40
      #8571017

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


      1. Joes
        14.09.2015 05:36
        #8571093

        У 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 уже умеет все из коробки. Разве что надо шаблон загрузить, получить модуль из него и вызывать макросы как обычные функции


  1. batment
    13.09.2015 11:56
    #8570717

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


  1. DmitryKoterov
    13.09.2015 12:29
    #8570735
    +1

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

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

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


  1. mgremlin
    13.09.2015 14:42
    #8570803

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


    1. artifex
      13.09.2015 22:39
      #8571013

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


      1. mgremlin
        14.09.2015 08:51
        #8571111

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


  1. jam31
    13.09.2015 19:54
    #8570933

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


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


    1. bosha
      16.09.2015 08:38
      #8574823
      +1

      Я не автор поста, но всё же.
      Насколько мне известно, одно использование 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 :(


      1. artifex
        16.09.2015 16:11
        #8575571

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


        1. bosha
          16.09.2015 20:34
          #8575951

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


          1. jam31
            16.09.2015 22:02
            #8576065

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


            1. bosha
              16.09.2015 22:10
              #8576073

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


              1. jam31
                16.09.2015 22:32
                #8576095

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


      1. jam31
        16.09.2015 22:42
        #8576115

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


  1. Crandel
    13.09.2015 21:06
    #8570965

    Вместо Jinja можно использовать Mako, очень легкий шаблонизатор, в котором можно задействовать всю мощь питона для обработки данных( генераторы, list comprehension и тд.)


    1. artifex
      13.09.2015 22:38
      #8571011

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


      1. Crandel
        14.09.2015 09:29
        #8571121
        +1

        так тут надо гибкость для sql запросов, или я неправильно понял вашу мысль?


  1. CJay
    14.09.2015 11:21
    #8571197

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

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



  1. ffsdmad
    14.09.2015 14:47
    #8571585

    а что если сделать вот такой декоратор (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() }}


  1. nwalker
    15.09.2015 00:54
    #8572487

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


  1. NElias
    15.09.2015 12:36
    #8573035

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


  1. devel787
    28.09.2015 12:36
    #8593517

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

    >> 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?