Не секрет, что SQLAlchemy — самая популярная ORM на Python. Она позволяет писать куда более продвинутые вещи, чем большинство Active Record собратьев. Но плата за это — более сложный код, и в простых задачах вроде CRUD это напрягает.

О том, как я сделал Алхимию удобной, воспользовавшись опытом лучших Active Record ORM, читайте под катом.

Введение

Я начал использовать SQLAlchemy, предварительно работав с Active Record в Ruby on Rails, c Eloquent ORM в PHP и c Django ORM и Peewee в Python. Все эти Active Record ORM имеют лаконичный код, и мне они очень нравятся.

Алхимия очень хороша: может строить совсем сложные запросы, да и паттерн Data Mapper рулит. Но, увы, после Active Record код Алхимии кажется слишком сложным, и это плата за гибкость.

В течение года я работал с Алхимией на сложном проекте, и понял, что для полного счастья хочется иметь:

Удобный CRUD: для простого create в Алхимии надо создать объект, да добавить его в сессию, да сделать flush
Динамическое построение фильтров/сортировки на основе магических строк как в Django: Post.objects.filter(user__name__startswith='John')
Вложенный eager load, когда нужно с комментарием сразу загрузить пост, а к посту его юзера (ладно, он есть, но не очень удобен)
Информативный __repr__: print(post) выдаёт что-то вроде <myapp.models.Post at 0x04287A50>, для отладки это не годится

Я с грустью вспоминал Active Record ORM, где всё это было (конечно, в каждой ORM были не все фичи, но большинство). Время шло, проект становился всё сложнее, и в конце концов не мог стерпеть и решил описанные задачи.

Решение оформлено в хорошо оттестированный и документированный пакет. Работает на боевом проекте и чувствует себя очень хорошо.

Есть готовые решения, скажете вы!

Да, есть, но они либо тяжело внедряются, либо заточены под конкретный фреймворк, либо плохо документированы.
Хотелось иметь универсальное, легко п��дключаемое решение, чтобы, к примеру, написать

from прекрасный_модуль import ActiveRecordMixin

class User(Base, ActiveRecordMixin):
     pass

и иметь готовый Active Record.
Варианты "инициализируйте Алхимию только через меня" и дополнения к flask-sqlalchemy не годятся.

Чем не устраивают конкретные пакеты, см. тут.

Подробнее о каждой задаче — ниже в статье.

О примерах в статье

Я буду приводить примеры для простенького блога с типовыми сущностями User, Post, Comment.

Схема БД и код ORM

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    name = Column(String)

    posts = relationship('Post')

class Post(Base):
    __tablename__ = 'post'
    id = Column(Integer, primary_key=True)
    body = Column(String)
    user_id = Column(Integer, ForeignKey('user.id'))

    user = relationship('User')
    comments = relationship('Comment')

class Comment(Base):
    __tablename__ = 'comment'
    id = Column(Integer, primary_key=True)
    body = Column(String)
    user_id = Column(Integer, ForeignKey('user.id'))
    post_id = Column(Integer, ForeignKey('post.id'))
    rating = Column(Integer)

    user = relationship('User')
    post = relationship('Post')

С кодом можно поиграться тут.
В нём ORM-классы, инициализация чистой Алхимии (без моего пакета) и наполнение начальными данными.

Как запустить

Установите Алхимию:

pip install sqlalchemy

Потом сохраните тестовое приложение в файл и запустите:

python файл.py

Active Record

После Active Record ORM, я не понимал, почему я должен для создания объекта писать 3 строчки

bob = User(name='Bobby', age=1)
session.add(bob)
session.flush()

вместо одной?

 bob = User.create(name='Bobby', age=1)

Я понимаю, что ручной flush сессии нужен, чтобы запросы в БД пошли одной пачкой, да и вообще паттерн unit of work даёт много преимуществ в плане производительности.

Но в реальных веб-приложениях большинство задач — тривиальный CRUD, и оттого, что в БД будет делаться не 3 запроса, а один, выигрыш невелик. Во всяком случае, он не стоит такого усложнения кода. Да и вообще, не зря же создатели Django, Ruby on Rails, Laravel, Yii выбрали Active Record ORM.

Что ж, ничто не мешает реализовать Active Record поверх Data Mapper! Для этого всего-то и надо, что при инициализации приложения сессию передать модели

BaseModel.set_session(session) # это базовый класс ОРМ
# теперь у нас есть доступ к BaseModel.session

Теперь ОРМ имеет доступ к сессии, и можно реализовывать методы save, create, update, delete и т.д.

bob = User.create(name='Bobby', age=1)
bob.update(name='Bob', age=21)
bob.delete()

