SQLAlchemy: СЛОЖНЫЕ запросы: JOIN, CTE, subquery, оконные функции #6

Началось всё отсюда:

Продолжаем...

CTE - Common Table Expressions (Распространенные табличные выражения)

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

Будет два класса SyncORM и AsyncORM в файле orm.py и два SyncCore и AsyncCore в файле core.py, а в main.py будет делаться выбор в пользу того или иного способа.

И вызываться оно будет из командной строки путём передачи аргументов с теми или иными вариантами запуска скрипта main.py, например:

После чего пойдет выполнение всех включенных функций в зависимости от указанных аргументов при вызове скрипта. А если никаких аргументов не указать или указать что-то не то, то собственно...

...ничего и не произойдет.

Собственно файл main.py и как в нём реализована обработка классов и методов классов, в зависимости от указанных аргументов:

синхронный вариант

Ну и соответственно нужно файлы orm.py и core.py привести к синхронному и асинхронному вариантам. Файл orm.py с двумя классами SyncORM и AsyncORM (получается много картинок, поэтому упакую это дело в галерею), плюс в таблицу "резюме" добавлено еще немного этих "резюме", чтобы результаты запроса в будущем были интересней:

Примерно тоже самое сделаем с файлом core.py (для этого придется создать в models.py переменную resumes_table, по аналогии с workers_table:

возможно можно было не менять имя таблицы с resumes на resumes_1, я это сделал - на всякий случай, дабы имена не конфликтовали... Пусть будет так!

После модернизации нашей программы получилась вот такая структура:

файлы core_old.py и orm_old.py - там код из самых первых вариантов программы (не стал удалять, дабы можно было посмотреть - с чего всё начиналось, хотя - может быть зря сохранил...)

После изменения напишем наши методы, в которых будут реализованны сложные запросы, подзапросы, CTE (Common Table Expressions -Распространенные табличные выражения) и оконные функции.

Итак, нам нужно сделать сложный запрос к базе данных при помощи SQLAlchemy, который на языке SQL будет выглядеть вот таким образом:

Если по-русски, то мы хотим узнать - кому работодатель переплачивает а кому наоборот недоплачивает исходя из средней зарплаты и рабочей нагрузки (не факт что я правильно понял смысл задачи).

Данный сложный запрос поделим на несколько частей, первая часть:

Вот этот кусок кода оформим в один подзапрос, см ниже как это реализуется в коде SQLAlchemy

aliased используется для сокращения длины записи

Вторая - используем CTE:

Третья, объединяем это всё в один запрос и выведем в консоль и сделаем запрос к базе данных:

В полном варианте это выглядит так:

Запустим выполнение скрипта из командной строки:

python main.py --orm --sync

Программа выдаст вот такой ответ, где рамкой выделен сформированный SQLAlchemy запрос, под которым приходит ответ из БД:

Примено так же делается и асинхронный вариант данного запроса, путём добавления async, await и использования соответствующего движка:

и вызывается это всё командой: python main.py --orm --async

Так как связи между таблицами при использовании Table() не настроены, а в core используются именно этот класс

То и рассматриваться вариант с core здесь не будет...

Ну вот как-то так. И на этом пожалуй всё. Продолжение следует...