Как использовать union и join в одном SQLAlchemy запросе?

Question

drew_dru @drew_dru

Как использовать union и join в одном SQLAlchemy запросе?

query1

self.session.query(ID_Streets, ID_Streets_history, ID_Nps, Modify_reason)\
            .outerjoin(ID_Streets_history, text('(ID_Streets.id_np=ID_Streets_history.id_np '\
                'AND ID_Streets.id_street=ID_Streets_history.id_street)'))\
            .join(ID_Nps, ID_Nps.id_np == ID_Streets.id_np)\
            .outerjoin(Modify_reason, text('(ID_Streets_history.code_reason=Modify_reason.code_reason '\
                'AND ID_Streets_history.code_detail=Modify_reason.code_detail)'))

query2

query2 = self.session.query(\
                ID_Streets_history, ID_Nps, Modify_reason,\
                sql.null().label('id_streets_id_np'),\
                sql.null().label('id_streets_id_street'),\
                sql.null().label('id_streets_name_street'),\
                sql.null().label('id_streets_type_street'))\
                .outerjoin(ID_Nps, ID_Nps.id_np == ID_Streets_history.id_np)\
                .outerjoin(Modify_reason, text('(ID_Streets_history.code_reason=Modify_reason.code_reason '\
                    'AND ID_Streets_history.code_detail=Modify_reason.code_detail)'))\
                .group_by(ID_Nps.id_region,\
                    ID_Nps.id_atu, ID_Nps.id_selsov, ID_Nps.id_np, ID_Streets_history.id_np,\
                    ID_Streets_history.id_street, ID_Streets_history.id_row,\
                    Modify_reason.code_reason, Modify_reason.code_detail)

query = query1.union(query2)

Сгенерированный sql

SELECT anon_1.id_streets_id_np AS anon_1_id_streets_id_np,
	anon_1.id_streets_id_street AS anon_1_id_streets_id_street,
	anon_1.id_streets_name_street AS anon_1_id_streets_name_street,
	anon_1.id_streets_type_street AS anon_1_id_streets_type_street,
	anon_1.id_nps_id_region AS anon_1_id_nps_id_region,
	anon_1.id_nps_id_atu AS anon_1_id_nps_id_atu,
	anon_1.id_nps_id_selsov AS anon_1_id_nps_id_selsov,
	anon_1.id_nps_id_np AS anon_1_id_nps_id_np,
	anon_1.id_nps_name_np AS anon_1_id_nps_name_np,
	anon_1.id_nps_type_np AS anon_1_id_nps_type_np,
	anon_1.id_nps_okato AS anon_1_id_nps_okato,
	anon_1.id_nps_oktmo AS anon_1_id_nps_oktmo,
	anon_1.id_streets_history_id_np AS anon_1_id_streets_history_id_np,
	anon_1.id_streets_history_id_street AS anon_1_id_streets_history_id_street,
	anon_1.id_streets_history_id_row AS anon_1_id_streets_history_id_row,	
	anon_1.id_streets_history_name_street AS anon_1_id_streets_history_name_street,
	anon_1.id_streets_history_type_street AS anon_1_id_streets_history_type_street,
	anon_1.id_streets_history_code_reason AS anon_1_id_streets_history_code_reason,
	anon_1.id_streets_history_code_detail AS anon_1_id_streets_history_code_detail,
	anon_1.id_streets_history_creater AS anon_1_id_streets_history_creater,
	anon_1.id_streets_history_createddate AS anon_1_id_streets_history_createddate,
	anon_1.id_streets_history_updater AS anon_1_id_streets_history_updater,
	anon_1.id_streets_history_updateddate AS anon_1_id_streets_history_updateddate,
	anon_1.id_streets_history_recordcomment AS anon_1_id_streets_history_recordcomment,
	anon_1.id_streets_history_sourcecomment AS anon_1_id_streets_history_sourcecomment,
	anon_1.modify_reason_code_reason AS anon_1_modify_reason_code_reason,
	anon_1.modify_reason_code_detail AS anon_1_modify_reason_code_detail,
	anon_1.modify_reason_comment_reason AS anon_1_modify_reason_comment_reason 