Насчёт update

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

Ну и ещё хочется быстро создать запрос на модель

User.query # вместо session.query(User)

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

User.first() # вместо session.query(User).first()
User.all() # вместо session.query(User).all()

или найти запись по id, обвалившись ошибкой если надо

User.find(1) # вместо session.query(User).get(1)
User.find_or_fail(123987) # выбросит исключение, если не найдено

В итоге у нас получается полноценный Active Record как в любимых мною Django, Laravel и Ruby on Rails, но под капотом у нас мощный Data Mapper. Таким образом, мы имеем лучшее из двух миров.

Я не против стандартного подхода Алхимии, но для простых задач хочется иметь более простой код (см. спойлер в конце статьи)

Подробное описание и примеры см. тут.

Eager Load

Для решения проблемы N+1 запросов каждая ORM имеет свои решения.

Допустим, мы отображаем на странице 10 юзеров и все посты каждого юзера. Чтобы не вышло 11 запросов (1 на юзеров и 10 на посты), в SQLAlchemy можно эти посты приджойнить

session.query(User).options(joinedload('posts'))

или загрузить отдельным запросом

session.query(User).options(subqueryload('posts'))

Что ж, прекрасно! Только вот если надо с постами отображать комментарии, а с комментариями их авторов? Алхимия это позволяет, но на практике это оказалось неудобно.

Хочется декларативно задавать отношения, которые мы хотим подгрузить:

User.with_({
    'posts': {
        'comments': {
            'user': JOINED
        }
    }
}).all()

можно и без магических строк:

User.with_({
    User.posts: {
        Post.comments: {
            Comment.user: JOINED
        }
    }
}).all()

Кроме того, можно задавать разную стратегию загрузки: joinedload или subqueryload.
Следующий код приведёт к 2 запросам: на посты (с приджойненными авторами постов) и на комментарии (с приджойненными авторами комментариев):

Post.with_({
    'user': JOINED, # джойним юзера
    'comments': (SUBQUERY, {  # но комментарии грузим отдельным запросом 
        'user': JOINED  #  и в этом отдельном запросе к каждому комментарию джойним автора 
    })
}).all()

Ну и для простых случаев, когда нужен joinedload или subqueryload пары-тройки отношений, есть простой синтаксис (метод with_subquery вышел совсем как в Eloquent):

Comment.with_joined('user', 'post', 'post.comments').all()
# то же самое на чистой Алхимии:
# session.query(Comment).options(joinedload('user'), joinedload('post'), 
#                                joinedload('post.comments')).all()

User.with_subquery('posts', 'posts.comments').all()
# то же самое на чистой Алхимии:
# session.query(User).options(subqueryload('posts'), subqueryload('posts.comments')).all()

Обратите внимание, что можно грузить вложенные отношения вроде posts.comments. Это не моя фича, но Алхимии (к сожалению, доку по этой фиче найти так и не смог).

Подробное описание с примерами можно найти тут.

P.S. Отдельное спасибо моим коллегам за код.

Магические операторы и join отношений, как в Django

Первое, что мне бросилось в глаза при изучении Django — это магические операторы в фильтрах:

Entry.objects.filter(headline__startswith="What")

и совсем поразила фильтрация по связям:

Entry.objects.filter(blog__name='Beatles Blog')

это проще, чем более "правильное" решение в Алхимии:

session.query(Entry).join(Entry.blog).filter(Blog.name=='Beatles Blog')

Хотя...

конечно, это менее гибко, сложных запросов тут не выйдет
магические строки и могут потенциально дать баг в Runtime, если сделать опечатку, например вместо blog__name написать blogg__name. Такие строки, в отличие от свойств класса вроде Entry.blog, IDE не будет инспектировать.

Помимо эстетики, магические строки позволяют строить запросы динамически (например, передавая фильтры с UI):

filters =  {'entry__headline__contains': 'Lennon', 'entry__pub_date__year': 2008} # это мог передать фронтенд
Blog.objects.filter(**filters)

Это особенно полезно в приложениях, где пользователь может строить произвольные фильтры.

Увы, в Алхимии нет возможности строить запросы столь динамично. Максимум, что она позволяет — простенькую фильтрацию типа "колонка=значение":

session.query(MyClass).filter_by(name = 'some name')

Взяв за образец готовое решение (которого всё же было недостаточно), я сделал свой аналог Джанги и теперь можно фильтровать декларативно:

Post.where(rating__in=[2, 3, 4], user___name__like='%Bi%').all()

Как это сделано

Строка user___name__like парсится и мы понимаем, что надо приджойнить отношение Post.user и применить фильтр User.name.like('...').
То есть

