Pull to refresh

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

Reading time 10 min
Views 53K

Не секрет, что 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 код Алхимии кажется слишком сложным, и это плата за гибкость.


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


  1. Удобный CRUD: для простого create в Алхимии надо создать объект, да добавить его в сессию, да сделать flush
  2. Динамическое построение фильтров/сортировки на основе магических строк как в Django: Post.objects.filter(user__name__startswith='John')
  3. Вложенный eager load, когда нужно с комментарием сразу загрузить пост, а к посту его юзера (ладно, он есть, но не очень удобен)
  4. Информативный __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

DB


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

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


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


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


  1. Сколько строк понадобится для подобных фильтров и сортировки на чистой Алхимии?
  2. Насколько получившийся чисто-алхимический правильный код будет понятным и поддерживаемым?
  3. Насколько легко будет его реюзать?
  4. Итого, что лучше: немного оттестированной, документированной магии или кастомный велосипед на чистой Алхимии по 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'>]

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


Итоги


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


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

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


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


  2. Я люблю Алхимию и её стандартный подход. Мне нравится её гибкость, контроль над SQL, явность и т.д. Я считаю Алхимию лучшей ORM, что я встречал. Никакой Active Record с ней не сравнится.


  3. Но плата за гибкость — более длинный код. И в простых задачах вроде CRUD код Алхимии сильно сложней аналогичного в Active Record ORM.
    Поэтому я и написал свой пакет, который иногда можно использовать как дополнение к чистой Алхимии.
    Мой пакет — лишь надстройка над Алхимией. Я до сих пор люблю Алхимию такой, какая она есть и не предлагаю отказаться от её изначального использования.


  4. Об Active Record:
    я понимаю всю мощь паттерна unit of work для оптимизации производительности приложения. Но в простых задачах, когда нужно в контроллере просто создать объект, писать для этого одни и те же 3 строки (создать объект, добавить в сессию, flush сессии) не вдохновляет. Поэтому я и сделал Active Record поверх, а не вместо Data Mapper для простых задач.


  5. Об фильтрации/сортировке в стиле Django:


    • я знаю, что магические строки вроде user___name__like — это неявно и чревато ошибками в runtime. Но иногда это может быть удобно.


    • я тоже предпочитаю писать явно: session.query(Post).join(Post.user).filter(User.name.like(...)), но бывает, что заранее не знаешь, по какому полю фильтровать, или фильтры бывают такие сложные, что явно писать — будет много кода и фиг в нём потом разберёшься. Попробуйте-ка написать вот такой фильтр и сортировку на чистой Алхимии. Вы-то напишите, но кода будет много.
      И самое главное, писать кучу сложного кода придётся каждый раз. А так можно декларативно задать нужные фильтры и сортировку


    • я понимаю, что нельзя пускать с фронтенда любые фильтры без валидации:
      • в боевом проекте мы задали список допустимых фильтров типа ['product___price', 'product___user___name'] и отсекаем всё остальное
      • если магический фильтр неправильный, пакет выдаст удобочитаемую ошибку
      • есть задумка сделать удобный валидатор магических строк


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

Tags:
Hubs:
+33
Comments 22
Comments Comments 22

Articles