FROM (SELECT id_streets.id_np AS id_streets_id_np,
	id_streets.id_street AS id_streets_id_street,
	id_streets.name_street AS id_streets_name_street,
	id_streets.type_street AS id_streets_type_street,
	id_nps.id_region AS id_nps_id_region,
	id_nps.id_atu AS id_nps_id_atu,
	id_nps.id_selsov AS id_nps_id_selsov,
	id_nps.id_np AS id_nps_id_np,
	id_nps.name_np AS id_nps_name_np,
	id_nps.type_np AS id_nps_type_np,
	id_nps.okato AS id_nps_okato,
	id_nps.oktmo AS id_nps_oktmo,
	id_streets_history.id_np AS id_streets_history_id_np,
	id_streets_history.id_street AS id_streets_history_id_street,
	id_streets_history.id_row AS id_streets_history_id_row,
	id_streets_history.name_street AS id_streets_history_name_street,
	id_streets_history.type_street AS id_streets_history_type_street,
	id_streets_history.code_reason AS id_streets_history_code_reason,
	id_streets_history.code_detail AS id_streets_history_code_detail,
	id_streets_history.creater AS id_streets_history_creater,
	id_streets_history.createddate AS id_streets_history_createddate,
	id_streets_history.updater AS id_streets_history_updater,
	id_streets_history.updateddate AS id_streets_history_updateddate,
	id_streets_history.recordcomment AS id_streets_history_recordcomment,
	id_streets_history.sourcecomment AS id_streets_history_sourcecomment,
	modify_reason.code_reason AS modify_reason_code_reason,
	modify_reason.code_detail AS modify_reason_code_detail,
	modify_reason.comment_reason AS modify_reason_comment_reason 
FROM id_streets
	JOIN id_nps ON id_nps.id_np = id_streets.id_np
	LEFT OUTER JOIN id_streets_history ON (
		ID_Streets.id_np=ID_Streets_history.id_np
		AND ID_Streets.id_street=ID_Streets_history.id_street)
	LEFT OUTER JOIN modify_reason ON (
		ID_Streets_history.code_reason=Modify_reason.code_reason
		AND ID_Streets_history.code_detail=Modify_reason.code_detail)
	GROUP BY id_streets.id_np, id_streets.id_street, id_nps.id_region, id_nps.id_atu, id_nps.id_selsov, id_nps.id_np, id_streets_history.id_np, id_streets_history.id_street, id_streets_history.id_row, modify_reason.code_reason, modify_reason.code_detail

UNION DISTINCT SELECT 
	id_nps.id_region AS id_nps_id_region, id_nps.id_atu AS id_nps_id_atu,
	id_nps.id_selsov AS id_nps_id_selsov, id_nps.id_np AS id_nps_id_np,
	id_nps.name_np AS id_nps_name_np, id_nps.type_np AS id_nps_type_np,
	id_nps.okato AS id_nps_okato, id_nps.oktmo AS id_nps_oktmo,

	id_streets_history.id_np AS id_streets_history_id_np,
	id_streets_history.id_street AS id_streets_history_id_street,
	id_streets_history.id_row AS id_streets_history_id_row,
	id_streets_history.name_street AS id_streets_history_name_street,
	id_streets_history.type_street AS id_streets_history_type_street,
	id_streets_history.code_reason AS id_streets_history_code_reason,
	id_streets_history.code_detail AS id_streets_history_code_detail,
	id_streets_history.creater AS id_streets_history_creater,
	id_streets_history.createddate AS id_streets_history_createddate,
	id_streets_history.updater AS id_streets_history_updater,
	id_streets_history.updateddate AS id_streets_history_updateddate,
	id_streets_history.recordcomment AS id_streets_history_recordcomment,
	id_streets_history.sourcecomment AS id_streets_history_sourcecomment,

	modify_reason.code_reason AS modify_reason_code_reason,
	modify_reason.code_detail AS modify_reason_code_detail,
	modify_reason.comment_reason AS modify_reason_comment_reason,
	
	NULL AS id_streets_id_np,
	NULL AS id_streets_id_street,
	NULL AS id_streets_name_street,
	NULL AS id_streets_type_street


FROM id_streets_history
	LEFT OUTER JOIN id_nps ON id_nps.id_np = id_streets_history.id_np
	LEFT OUTER JOIN modify_reason ON (
		ID_Streets_history.code_reason=Modify_reason.code_reason
		AND ID_Streets_history.code_detail=Modify_reason.code_detail)
	GROUP BY id_nps.id_region, id_nps.id_atu, id_nps.id_selsov, id_nps.id_np, id_streets_history.id_np, id_streets_history.id_street, id_streets_history.id_row, modify_reason.code_reason, modify_reason.code_detail
)AS anon_1

В postgreSql сгенерированный код не работает т.к. для UNION важен порядок колонок после SELECT.
Порядок колонок в query2 пришлось изменить т.к. sqlalchemy на такой порядок:

