Розробка на Django зазвичай проста та зрозуміла: чудова документація, багато інструментів доступні «з коробки», є чимало відкритих бібліотек та активна підтримка спільноти. Django ORM повністю контролює SQL-рівень, захищаючи від помилок та приховуючи внутрішні деталі запитів, аби ви могли приділити більше часу проектуванню та організації структури вашого застосунку на Python.
Однак іноді ці переваги перетворюються на недоліки. Наприклад, якщо ваш проект пов'язаний з аналізом даних. Створення нетипових запитів з Django — нелегка справа: їх складно читати (в Python коді) і складно зрозуміти, що відбувається на SQL-рівні без виводу згенерованих SQL-запитів. До того ж такі запити можуть бути недостатньо ефективними, тому ви матимете проблеми при подальшому наповненні вашої БД даними. Якщо ви часто маєте справу з чистим SQL в Django, варто звернути увагу на цікавий інструмент між шаром ORM та чистими SQL-запитами.
Як видно з назви статті, ми успішно замiксували Django ORM з SQLAlchemy Core, і дуже задоволені результатами. Ми побудували продукт, який допомагає аналізувати дані (створені системами EMR), за допомогою агрегацї даних у діаграми та таблиці, оцінюючи пропускну здатність, ефективність та вартість персоналу, показуючи відхилення, що дозволяє оптимізувати бізнес-процеси для клінік та заощадити гроші.
Навіщо об'єднувати Django ORM та SQLAlchemy
Існує декілька причин:
- Концепція ORM розглядає об'єкт як один запис у БД, але у цьому випадку ми маємо справу з агрегованими даними.
- Іноді агрегація даних може бути нетривіальна, така, яку неможливо розв'язати за допомогою Django ORM. Зазвичай складно (або навіть неможливо) змусити ORM генерувати SQL-запит так, як це необхідно. А якщо ви маєте справу з big data, страждатиме швидкодія.
- Просунуті запити з Django ORM можуть бути складними для розуміння у Python, та й важко передбачити, який SQL-запит буде згенеровано й оброблено базою даних.
Варто підкреслити, що у згаданому застосунку було створено ще одну БД, яка контролюється Django ORM. Така БД потрібна для виконання інших завдань веб-застосунку та потреб бізнес-логіки — і вона чудово виконує свої задачі.
Django ORM розвивається від версії до версії, реалізуючи все більше фіч. Наприклад, у нещодавніх релізах з'явилися «Вкладені запити» або «Віконні функції» та багато інших функцій. Варто спробувати їх, перш ніж братися за чистий SQL, або, якщо у вас складніша проблема, ніж виправити декілька запитиів, — варто розглянути такі інструменти як SQLAlchemy.
Для згаданого застосунку ми обрали SQLAlchemy. Інструмент складається з двох частин — ORM та Core. SQLAlchemy ORM подібний до Django ORM, але є і відмінності. SQLAlchemy ORM використовує концепцію Data Mapper, натомість Django використовує підхід Active Record.
Оскільки ви створюєте проекти на Django, вам не треба замінювати ORM (якщо для цього немає особливих причин), тому що тоді ви не зможете використовувати Django REST фреймворк, Django-admin та інші корисні інструменти, які працюють з моделями Django.
Друга частина SQLAlchemy називається Core. Вона розміщується одразу між високорівневим ORM та низькорівневим SQL. Core дуже потужний та гнучкий: ви можете створювати будь-які SQL-запити, які потім легко зрозуміти в коді на Python.
Для прикладу розглянемо запит з документації:
q = session.query(User).filter(User.name.like('e%')).
limit(5).from_self().
join(User.addresses).filter(Address.email.like('q%')).
order_by(User.name)
Отримаємо такий результат:
SELECT anon_1.user_id AS anon_1_user_id,
anon_1.user_name AS anon_1_user_name
FROM (SELECT "user".id AS user_id, "user".name AS user_name
FROM "user"
WHERE "user".name LIKE :name_1
LIMIT :param_1) AS anon_1
JOIN address ON anon_1.user_id = address.user_id
WHERE address.email LIKE :email_1 ORDER BY anon_1.user_name
Зверніть увагу: з подібним трюком, ми не отримаємо проблему N+1: from_select створює додаткову SELECT-обгортку над запитом, тому спершу ми зменшуємо кількість рядків (через LIKE та LIMIT), а вже потім приєднуємо інформацію адреси.
Як об'єднати Django-застосунок та SQLAlchemy
Якщо вас зацікавила тема й ви вирішили спробувати використати SQLAlchemy разом з застосунком на Django, ось декілька порад, які можуть допомогти.
Перш за все, вам треба створити глобальну змінну з Engine, проте фактичне з'єднання з БД буде встановлено при першому виклику connect чи execute.
sa_engine = create_engine(settings.DB_CONNECTION_URL, pool_recycle=settings.POOL_RECYCLE)
Create_engine приймає додаткову конфігурацію для підключення. MySQL/MariaDB/AWS Aurora (сумісна з MySQL) мають налаштування interactive_timeout, що за замовчуванням становить 8 годин. Тож без додаткового параметра pool_recycle ви отримаєте надокучливу помилку:
SQLError: (OperationalError) (2006, 'MySQL server has gone away')
Тому значення POOL_RECYCLE повинно бути меншим за interactive_timeout. Наприклад, удвічі меншим за POOL_RECYCLE = 4 * 60 * 60.
Наступний крок — створення запитів. Залежно від архітектури вашого застосунку, ви можете оголошувати таблиці та поля, використовуючи класи Table та Column (які також можна використовувати з ORM). Якщо ж ваш застосунок вже має таблиці та поля, оголошені іншим чином, можете використати функції table_name та column_name, щоб використовувати їх в запитах динамічно (як пояснено тут).
У застосунку ми обрали другий варіант, оскільки інформація, формули та форматування були збережені за допомогою іншого декларативного підходу. Потім було створено шар, що читав ці структури та виконував запити на основі інструкцій.
Коли ваш запит готовий, просто викличте sa_engine.execute(query). Курсор буде відкрито, поки ви не прочитаєте усі дані або явно не закриєте його.
Варто помітити ще одну неприємну особливість. Як зазначено у документації, в SQLAlchemy обмежені можливості перетворення запиту на рядок, тому досить непросто отримати кінцевий запит для виконання. Можна надрукувати запит самостійно:
print(query)
SELECT
role_group_id, role_group_name, nr_patients
FROM "StaffSummary"
WHERE day >= :day_1 AND day <= :day_2 AND location_id = :location_id_1 AND service_id = :service_id_1
(Такий запит ще не страшний, але для складнішого запиту на 20+ плейсхолдерів доведеться витратити багато часу на заповнення вручну для подальшої взаємодії з SQL через консоль.)
Однак, якщо до запиту вам потрібно додати лише рядки та числа, такий підхід вам допоможе:
print(s.compile(compile_kwargs={"literal_binds": True}))
З датами подібний трюк не вдасться. На StackOverflow є обговорення того, як отримати бажані результати, але варіанти не дуже привабливі.
Інший варіант: активувати логування запитів у файл через конфіг БД. Однак тоді ви матимете іншу проблему: буде складно знайти запит, якщо до цієї БД під'єднана Django ORM.
Тестування
Зверніть увагу: Pytest попереджає, що «наразі pytest-django не працює з підтримкою декількох БД у Django. Однак можна використовувати звичайні Django TestCase для підтримки multi_db».
Що це може означати? За замовчуванням Django створить та видалить (по завершенню усіх тестів) тестову БД, для кожної БД з переліку DATABASES. Така фіча працює також і в pytest.
В Django TestCase та TransactionTestCase зі встановленим атрибутом multi_db=True активують видалення даних у декількох БД між тестами. Він також викликає завантаження даних в другу БД за допомогою django-fixtures. Проте краще використовувати modelmommy або factoryboy, на які атрибут multi_db не впливає.
Існує декілька способів активувати multi_db для продовження тестування з Pytest, запропонованих в обговоренні pytest-django.
Для таблиць, що мають моделі Django є важлива порада: слід зберігати дані в них через Django ORM. Інакше будуть проблеми зі створенням тестів: TestCase не зможе відкотити транзакції, які будуть за межами з'єднання Django DB. Якщо у вас виникла така ситуація, можете використати TransactionalTestCase з атрибутом multi_db=True для тестів, в яких БД функціонує через з'єднання SQLAlchemy. Проте пам'ятайте, що такі тести повільніші за звичайний TestCase.
Можливий також інший сценарій: ви маєте моделі Django лише в одній БД, а з іншою БД ви працюєте через SQLAlchemy. В такому разі multi_db жодним чином не впливатиме на код. Тут вам треба написати pytest-fixture (або зробити mixin та запустити логіку setUP, якщо ви використовуєте unittest), який створить структуру БД з SQL файлу. Такий файл повинен містити вираз DROP TABLE IF EXISTS перед CREATE TABLE. Цю фікстуру треба застосовувати до кожного тесткейсу, який працює з цією БД. Інша фікстура може завантажувати дані до таблиць.
Зверніть увагу: Подібні тести будуть повільніші, оскільки таблиці створюються повторно при кожному тесті. В ідеалі таблиці повинні створюватись один раз (@pytest.fixture(scope='session', autouse=True), а кожна транзакція повинна відкочувати дані для кожного тесту.
Такого ефекту важко досягнути через різні з'єднання Django та SQLAlchemy або різні з'єднання пулу SQLAlchemy (тобто потрібно почати транзакцію у тесті, заповнити БД даними, запустити тест та відкотити транзакцію, яка не була закомічена).
Але під час тесту код вашого застосунку може робити запити до БД, на зразок connection.execute(query), які виконуються поза транзакцією, що створює тестові дані. Тож з рівнем ізоляції транзакцій, який стоїть за замовчуванням, застосунок побачить замість даних лише пусті таблиці.
Існує можливість змінити рівень ізоляції транзакцій для SQLAlchemy на READ UNCOMMITTED — і все працюватиме, як очікувалось, проте це не вихід.
Висновок
SQLAlchemy Core — чудовий інструмент, який наближає вас до SQL, дає розуміння та повний контроль над запитами. Якщо ви створюєте застосунок (або його частину), що передбачає складну агрегацію даних, варто звернути увагу на можливості SQLAlchemy Core як альтернативу інструментам Django ORM.
Для глибшого розуміння теми я рекомендую переглянути мій виступ на Djangocon Europe 2019.
Ще немає коментарів