Как правильно делать upsert в SQLAlchemy?

Question

[Jolt]

Вроде частая задача но в англоязычных интернетах не могу найти решения нормального.
Предположим у нас стандартная ETL задача запускается через Apache Airflow.
Она пишет в таблицу "Statistic". У таблицы есть уникальность по (user, date), мы же не хотим дублирования данных, зачем нам это?

Процесс простой:
1) создаешь orm объект

Statistic(user='Олег', date='2022-03-31', value='145')

2) добавляешь его в сессию через session.add()
3) делаешь в session.commit()

Все просто, все даже работает.
Но если вдруг мы хотим перезапустить задачку, вы попадаем в ситуацию когда это не работает, потому что для Олега уже данные в БД записаны. И код вообще ничего не сделает.

Обычно это называется Upsert, и в postgresql это делается через

insert into...on conflict do update set value = EXTENDED.value

Это можно реализовать через from sqlalchemy.dialects.postgresql import insert
Но погодите, а как тогда:
1) тесты писать с моком через inmemory sqlite? там эти конструкции я полагаю не сработают?
2) тогда весь код придется переписывать с элегантного добавления в сессию (где вся магия делается под капотом), на конструкции с insert, что в итоге выглядит как "легче было сразу написать на обычных текстовых запросах"
3) пропадает сразу плюс алхимии в легкой миграции кода на другие бд, скажем mysql (проблема схожа с пунктом 1)

В общем поясните пожалуйста что я не так делаю?
Как добавить новую статистику для выдуманного Олега в сессию чтобы там просто обновился value на новый?

Answer 1

[Zolg]

inmemory sqlite? там эти конструкции я полагаю не сработают?

сама конструкция в SQLite имеется, и sqlalchemy.dialects.sqlite поддерживается. Насколько ее поведение идентично postgresql - не подскажу, но для простых случаев скорее всего - да.

3) пропадает сразу плюс алхимии в легкой миграции кода на другие бд, скажем mysql (проблема схожа с пунктом 1)

В общем поясните пожалуйста что я не так делаю?

Вы хотите одновременно использовать и нестандартный SQL синтаксис и сохранять совместимость между разными реализациями.

Тут либо расширять алхимию под ваши задачи, адресно реализуя для общего случая и отдельных диалектов, либо задуматься над тем, а действительно ли эти upsert'ы настолько массовы, что оптимизация под ON CONFLICT действительно принесет существенный выигрыш по сравнению с банальным предварительным SELECT и выбором INSERT или UPDATE на стороне питона.

Comments

[Jolt]

Вы хотите одновременно использовать и нестандартный SQL синтаксис и сохранять совместимость между разными реализациями.

я хочу просто сделать upsert, это довольно популярная операция в базах данных, поэтому мне кажется что в алхимии это должно как-то делаться более простым путем чем через разные диалекты
пункт про миграции конечно приведен тут скорее для примера)

действительно ли эти upsert'ы настолько массовы

довольно массовы, в apache airflow таски должны быть максимально идемпотентны, чтобы их можно было легко перезапустить

предварительным SELECT и выбором INSERT или UPDATE на стороне питона

upsert все-таки атомарная операция

[galaxy]

Jolt,

я хочу просто сделать upsert, это довольно популярная операция в базах данных, поэтому мне кажется что в алхимии это должно как-то делаться более простым путем чем через разные диалекты

Не особо популярная, и не стандартизированная. Сравнить тот же mysql и postgres:
* синтаксис разный (ну бог с ним, адаптация синтаксиса - проблема ORM)
* семантика разная: postgres позволяет указать колонку или индекс, в которой возникает нарушение уникальности, а mysql - нет (и черт его знает, как он себя поведет). А в oracle и вовсе нет такого синтаксиса (зато есть MERGE)