spoiler

self.session.query(\
                sql.null().label('id_streets_id_np'),\
                sql.null().label('id_streets_id_street'),\
                sql.null().label('id_streets_name_street'),\
                sql.null().label('id_streets_type_street'),\
                ID_Streets_history, ID_Nps, Modify_reason)

Выдаёт следующую ошибку:

Don't know how to join from NULL; please use select_from() to establish the left entity/selectable of this join

Можно ли как-то иначе исправить эту ошибку, не изменяя порядок колонок, или перед вызовом union изменить порядок колонок?

Вопрос задан более трёх лет назад
374 просмотра

2 комментария

Подписаться 1 Оценить 2 комментария

drew_dru @drew_dru Автор вопроса

UPDATE:
Так session.query обрубает join'ы в запросе:

spoiler

from sqlalchemy import outerjoin
...
query_test = self.session.query(\
                sql.null().label('id_streets_id_np'),\
                sql.null().label('id_streets_id_street'),\
                sql.null().label('id_streets_name_street'),\
                sql.null().label('id_streets_type_street'),\
                ID_Streets_history,\
                outerjoin(ID_Streets_history, ID_Nps, ID_Nps.id_np == ID_Streets_history.id_np),\
                outerjoin(ID_Streets_history, Modify_reason, text(\
                    '(ID_Streets_history.code_reason=Modify_reason.code_reason '\
                    'AND ID_Streets_history.code_detail=Modify_reason.code_detail)')))

Написано более трёх лет назад

drew_dru @drew_dru Автор вопроса

При следующем query2 при объединении падает с ошибкой:

All selectables passed to CompoundSelect must have identical numbers of columns; select #1 has 28 columns, select #2 has 31

query2

query2 = self.session.query(\
                sql.null().label('id_streets_id_np'),\
                sql.null().label('id_streets_id_street'),\
                sql.null().label('id_streets_name_street'),\
                sql.null().label('id_streets_type_street'),\
                ID_Streets_history, ID_Nps, Modify_reason)
            query2 = outerjoin(query2, ID_Nps, ID_Nps.id_np == ID_Streets_history.id_np)
            query2 = outerjoin(query2, Modify_reason, text(\
                    '(ID_Streets_history.code_reason=Modify_reason.code_reason '\
                    'AND ID_Streets_history.code_detail=Modify_reason.code_detail)'))

Но при генерации query2 количество колонок не изменилось.

Сгенерированный query2 перед объединением

(SELECT NULL AS id_streets_id_np, NULL AS id_streets_id_street, NULL AS id_streets_name_street, NULL AS id_streets_type_street, id_streets_history.id_np AS id_streets_history_id_np, id_streets_history.id_street AS id_streets_history_id_street, id_streets_history.id_row AS id_streets_history_id_row, id_streets_history.name_street AS id_streets_history_name_street, id_streets_history.type_street AS id_streets_history_type_street, id_streets_history.code_reason AS id_streets_history_code_reason, id_streets_history.code_detail AS id_streets_history_code_detail, id_streets_history.creater AS id_streets_history_creater, id_streets_history.createddate AS id_streets_history_createddate, id_streets_history.updater AS id_streets_history_updater, id_streets_history.updateddate AS id_streets_history_updateddate, id_streets_history.recordcomment AS id_streets_history_recordcomment, id_streets_history.sourcecomment AS id_streets_history_sourcecomment, id_nps.id_region AS id_nps_id_region, id_nps.id_atu AS id_nps_id_atu, id_nps.id_selsov AS id_nps_id_selsov, id_nps.id_np AS id_nps_id_np, id_nps.name_np AS id_nps_name_np, id_nps.type_np AS id_nps_type_np, id_nps.okato AS id_nps_okato, id_nps.oktmo AS id_nps_oktmo, modify_reason.code_reason AS modify_reason_code_reason, modify_reason.code_detail AS modify_reason_code_detail, modify_reason.comment_reason AS modify_reason_comment_reason 
FROM id_streets_history, id_nps, modify_reason) LEFT OUTER JOIN id_nps ON id_nps.id_np = id_streets_history.id_np LEFT OUTER JOIN modify_reason ON (ID_Streets_history.code_reason=Modify_reason.code_reason AND ID_Streets_history.code_detail=Modify_reason.code_detail)

Написано более трёх лет назад

Решения вопроса 1

Комментировать

Пригласить эксперта

Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации

Похожие вопросы

Python

Простой
Как в конце каждой строки файла добавить тэг?
- 1 подписчик
- 6 часов назад
- 64 просмотра
1