Post.where(user___name__like='%Bi%').all()

превращается в

session.query(Post).join(Post.user).filter(User.name.like('%Bi%')).all()

А на самом деле всё сложнее

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

User.where(posts___comments___user___name='Vasya').all()

Получается, есть юзер, которого я запрашиваю, а есть автор комментария.
Проблему решают через alias'ы, т.е. в итоговом запросе будут присутствовать 2 таблицы: user и user_1.

Конечно, мы не можем заранее знать, будут ли повторяться таблицы, поэтому делаем каждому отношению, которое джойним, свой alias:

post_alias = User.posts.property.argument() # так можно вытащить целевой класс из relationship
session.query(User).outerjoin(post_alias) # и т.д.

Вот упрощенный аналог реального кода:

from sqlalchemy.orm import aliased
from sqlalchemy.sql import operators

# Имеем на входе {'posts___comments___user___name__like': 'Vasya'}. Достанем:
relations = ['posts', 'comments', 'user'] # 1. отношения, они были разделены ___
attr_name = 'name' # 2. аттрибут, он был после последнего ___
op_name = 'like' # 3. оператор, он был после __
# получаем оператор Алхимии на основе op_name.
# в реале имеется фиксированное соответствие OPERATORS = {'like': operators.like_op},
# и из него оператор достаётся как OPERATORS[op_name]
operator = operators.like_op

value = 'Vasya'

cls = User # в жизни это статический метод и текущий класс хранится в cls
query = session.query(cls) # делаем начальный запрос

# джойним все связи в цикле
last_alias = cls
for relation in relations:
    relation = getattr(last_alias, relation) # берём relation, например User.posts
    next_alias = aliased(relation.property.argument()) # достаём целевой класс (Post для User.posts) 
                                                       # и делаем на него alias
    query = query.outerjoin(next_alias) # джойним класс/алиас
    last_alias = next_alias

# теперь применим SQL-оператор к последнему классу/алиасу в цепочке (у нас это User)
attr = getattr(last_alias, attr_name) # получаем реальный аттрибут User.name
query = query.filter(operator(attr, value)) # применим SQL-оператор, передав ему аттрибут User.name и Васю
print(query.all())

Вот готовый к запуску код.

и сортировать:

Post.sort('-rating', 'user___name').all() # sort by rating DESC, user name ASC

Автоматический eager load

Более того, раз уж мы автоматически делаем join связей, логично указать SQLAlchemy, что указанные связи уже приджойнены, при помощи contains_eager. Теперь, если отношение Post.user использовалось в фильтре/сортировке, то мы сможем достать юзера без дополнительного запроса:

post = Post.sort('user___name').first()
print(post.user) # не потребуется дополнительного запроса в БД, т.к. юзер был приджойнен

Подробное описание с примерами см. тут.

Если Вы не любите магию

Вот пример магической фильтрации и сортировки в реальном проекте.
Вопрос ко всем, особенно к нелюбителям магических строк:

Сколько строк понадобится для подобных фильтров и сортировки на чистой Алхимии?
Насколько получившийся чисто-алхимический правильный код будет понятным и поддерживаемым?
Насколько легко будет его реюзать?
Итого, что лучше: немного оттестированной, документированной магии или кастомный велосипед на чистой Алхимии по 100 строк каждый раз, когда нужны сложные фильтры?

Всё в кучу: фильтры, сортировка, eager load

В реальном мире приходится одновременно фильтровать, сортировать, да ещё и eager load'ить связи.

Допустим, мы фильтруем и сортируем посты по одному и тому же отношению Post.user. Может статься, что фильтрация и сортировка 2 раза приджойнят одно и то же отношен��е, что скажется на быстродействии.

Разве Алхимия сама не разберётся?

Если просто писать

session.query(Post).join(Post.user).join(Post.user)

то, действительно, Алхимия сделает только один join.
Штука в том, что мы для каждого отношения делаем свой alias (см. спойлер "как это сделано" выше), и поэтому Алхимия не знает, что 2 alias-а на Post.user — это по сути одно и то же, и надо следить за этим самостоятельно.

Поэтому фильтрацию, сортировку и eager load (да, его тоже) пришлось сделать в одной функции, чтобы иметь информацию о всех требуемых джоинах (точнее, иметь единый список alias-ов, см. спойлер "как это сделано") и делать их только один раз:

 Comment.smart_query(
     filters={
         'post___public': True,
         'user__isnull': False
     },
     sort_attrs=['user___name', '-created_at'],
     schema={
         'post': {
             'user': JOINED
         }
     }).all()

Подробное описание с примерами можно найти тут.

Удобная отладка с удобным `repr`

