[Из песочницы] Как я SQLAlchemy удобной сделал26.03.2017 22:47

Не секрет, что 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 ORM многого не хватает.

Многие, элементарные на мой взгляд, задачи в Алхимии не решены:

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

После наслаждения от использования Active Record ORM, я не мог стерпеть и решил описанные задачи.

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

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

class User(Base, ActiveRecordMixin):
     pass

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

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

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

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')

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()

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

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': None
        }
    }
}.all()

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

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

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

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

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

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')

Помимо эстетики, магические строки позволяют строить запросы динамически (например, передавая фильтры с 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()

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

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

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

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

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

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

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

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

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

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

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

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

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

>>> session.query(Post).all()
[, ]

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

>>> session.query(Post).all()
[, ]

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()
[ body:'post 11'>,
  body:'post 12'>]

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

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

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

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

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

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

• Zagrebelion

  26 марта 2017 в 18:14

  0

  ↑

  ↓

  По поводу джанговских filter(blog__name='John') смотрите, как pycharm умеет (у модели поле FK gmfoto к другой модели, у которой есть поле-строка cover_hash:
  https://habrastorage.org/files/869/bb9/1c9/869bb91c98d948e9942c9294f1352960.png

  • litvinenko1706

    26 марта 2017 в 18:40

    0

    ↑

    ↓

    Спасибо, буду знать!

  • evocatus

    26 марта 2017 в 20:50

    0

    ↑

    ↓

    Это только в Pro версии?

• VovanZ

  26 марта 2017 в 18:55

  +3

  ↑

  ↓

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

  Странно, что вы не посмотрели на соседний метод (filter)[http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.filter], который позволяет фильтровать куда более «динамично», и главное — читабельно:

  
  

  session.query(MyClass).\
      filter(MyClass.name == 'some name', MyClass.id > 5)

  
  

  Помимо этого, с помощью других методов атибутов можно записать и in (MyClass.some_value.in([1, 2, 3])) и вообще что угодно.

  • litvinenko1706

    26 марта 2017 в 20:02 (комментарий был изменён)

    0

    ↑

    ↓

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

    
    

    filters = {'rating__in': [2, 3, 4], 'body__like': '%search%'} # пришло с фронтенда
    Post.where(*filters).all()

    
    

    Придется писать ручками:

    
    

    session.query(Post).filter(Post.rating.in_([2,3,4]), Post.body.like('%search%')).all()

    
    

    тут дело именно в том, что нельзя задать фильтр/сортировку, передав строку

    
    

    filters = {'rating__in': [2, 3, 4], 'body__like': '%post%'}

    • VovanZ

      26 марта 2017 в 20:12

      +1

      ↑

      ↓

      Хмм, ок, теперь я понял вашу идею.

      
      

      Всё же, эта идея кажется мне достаточно стрёмной: по сути, мы позволяем фронтенду делать любые запросы, без явной реализации этих запросов в контроллере.
      Я вижу в этом некоторую опасность: например, какой-нибудь злодей может подобрать максимально «тяжёлую» комбинацию параметров (фильрация по параметрам, у которых нет индекса, использование таких параметров для «like», которые не используют индекс) и создать аномальную нагрузку на БД.

      
      

      Я предпочитаю такие вещи контролировать в явном виде.

      • litvinenko1706

        26 марта 2017 в 20:57

        0

        ↑

        ↓

        Да, в таком подходе есть минусы. Ещё минус, что в таких «магических строках» легко ошибиться и, допустим, вместо user___name, написать yser___name, IDE такое не провалидирует, и получим ошибку в runtime.

        
        

        Я тоже предпочитаю писать в явном виде, но бывают ситуации, когда надо строить запрос «на лету», когда фильтров не знаешь заранее. И вот такого как раз Алхимия не умеет.

        
        

        А по поводу злодейской «тяжёлой комбинации» и вообще валидации: на боевом проекте мы валидируем такие магические строки, приходящие с UI (с помощью marshmallow)

        
        

        Имею в планах сделать возможность валидации таких строк.

        • novoxudonoser

          26 марта 2017 в 21:35

          +1

          ↑

          ↓

          Вы можете хоть по условию, хоть в цикле, в зависимости от входных данных формировать динамическую фильтрацию через query.filter (…). Если вы можете написать алгоритм который по входным данным формирует магическую строку, то вы можете написать и алгоритм который применяет цепочку filter.

          • litvinenko1706

            26 марта 2017 в 21:54

            0

            ↑

            ↓

            можно., но каждый раз придётся писать новый алгоритм.
            я же, как и создатели Django ORM, решил иметь возможность описать нужные джоины декларативно и дать протестированному коду это джоины сделать.

            
            

            Это — не серебряная пуля, здесь есть свои минусы. Однако это удобная дополнительная фишка, которая может сделать код короче.

        • ikovrigin

          26 марта 2017 в 21:57

          0

          ↑

          ↓

          Вопрос даже не в быстродействии, что мешает такими фильтрами подбирать пароли userpasswordstartswith='a'.

          • litvinenko1706

            26 марта 2017 в 22:00

            0

            ↑

            ↓

            это скорее вопрос к проектировке безопасности приложения.

            
            

            конечно, не следует разрешать фронтенду передавать что угодно в качестве фильтра.

• evocatus

  26 марта 2017 в 20:51

  0

  ↑

  ↓

  Кстати, гляньте на Pony ORM: https://ponyorm.com/

• novoxudonoser

  26 марта 2017 в 21:28

  +1

  ↑

  ↓

  Спасибо за статью, но блин простите, вы выбрали плохой путь, может вам так и удобней, но…
  

  Удобный CRUD: для простого create в Алхимии надо создать объект, да добавить его в сессию, да сделать flush

  
  Это вопрос скорее не удобства, а стиля (и иногда производительности), лично для меня написать больше строк, но сделать всё наглядным удобней, может конечно наверно и потому что в моих задачах обычно используется не простой CRUD.
  

  Да и вообще, не зря же создатели Django, Ruby on Rails, Laravel, Yii выбрали Active Record ORM.

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

  Динамическое построение фильтров/сортировки на основе магических строк как в Django: Post.objects.filter (user__name__startswith='John')

  
  Одно из списка, из за чего лично у меня возникает б**дская ненависть к Django, для меня это жутко не удобно и антипатерн. Ды и к тому же доверять клиенту нельзя, формировать магические строки на клиенте — плохая идея.
  

  Вложенный eager load, когда нужно с комментарием сразу загрузить пост, а к посту его юзера (ладно, он есть, но не очень удобен)

  
  Я не очень понимаю чем вам такой синтаксис не удобен?:
  

  .options(
      joinedload_all(Watchdog.recipe, Recipe.recipeset, RecipeSet.job),
      joinedload_all(Watchdog.recipe, Recipe.recipeset, RecipeSet.lab_controller),
      joinedload_all(Watchdog.recipetask, RecipeTask.task)
  )
  

  
  Ваше предложение:
  

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

  
  Плохой синтаксис, пихать всё в словари, очень напоминает реализацию sequelize.js, только там так сделано от того что в js нет способа сделать это красиво как в python.

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

  • litvinenko1706

    26 марта 2017 в 21:50 (комментарий был изменён)

    0

    ↑

    ↓

    1. Тут вопрос стиля и личных предпочтений. Вы привыкли к одному, я — к другому.
    Я не против стандартного синтаксиса Алхимии, просто в некоторых местах мне он неудобен. А в других местах, напротив, Алхимия очень рулит.

    
    

    2.

    
    

    Я не очень понимаю чем вам такой синтаксис не удобен?:
    .options (
    joinedload_all (Watchdog.recipe, Recipe.recipeset, RecipeSet.job),
    joinedload_all (Watchdog.recipe, Recipe.recipeset, RecipeSet.lab_controller),
    joinedload_all (Watchdog.recipetask, RecipeTask.task)
    )

    лично мне не видно иерархии.

    
    

    3. С Алхимией работаю год. И за этот год после Active Record наболело очень многое. Что и вылилось с описанный пакет.