ответ
Python

+1 ещё

Простой
Почему asyncio.current_task() не передается в функцию?
- 1 подписчик
- 7 часов назад
- 46 просмотров
1

ответ
Python

+2 ещё

Простой
Срабатывает антивирус на скомпилированный файл python, как исправить?
- 1 подписчик
- вчера
- 168 просмотров
1

ответ
Python

Простой
Почему не срабатывает if? как это пофиксить?
- 1 подписчик
- вчера
- 131 просмотр
2

ответа
Python

+2 ещё

Простой
Как транслировать аудио в микрофон, py, c#, c++?
- 1 подписчик
- вчера
- 151 просмотр
3

ответа
Python

Средний
Почему Низкий FPS анализ изображения yolov8 YOLO?
- 1 подписчик
- вчера
- 27 просмотров
0

ответов
SQLAlchemy

Простой
Не обновляется поле в модели БД с помощью SQLAlchemy — session.add() — session.commit()?
- 1 подписчик
- вчера
- 50 просмотров
1

ответ
Python

+2 ещё

Простой
Почему Планировщик задач Windows 10 не выполняет скрипт Python?
- 3 подписчика
- вчера
- 1576 просмотров
3

ответа
Python

+1 ещё

Средний
Как переместить мышь вместе с элементом в pyppeteer?
- 1 подписчик
- 21 апр.
- 90 просмотров
0

ответов
Python

+2 ещё

Простой
OK.RU, API приложений, не могу загрузить видео: User must grant an access to permission 'VIDEO_CONTENT'". Как получить этиправа для приложения?
- 1 подписчик
- 21 апр.
- 54 просмотра
1

ответ
Показать ещё Загружается…

Team Lead (С++, Python)

TopAssistant • Москва

от 400 000 ₽

Python developer

Bell Integrator

До 350 000 ₽

Python developer

Greenway Global • Новосибирск

от 150 000 ₽

Репетитор javascript vue.js tauri rust

23 апр. 2024, в 23:58

3000 руб./в час

Разработка дизайна раздела «Статьи» на сайте «Мир отходов»

23 апр. 2024, в 23:01

10000 руб./за проект

Дизайн личного кабинета (клиентская часть)

23 апр. 2024, в 22:37

500 руб./в час

UPDATE:
Так session.query обрубает join'ы в запросе:
spoiler
from sqlalchemy import outerjoin ... query_test = self.session.query(\ sql.null().label('id_streets_id_np'),\ sql.null().label('id_streets_id_street'),\ sql.null().label('id_streets_name_street'),\ sql.null().label('id_streets_type_street'),\ ID_Streets_history,\ outerjoin(ID_Streets_history, ID_Nps, ID_Nps.id_np == ID_Streets_history.id_np),\ outerjoin(ID_Streets_history, Modify_reason, text(\ '(ID_Streets_history.code_reason=Modify_reason.code_reason '\ 'AND ID_Streets_history.code_detail=Modify_reason.code_detail)')))

Answer 1 · 2017-04-05 11:03:27

query1 = self.session.query(ID_Streets, ID_Streets_history, ID_Nps, Modify_reason)\
            .outerjoin(ID_Streets_history, text('(ID_Streets.id_np=ID_Streets_history.id_np '\
                'AND ID_Streets.id_street=ID_Streets_history.id_street)'))\
            .join(ID_Nps, ID_Nps.id_np == ID_Streets.id_np)\
            .outerjoin(Modify_reason, text('(ID_Streets_history.code_reason=Modify_reason.code_reason '\
                'AND ID_Streets_history.code_detail=Modify_reason.code_detail)'))

query2 = self.session.query(\
                sql.null().label('id_streets_id_np'),\
                sql.null().label('id_streets_id_street'),\
                sql.null().label('id_streets_name_street'),\
                sql.null().label('id_streets_type_street'),
                ID_Streets_history, ID_Nps, Modify_reason)\
                .select_from(ID_Streets_history, ID_Nps, Modify_reason)\
                .outerjoin(ID_Nps, ID_Nps.id_np == ID_Streets_history.id_np)\
                .outerjoin(Modify_reason, text(\
                    '(ID_Streets_history.code_reason=Modify_reason.code_reason '\
                    'AND ID_Streets_history.code_detail=Modify_reason.code_detail)'))

query = query1.union(query2)

Как использовать union и join в одном SQLAlchemy запросе?

Войдите, чтобы написать ответ

Минуточку внимания

Войдите на сайт