- PVSM.RU - https://www.pvsm.ru -
Так вышло, что на заре моей карьеры в IT меня покусал Oracle -- тогда я ещё не знал ни одной ORM, но уже шпарил SQL и знал, насколько огромны возможности БД.
Знакомство с DjangoORM ввело меня в глубокую фрустрацию. Вместо возможностей -- хрена с два, а не составной первичный ключ или оконные функции. Специфические фичи БД проще забыть. Добивало то, что по цене нулевой гибкости мне продавали падение же производительности -- сборка ORM-запроса не бесплатная. Ну и вишенка на торте -- в дополнение к синтаксису SQL надо знать ещё и синтаксис ORM, который этот SQL сгенерирует. Недостатки, которые я купил за дополнительную когнитивную нагрузку -- вот уж где достижение индустрии. Поэтому я всерьёз считал, что без ORM проще, гибче и в разы производительнее -- ведь у вас в руках все возможности БД.
Так вот, эта история с SQLAlchemy -- счастливая история о том, как я заново открыл для себя ORM. В этой статье я расскажу, как я вообще докатился до такой жизни, о некоторых подводных камнях SQLAlchemy, и под конец перейду к тому, что вызвало у меня бурный восторг, которым попытаюсь с вами поделиться.
Я занимался оптимизацией SQL-запросов. Мне удавалось добиться стократного и более уменьшения cost запросов, в основном для Oracle и Firebird. Я проводил исследования, экспериментировал с индексами. Я видел в жизни много схем БД: среди них были как некоторое дерьмо, так и продуманные гибкие и расширяемые инженерные решения.
Этот опыт сформировал у меня систему взглядов касательно БД:
ORM не позволяет забыть о проектировании БД [1], если вы не хотите завтра похоронить проект
Переносимость -- миф, а не аргумент:
Если ваш проект работает с postgres через ORM, то вы на локальной машине разворачиваете в докере postgres, а не работаете с sqlite
Вы часто сталкивались с переходом на другую БД? Не пишите только "Однажды мы решили переехать..." -- это было однажды. Если же это происходит часто или заявленная фича, оправданная разными условиями эксплуатации у разных ваших клиентов -- милости прошу в обсуждения
У разных БД свои преимущества и болячки, всё это обусловлено разными структурами данных и разными инженерными решениями. И если при написании приложения мы используем верхний
Структура таблиц определяется вашими данными, а не ограничениями вашей ORM
Естественно, я ещё и код вне БД писал, и касательно этого кода у меня тоже сформировалась система взглядов:
Контроллер должен быть тонким, а лучший код -- это тот код, которого нет. Код ORM -- это часть контроллера. И если код контроллера спрятан в библиотеку, это не значит, что он стал тонким -- он всё равно исполняется
Контроллер, выполняющий за один сеанс много обращений к БД -- это очень тонкий лёд
Я избегаю повсеместного использования ActiveRecord -- это верный способ как работать с неконсистентными данными, так и незаметно для себя сгенерировать бесконтрольное множество обращений к БД
Оптимизация работы с БД сводится к тому, что мы не читаем лишние данные. Есть смысл запросить только интересующий нас список колонок
Часть данных фронт всё равно запрашивает при инициализации. Чаще всего это категории. В таких случаях нам достаточно отдать только id
Отладка всех новых запросов ORM обязательна. Всегда надо проверять, что там ORM высрала (тут [5] пара сочных примеров), дабы не было круглых глаз. Даже при написании этой статьи у меня был косяк как раз по этому пункту
Идея сокращения по возможности количества выполняемого кода в контроллере приводит меня к тому, что проще всего возиться не с сущностями, а сразу запросить из БД в нужном виде данные, а выхлоп можно сразу отдать сериализатору JSON.
Все вопросы данной статьи происходят из моего опыта и системы взглядов
Мы разные, и у нас всех разный фокус внимания. Я общался с разными разработчиками. Я видел разные позиции, от "да не всё ли равно, что там происходит? Работает же" до "я художник, у меня справка есть". При этом у некоторых из них были другие сильные стороны. Различие позиций -- это нормально. Невозможно фокусироваться на всех аспектах одновременно.
Мне, например, с большего без разницы, как по итогу фронт визуализирует данные, хотя я как бы фулстэк. Чем я отличаюсь от "да не всё ли равно, что там происходит"? Протокол? Да! Стратегия и оптимизация рендеринга? Да! Упороться в WebGL? Да! А что по итогу на экране -- пофиг.
Первое, что бросилось в глаза -- возможность писать DML-запросы в стиле SQL, но в синтаксисе python:
order_id = bindparam('order_id', required=True)
return
select(
func.count(Product.id).label("product_count"),
func.sum(Product.price).label("order_price"),
Customer.name,
)
.select_from(Order)
.join(
Product,
onclause=(Product.id == Order.product_id),
)
.join(
Customer,
onclause=(Customer.id == Order.customer_id),
)
.where(
Order.id == order_id,
)
.group_by(
Order.id,
)
.order_by(
Product.id.desc(),
)
Этим примером кода я хочу сказать, что ORM не пытается изобрести свои критерии, вместо этого она пытается дать нечто, максимально похожее на SQL. К сожалению, я заменил реальный фрагмент ORM-запроса текущего проекта, ибо NDA. Пример крайне примитивен -- он даже без подзапросов. Кажется, в моём текущем проекте таких запросов единицы.
Естественно, я сразу стал искать, как тут дела с составными первичными ключами -- и они есть! И оконные функции, и SQL хинты [6]! Дальнейшее погружение продолжает радовать: я не сталкивался ни с одним вопросом, который решить было невозможно из-за архитектурных ограничений. Правда, некоторые свои вопросы я решал через monkey-patching.
Насколько крутым и гибким бы ни было API, краеугольным камнем является вопрос производительности. Сегодня вам может и хватит 10 rps, а завтра вы пытаетесь масштабироваться, и если затык в БД -- поздравляю, вы мертвы.
Производительность query builder в SQLAlchemy оставляет желать лучшего. Благо, это уровень приложения, и тут масштабирование вас спасёт. Но можно ли это как-то обойти? Можно ли как-то нивелировать низкую производительность query builder? Нет, серьёзно, какой смысл тратить мощности ради увеличения энтропии Вселенной?
В принципе, нам на python не привыкать искать обходные пути: например, python непригоден для реализации числодробилок, поэтому вычисления принято выкидывать в сишные либы.
Для SQLAlchemy тоже есть обходные пути, и их сразу два, и оба сводятся к кэшированию по разным стратегиям. Первый -- применение bindparam
и lru_cache
. Второй предлагает документация [7] -- future_select
. Рассмотрим их преимущества и недостатки.
bindparam + lru_cache
Это самое простое и при этом самое производительное решение. Мы покупаем производительность по цене памяти -- просто кэшируем собранный объект запроса, который в себе кэширует отрендеренный запрос. Это выгодно до тех пор, пока нам не грозит комбинаторный взрыв, то есть пока число вариаций запроса находится в разумных пределах. В своём проекте в большинстве представлений я использую именно этот подход. Для удобства я применяю декоратор cached_classmethod
, реализующий композицию декораторов classmethod
и lru_cache [8]:
from functools import lru_cache
def cached_classmethod(target):
cache = lru_cache(maxsize=None)
cached = cache(target)
cached = classmethod(cached)
return cached
Для статических представлений тут всё понятно -- функция, создающая ORM-запрос не должна принимать параметров. Для динамических представлений можно добавить аргументы функции. Так как lru_cache
под капотом использует dict
, аргументы должны быть хешируемыми. Я остановился на варианте, когда функция-обработчик запроса генерирует "сводку" запроса и параметры, передаваемые в сгенерированный запрос во время непосредственно исполнения. "Сводка" запроса реализует что-то типа плана ORM-запроса, на основании которой генерируется сам объект запроса -- это хешируемый инстанс frozenset
, который в моём примере называется query_params
:
class BaseViewMixin:
def build_query_plan(self):
self.query_kwargs = {}
self.query_params = frozenset()
async def main(self):
self.build_query_plan()
query = self.query(self.query_params)
async with BaseModel.session() as session:
respone = await session.execute(
query,
self.query_kwargs,
)
mappings = respone.mappings()
return self.serialize(mappings)
В простейшем случае query_params
можно получить, просто преобразовав ключи query_kwargs
во frozenset
. Обращаю ваше внимание, что это не всегда справедливо: флаги в query_params
запросто могут поменять сам SQL-запрос при неизменных query_kwargs
.
На всякий случай предупреждаю: не стоит слепо копировать код. Разберитесь с ним, адаптируйте под свой проект. Даже у меня данный код на самом деле выглядит немного иначе, он намеренно упрощён, из него выкинуты некоторые несущественные детали.
Сколько же памяти я заплатил за это? А немного. На все вариации запросов я расходую не более мегабайта.
future_select
В отличие от дубового первого варианта, future_select
кэширует куски SQL-запросов, из которых итоговый запрос собирается очень быстро. Всем хорош вариант: и высокая производительность, и низкое потребление памяти. Читать такой код сложно, сопровождать дико:
stmt = lambdas.lambda_stmt(lambda: future_select(Customer))
stmt += lambda s: s.where(Customer.id == id_)
Этот вариант я обязательно задействую, когда дело будет пахнуть комбинаторным взрывом [9].
По идее, future_select
через FutureSelectWrapper
можно пользоваться почти как старым select
, что нивелирует дикий синтаксис:
class FutureSelectWrapper:
def __init__(self, clause):
self.stmt = lambdas.lambda_stmt(
lambda: future_select(clause)
)
def __getattribute__(self, name):
def outer(clause):
def inner(s):
callback = getattr(s, name)
return callback(clause)
self.stmt += inner
return self
return outer
Я обращаю ваше внимание, что это лишь наброски. Я их ни разу не запускал. Необходимы дополнительные исследования.
Промежуточный вывод: низкую производительность query builder в SQLAlchemy можно нивелировать кэшем запросов. Дикий синтаксис future_select
можно спрятать за фасадом.
А ещё я не уделил должного внимания prepared statements. Эти исследования я проведу чуть позже.
Мы добрались главного -- ради этого раздела я писал статью. В этом разделе я поделюсь своими откровениями, посетившими меня в процессе работы.
Модульность
Когда я реализовывал на SQL дикую аналитику, старой болью отозвалось отсутствие модульности и интроспекции. При последующем переносе на ORM у меня уже была возможность выкинуть весь подзапрос поля FROM
в отдельную функцию (по факту метод класса), а в последующем эти функции было легко комбинировать и на основании флагов реализовывать паттерн Стратегия [10], а также исключать дублирование одинакового функционала через наследование.
Собственные типы
Если данные обладают хитрым поведением, или же хитро преобразуются, совершенно очевидно, что их надо выкинуть на уровень модели. Я столкнулся с двумя вопросами: хранение цвета и работа с ENUM
. Погнали по порядку.
Создание собственных простых типов рассмотрено в документации [11]:
class ColorType(TypeDecorator):
impl = Integer
cache_ok = True
def process_result_value(self, value, dialect):
if value is None:
return
return color(value)
def process_bind_param(self, value, dialect):
if value is None:
return
value = color(value)
return value.value
Сыр-бор тут только в том, что мне стрельнуло хранить цвета не строками, а интами. Это исключает некорректность данных, но усложняет их сериализацию и десериализацию.
Теперь про ENUM
. Меня категорически не устроило, что документация [12] предлагает хранить ENUM
в базе в виде VARCHAR
. Особенно уникальные целочисленные Enum хотелось хранить интами. Очевидно, объявлять этот тип мы должны, передавая аргументом целевой Enum. Ну раз String при объявлении требует указать длину -- задача, очевидно, уже решена. Штудирование исходников вывело меня на TypeEngine [13] -- и тут вместо примеров использования вас встречает "our source code is open 24/7". Но тут всё просто:
class IntEnumField(TypeEngine):
def __init__(self, target_enum):
self.target_enum = target_enum
self.value2member_map = target_enum._value2member_map_
self.member_map = target_enum._member_map_
def get_dbapi_type(self, dbapi):
return dbapi.NUMBER
def result_processor(self, dialect, coltype):
def process(value):
if value is None:
return
member = self.value2member_map[value]
return member.name
return process
def bind_processor(self, dialect):
def process(value):
if value is None:
return
member = self.member_map[value]
return member.value
return process
Обратите внимание: обе функции -- result_processor
и bind_processor
-- должны вернуть функцию.
Собственные функции, тайп-хинты и вывод типов
Дальше больше. Я столкнулся со странностями реализации json_arrayagg [14] в mariadb: в случае пустого множества вместо NULL
возвращается строка "[NULL]"
-- что ни под каким соусом не айс. Как временное решение я накостылил связку из group_concat, coalesce и concat. В принципе неплохо, но:
При вычитывании результата хочется нативного преобразования строки в JSON
.
Если делать что-то универсальное, то оказывается, что строки надо экранировать. Благо, есть встроенная функция json_quote
. Про которую SQLAlchemy не знает.
А ещё хочется найти workaround-функции в объекте sqlalchemy.func
Оказывается, в SQLAlchemy эти проблемы решаются совсем влёгкую. И если тайп-хинты мне показались просто удобными, то вывод типов поверг меня в восторг: типозависимое поведение можно инкапсулировать в саму функцию, что сгенерирует правильный код на SQL.
from sqlalchemy.sql.functions import GenericFunction, register_function
from sqlalchemy.sql import sqltypes
from sqlalchemy import func, literal_column
def register(target):
name = target.__name__
register_function(name, target)
return target
# === Database functions ===
class json_quote(GenericFunction):
type = sqltypes.String
inherit_cache = True
class json_object(GenericFunction):
type = sqltypes.JSON
inherit_cache = True
# === Macro ===
empty_string = literal_column("''", type_=sqltypes.String)
json_array_open = literal_column("'['", type_=sqltypes.String)
json_array_close = literal_column("']'", type_=sqltypes.String)
@register
def json_arrayagg_workaround(clause):
clause_type = clause.type
if isinstance(clause_type, sqltypes.String):
clause = func.json_quote(clause)
clause = func.group_concat(clause)
clause = func.coalesce(clause, empty_string)
return func.concat(
json_array_open,
clause,
json_array_close,
type_=sqltypes.JSON,
)
def __json_pairs_iter(clauses):
for clause in clauses:
clause_name = clause.name
clause_name = "'%s'" % clause_name
yield literal_column(clause_name, type_=sqltypes.String)
yield clause
@register
def json_object_wrapper(*clauses):
json_pairs = __json_pairs_iter(clauses)
return func.json_object(*json_pairs)
В рамках эксперимента я также написал функцию json_object_wrapper
, которая из переданных полей собирает json, где ключи -- это имена полей. Буду использовать или нет -- ХЗ. Причём тот факт, что эти макроподстановки не просто работают, а даже правильно, меня немного пугает.
SELECT concat(
'[',
coalesce(group_concat(product.tag_id), ''),
']'
) AS product_tags
SELECT json_object(
'name', product.name,
'price', product.price
) AS product,
PS: Да, в случае json_object_wrapper
я изначально допустил ошибку. Я человек простой: вижу константу -- вношу её в код. Что привело к ненужным bindparam
на месте ключей этого json_object
. Мораль -- держите ORM в ежовых рукавицах. Упустите что-то -- и она вам такого нагенерит! Только literal_column
позволяет надёжно захардкодить константу в тело SQL-запроса.
Такие макроподстановки позволяют сгенерировать огромную кучу SQL кода, который будет выполнять логику формирования представлений. И что меня восхищает -- эта куча кода работает эффективно. Ещё интересный момент -- эти макроподстановки позволят прозрачно реализовать паттерн Стратегия -- я надеюсь, поведение json_arrayagg
пофиксят в следующих релизах MariaDB, и тогда я смогу своё костылище заменить на связку json_arrayagg
+coalesce
незаметно для клиентского кода.
SQLAlchemy позволяет использовать преимущества наследования и полиморфизма (и даже немного иннкапсуляции. Флеш-рояль, однако) в SQL. При этом она не загоняет вас в рамки задач уровня Hello, World!
архитектурными ограничениями, а наоборот даёт вам максимум возможностей.
Субъективно это прорыв. Я обожаю реляционные базочки, и наконец-то я получаю удовольствие от реализации хитрозакрученной аналитики. У меня в руках все преимущества ООП и все возможности SQL.
Автор: kai3341
Источник [15]
Сайт-источник PVSM.RU: https://www.pvsm.ru
Путь до страницы источника: https://www.pvsm.ru/python/364951
Ссылки в тексте:
[1] забыть о проектировании БД: https://habr.com/ru/post/237889/
[2] мозг: http://www.braintools.ru
[3] Базы данных для программиста: https://www.youtube.com/watch?v=MgQO5cRUNM0&list=PLmqFxxywkatS8Hfj6-aYgXfrpvV6OoKSc
[4] Транзакции: https://www.youtube.com/watch?v=4aa1lRShrrg&list=PLmqFxxywkatR3Psg4pz0Br0uDHzjR9Sne
[5] тут: https://habr.com/ru/company/domclick/blog/552930/
[6] SQL хинты: https://docs.sqlalchemy.org/en/13/core/selectable.html#sqlalchemy.sql.expression.Select.prefix_with
[7] документация: https://docs.sqlalchemy.org/en/14/changelog/migration_14.html#transparent-sql-compilation-caching-added-to-all-dql-dml-statements-in-core-orm
[8] lru_cache: https://docs.python.org/3/library/functools.html#functools.lru_cache
[9] комбинаторным взрывом: https://habr.com/ru/post/559738/#comment_23118792
[10] паттерн Стратегия: https://ru.wikipedia.org/wiki/%D0%A1%D1%82%D1%80%D0%B0%D1%82%D0%B5%D0%B3%D0%B8%D1%8F_(%D1%88%D0%B0%D0%B1%D0%BB%D0%BE%D0%BD_%D0%BF%D1%80%D0%BE%D0%B5%D0%BA%D1%82%D0%B8%D1%80%D0%BE%D0%B2%D0%B0%D0%BD%D0%B8%D1%8F)
[11] документации: https://docs.sqlalchemy.org/en/14/core/custom_types.html
[12] документация: https://docs.sqlalchemy.org/en/14/core/type_basics.html?highlight=enum#sqlalchemy.types.Enum
[13] TypeEngine: https://docs.sqlalchemy.org/en/14/core/type_api.html#sqlalchemy.types.TypeEngine
[14] json_arrayagg: https://mariadb.com/kb/en/json_arrayagg/
[15] Источник: https://habr.com/ru/post/559738/?utm_source=habrahabr&utm_medium=rss&utm_campaign=559738
Нажмите здесь для печати.