Мне, как разработчику, очень важно играться с приложением в консоли. И было очень неприятно после красоты в Laravel видеть вот такое:

>>> session.query(Post).all()
[<myapp.models.Post object at 0x04287A50>, <myapp.models.Post object at 0x04287A90>]

Это жутко неинформативно ~~и очень бесит~~. Поэтому я решил выводить хотя бы ID

>>> session.query(Post).all()
[<Post #11>, <Post #12>]

Как универсально получить ID

from sqlalchemy import inspect
ids = inspect(post).identity # вернёт кортеж со значениями primary ключей

А вообще у каждой модели есть 1-2 аттрибута, которые отражают её суть. Например у юзера это имя, а у поста это юзер и тело поста. Поэтому я сделал возможность задать эти аттрибуты декларативно:

class User(BaseModel):
    __repr_attrs__ = ['name']
    # ...

class Post(BaseModel):
    __repr_attrs__ = ['user', 'body'] # body is just column, user is relationship
    # ...

Теперь имеем совсем удобный __repr__:

>>> session.query(Post).all()
[<Post #11 user:<User #1 'Bill'> body:'post 11'>,
 <Post #12 user:<User #2 'Bob'> body:'post 12'>]

Подробное описание с примерами тут.

Итоги

Реализованы фичи, без которых лично мне очень тяжко работалось с Алхимией.
Теперь жить стало легче, ведь:

C Active Record мы можем быстро делать CRUD, не заморачиваясь с flush-ем сессии.
С мощной фильтрацией/сортировкой в стиле Django мы можем делать очень сложные, динамические джойны в одну строку, которую можно хоть с UI передать.
Мы может задавать eager load декларативно.
Отладка стала удобнее с информативным __repr__.

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

Если вам кажется, что: (1) всё это зло; (2) я против чистой Алхимии; (3) я, не разобравшись, полез её улучшать

Я очень плотно работаю с Алхимией уже год на сложном боевом проекте. Все задачи, которые я привёл — не прихоть, а реальная необходимость.
Я люблю Алхимию и её стандартный подход. Мне нравится её гибкость, контроль над SQL, явность и т.д. Я считаю Алхимию лучшей ORM, что я встречал. Никакой Active Record с ней не сравнится.
Но плата за гибкость — более длинный код. И в простых задачах вроде CRUD код Алхимии сильно сложней аналогичного в Active Record ORM.
Поэтому я и написал свой пакет, который иногда можно использовать как дополнение к чистой Алхимии.
Мой пакет — лишь надстройка над Алхимией. Я до сих пор люблю Алхимию такой, какая она есть и не предлагаю отказаться от её изначального использования.
Об Active Record:
я понимаю всю мощь паттерна unit of work для оптимизации производительности приложения. Но в простых задачах, когда нужно в контроллере просто создать объект, писать для этого одни и те же 3 строки (создать объект, добавить в сессию, flush сессии) не вдохновляет. Поэтому я и сделал Active Record поверх, а не вместо Data Mapper для простых задач.
Об фильтрации/сортировке в стиле Django:
- я знаю, что магические строки вроде user___name__like — это неявно и чревато ошибками в runtime. Но иногда это может быть удобно.
- я тоже предпочитаю писать явно: session.query(Post).join(Post.user).filter(User.name.like(...)), но бывает, что заранее не знаешь, по какому полю фильтровать, или фильтры бывают такие сложные, что явно писать — будет много кода и фиг в нём потом разберёшься. Попробуйте-ка написать вот такой фильтр и сортировку на чистой Алхимии. Вы-то напишите, но кода будет много.
  И самое главное, писать кучу сложного кода придётся каждый раз. А так можно декларативно задать нужные фильтры и сортировку
- я понимаю, что нельзя пускать с фронтенда любые фильтры без валидации:
  - в боевом проекте мы задали список допустимых фильтров типа ['product___price', 'product___user___name'] и отсекаем всё остальное
  - если магический фильтр неправильный, пакет выдаст удобочитаемую ошибку
  - есть задумка сделать удобный валидатор магических строк

Спасибо за внимание!

Как я SQLAlchemy удобной сделал

Введение

Active Record

Eager Load

Магические операторы и join отношений, как в Django

Автоматический eager load

Если Вы не любите магию

Всё в кучу: фильтры, сортировка, eager load

Удобная отладка с удобным `repr`

Итоги

Articles

Как я SQLAlchemy удобной сделал

Введение

Active Record

Eager Load

Магические операторы и join отношений, как в Django

Автоматический eager load

Если Вы не любите магию

Всё в кучу: фильтры, сортировка, eager load

Удобная отладка с удобным __repr__

Итоги

Удобная отладка с удобным `repr`