Как Django и Alchemy (не) справляются со сложным SQL

Михаил Попугин (S7, Python-разработчик) @ Moscow Python №77

"Иногда проект перерастает Django ORM, и в игру входит SQLAlchemy. Расскажу, как каждый из них справляется (или нет) с нашей сложной бизнес-логикой. Ещё немного о том, почему мы выбрали SQLAlchemy, а что всё-таки можно было сделать, не выходя из Django".

Видео: https://moscowpython.ru/meetup/77/django-alchemy/

Transcript

1. 1 Михаил Попугин × S7 Techlab как Django ORM и SQLAlchemy (не) справляются со сложным SQL
2. ссылки на меня t.me/mpopugin linkedin.com/in/mpopugin 2
3. обо мне 3
4. обо мне ➜ работал в S7, писал на Python 4
5. обо мне ✓ работал в S7, писал на Python ➜ делал бэкенд в ML-продукте 5
6. обо мне ✓ работал в S7, писал на Python ✓ делал бэкенд в ML-продукте ➜ написал SQL-запрос 😎 6
7. наш сложный SQL 7
8. наш сложный SQL 8 with flights as ( select 'OVB' as origin, 'VVO' as destination, 5201 as flight_number union all select 'OVB' as origin, 'VVO' as destination, 5202 as flight_number ) matching_rules as ( select * from rule, phase, flights where rule.origin = flights.origin and rule.destination = flights.destination and ( rule.flight_number = flights.flight_number or rule.flight_number is null ) ) prioritized_rules as ( select *, row_number() over ( partition by {{ pb_clause }} order by {{ ob_clause_1 }} nulls last, {{ ob_clause_2 }} nulls last, ... ) as priority from matching_rules ) select * from prioritized_rules where prioritized_rules.priority = 1;
9. наш сложный SQL 9 with flights as ( select 'OVB' as origin, 'VVO' as destination, 5201 as flight_number union all select 'OVB' as origin, 'VVO' as destination, 5202 as flight_number ) matching_rules as ( select * from rule, phase, flights where rule.origin = flights.origin and rule.destination = flights.destination and ( rule.flight_number = flights.flight_number or rule.flight_number is null ) ) prioritized_rules as ( select *, row_number() over ( partition by {{ pb_clause }} order by {{ ob_clause_1 }} nulls last, {{ ob_clause_2 }} nulls last, ... ) as priority from matching_rules ) select * from prioritized_rules where prioritized_rules.priority = 1; всё это — части одного запроса
10. наш сложный SQL 10 with flights as ( select 'OVB' as origin, 'VVO' as destination, 5201 as flight_number union all select 'OVB' as origin, 'VVO' as destination, 5202 as flight_number ) matching_rules as ( select * from rule, phase, flights where rule.origin = flights.origin and rule.destination = flights.destination and ( rule.flight_number = flights.flight_number or rule.flight_number is null ) ) prioritized_rules as ( select *, row_number() over ( partition by {{ pb_clause }} order by {{ ob_clause_1 }} nulls last, {{ ob_clause_2 }} nulls last, ... ) as priority from matching_rules ) select * from prioritized_rules where prioritized_rules.priority = 1;
11. наш сложный SQL 11 with flights as ( select 'OVB' as origin, 'VVO' as destination, 5201 as flight_number union all select 'OVB' as origin, 'VVO' as destination, 5202 as flight_number ) matching_rules as ( select * from rule, phase, flights where rule.origin = flights.origin and rule.destination = flights.destination and ( rule.flight_number = flights.flight_number or rule.flight_number is null ) ) prioritized_rules as ( select *, row_number() over ( partition by {{ pb_clause }} order by {{ ob_clause_1 }} nulls last, {{ ob_clause_2 }} nulls last, ... ) as priority from matching_rules ) select * from prioritized_rules where prioritized_rules.priority = 1; а это — CTE
12. что такое CTE 12
13. что такое CTE with flights as ( ... ), matching_rules as ( ... ), prioritized_rules as ( ... ) select * from prioritized_rules where prioritized_rules.priority = 1; 13
14. что такое CTE with flights as ( ... ), matching_rules as ( ... ), prioritized_rules as ( ... ) select * from prioritized_rules where prioritized_rules.priority = 1; 14
15. with flights as ( ... ), matching_rules as ( ... ), prioritized_rules as ( ... ) select * from prioritized_rules where prioritized_rules.priority = 1; что такое CTE 15 -- common table expressions
16. with flights as ( ... ), matching_rules as ( ... ), prioritized_rules as ( ... ) select * from prioritized_rules where prioritized_rules.priority = 1; что такое CTE 16
17. CTE упрощают жизнь 17
18. CTE упрощают жизнь select * from ( ... ) as prioritized_rules 18
19. CTE упрощают жизнь select * from ( ... ) as prioritized_rules 19 with prioritized_rules as ( ... ) select * from prioritized_rules
20. CTE упрощают жизнь select * from ( ... /* <-- */ ) as prioritized_rules 20 with prioritized_rules as ( ... /* <-- */ ) select * from prioritized_rules
21. CTE упрощают жизнь select * from ( ... ) as prioritized_rules 21 with prioritized_rules as ( ... ) select * from prioritized_rules
22. data CTE упрощают жизнь 22
23. data CTE упрощают жизнь select_1 23
24. data CTE упрощают жизнь select_1 24 select_2
25. data CTE упрощают жизнь select_1 25 select_2 select_3
26. CTE упрощают жизнь 26 select 1
27. CTE упрощают жизнь 27 select 1
28. CTE упрощают жизнь 28 select * from ( select 1 ) as t_1
29. CTE упрощают жизнь 29 select * from ( select * from ( select 1 ) as t_1 ) as t_2
30. CTE упрощают жизнь 30 select * from ( select * from ( select * from ( select 1 ) as t_1 ) as t_2 ) as t_3
31. CTE упрощают жизнь 31 select * from ( select * from ( select * from ( select 1 ) as t_1 ) as t_2 ) as t_3 select 1
32. CTE упрощают жизнь 32 select * from ( select * from ( select * from ( select 1 ) as t_1 ) as t_2 ) as t_3 select 1
33. CTE упрощают жизнь 33 select * from ( select * from ( select * from ( select 1 ) as t_1 ) as t_2 ) as t_3 with data as ( select 1 ), t_1 as (select * from data) select * from t_1;
34. CTE упрощают жизнь 34 select * from ( select * from ( select * from ( select 1 ) as t_1 ) as t_2 ) as t_3 with data as ( select 1 ), t_1 as (select * from data), t_2 as (select * from t_1) select * from t_1;
35. CTE упрощают жизнь 35 select * from ( select * from ( select * from ( select 1 ) as t_1 ) as t_2 ) as t_3 with data as ( select 1 ), t_1 as (select * from data), t_2 as (select * from t_1), t_3 as (select * from t_2) select * from t_1;
36. наша CTE с данными 36
37. наша CTE с данными with flights as ( select 'OVB' as origin, 'VVO' as destination, 5201 as flight_number union all select 'OVB' as origin, 'VVO' as destination, 5202 as flight_number ) 37
38. наша CTE с данными with flights as ( select 'OVB' as origin, 'VVO' as destination, 5201 as flight_number union all select 'OVB' as origin, 'VVO' as destination, 5202 as flight_number ) 38 origin destination flight_number OVB VVO 5201 OVB VVO 5202
39. наша CTE с данными with flights as ( select 'OVB' as origin, 'VVO' as destination, 5201 as flight_number union all select 'OVB' as origin, 'VVO' as destination, 5202 as flight_number ) 39 origin destination flight_number OVB VVO 5201 OVB VVO 5202
40. select without from — SWF 40
41. select without from — SWF 41 select 'OVB' as origin
42. select without from — SWF 42 select 'OVB' as origin origin OVB
43. select without from — SWF 43 select 'OVB' as origin, 'VVO' as destination origin destination OVB VVO
44. select without from — SWF 44 select 'OVB' as origin, 'VVO' as destination, 5201 as flight_number origin destination flight_number OVB VVO 5201
45. select without from — SWF 45 select 'OVB' as origin, 'VVO' as destination, 5201 as flight_number union all select 'OVB' as origin, 'VVO' as destination, 5202 as flight_number origin destination flight_number OVB VVO 5201 OVB VVO 5202
46. select without from — SWF 46 select 'OVB' as origin, 'VVO' as destination, 5201 as flight_number union all select 'OVB' as origin, 'VVO' as destination, 5202 as flight_number origin destination flight_number OVB VVO 5201 OVB VVO 5202
47. select without from — SWF 47 select 'OVB' as origin, 'VVO' as destination, 5201 as flight_number union all select 'OVB' as origin, 'VVO' as destination, 5202 as flight_number origin destination flight_number OVB VVO 5201 OVB VVO 5202
48. SWF удобно читать 48
49. SWF удобно читать create temp table flights as select * from ( values (5201) ) as t (flight_number); 49
50. SWF удобно читать create temp table flights as select * from ( values (5201) ) as t (flight_number); 50 select 5201 as flight_number
51. SWF удобно читать create temp table flights as select * from ( values (5201) ) as t (flight_number); 51 select 5201 as flight_number
52. SWF удобно читать create temp table flights as select * from ( values (5201) ) as t (flight_number); 52 select 5201 as flight_number
53. ещё раз, всё вместе 53
54. ещё раз, всё вместе with flights as ( select 'OVB' as origin, 'VVO' as destination, 5201 as flight_number union all select 'OVB' as origin, 'VVO' as destination, 5202 as flight_number ) 54
55. ещё раз, всё вместе with flights as ( select 'OVB' as origin, 'VVO' as destination, 5201 as flight_number union all select 'OVB' as origin, 'VVO' as destination, 5202 as flight_number ) 55
56. ещё раз, всё вместе with flights as ( select 'OVB' as origin, 'VVO' as destination, 5201 as flight_number union all select 'OVB' as origin, 'VVO' as destination, 5202 as flight_number ) 56 -- cte
57. ещё раз, всё вместе with flights as ( select 'OVB' as origin, 'VVO' as destination, 5201 as flight_number union all select 'OVB' as origin, 'VVO' as destination, 5202 as flight_number ) 57
58. ещё раз, всё вместе with flights as ( select 'OVB' as origin, 'VVO' as destination, 5201 as flight_number union all select 'OVB' as origin, 'VVO' as destination, 5202 as flight_number ) 58 -- swf -- swf
59. зачем нам ORM 59
60. зачем нам ORM ➜ можно не собирать SQL руками 60
61. зачем нам ORM ✓ можно не собирать SQL руками ➜ защита от наивных ошибок 61
62. зачем нам ORM ✓ можно не собирать SQL руками ✓ защита от наивных ошибок ➜ аккуратная работа с типами 62
63. зачем нам ORM ✓ можно не собирать SQL руками ✓ защита от наивных ошибок ✓ аккуратная работа с типами ➜ переиспользование моделей (в планах) 63
64. зачем нам ORM ✓ можно не собирать SQL руками ✓ защита от наивных ошибок ✓ аккуратная работа с типами ✓ переиспользование моделей (в планах) ➜ миграции (тоже в планах) 64
65. SWF × SQLAlchemy 65
66. SWF × SQLAlchemy 66 select 5201 as flight_number
67. SWF × SQLAlchemy 67 import sqlalchemy as sa sa.select([ sa.literal(5201).label( 'flight_number' ) ]) select 5201 as flight_number
68. SWF × SQLAlchemy 68 import sqlalchemy as sa sa.select([ sa.literal(5201).label( 'flight_number' ) ]) select 5201 as flight_number
69. SWF × SQLAlchemy 69 import sqlalchemy as sa sa.select([ sa.literal(5201).label( 'flight_number' ) ]) select 5201 as flight_number
70. SWF × SQLAlchemy 70 import sqlalchemy as sa sa.select([ sa.literal(5201).label( 'flight_number' ) ]) select 5201 as flight_number хорошо
71. SWF × Django ORM 71
72. SWF × Django ORM 72 select 5201 as flight_number
73. SWF × Django ORM 73 from django.db import ( connections ) conn = connections['rules'] swf_query = ... with conn.cursor() as cursor: cursor.execute(swf_query) data = cursor.fetchall() select 5201 as flight_number
74. SWF × Django ORM 74 from django.db import ( connections ) conn = connections['rules'] swf_query = ... with conn.cursor() as cursor: cursor.execute(swf_query) data = cursor.fetchall() select 5201 as flight_number
75. SWF × Django ORM 75 from django.db import ( connections ) conn = connections['rules'] swf_query = ... with conn.cursor() as cursor: cursor.execute(swf_query) data = cursor.fetchall() select 5201 as flight_number плохо
76. CTE × SQLAlchemy 76
77. CTE × SQLAlchemy with flights as ( ... ) select * from flights; 77
78. CTE × SQLAlchemy with flights as ( ... ) select * from flights; 78 import sqlalchemy as sa ... flights = ... flights_cte = \ flights.cte( name='flights' ) session.query(flights_cte)
79. CTE × SQLAlchemy with flights as ( ... /* <-- */ ) select * from flights; 79 import sqlalchemy as sa ... flights = ... flights_cte = \ flights.cte( name='flights' ) session.query(flights_cte)
80. CTE × SQLAlchemy with flights as ( ... /* <-- */ ) select * from flights; 80 import sqlalchemy as sa ... flights = ... flights_cte = \ flights.cte( name='flights' ) session.query(flights_cte)
81. CTE × SQLAlchemy with flights as ( ... /* <-- */ ) select * from flights; 81 import sqlalchemy as sa ... flights = ... flights_cte = \ flights.cte( name='flights' ) session.query(flights_cte) хорошо
82. CTE × Django ORM 82
83. 83 with flights as ( ... ) select * from flights; CTE × Django ORM
84. 84 from django.db import ( connections ) conn = connections['rules'] cte_query = ... with conn.cursor() as cursor: cursor.execute(cte_query) data = cursor.fetchall() with flights as ( ... ) select * from flights; CTE × Django ORM
85. CTE × Django ORM 85 from django.db import ( connections ) conn = connections['rules'] cte_query = ... with conn.cursor() as cursor: cursor.execute(cte_query) data = cursor.fetchall() with flights as ( ... ) select * from flights;
86. CTE × Django ORM 86 from django.db import ( connections ) conn = connections['rules'] cte_query = ... with conn.cursor() as cursor: cursor.execute(cte_query) data = cursor.fetchall() with flights as ( ... ) select * from flights; плохо
87. reflection 87
88. reflection import sqlalchemy as sa engine = \ sa.create_engine('...') metadata = sa.MetaData(engine) Rule = sa.Table( 'rule', metadata, autoload=True, autoload_with=engine ) 88 бонус
89. reflection import sqlalchemy as sa engine = \ sa.create_engine('...') metadata = sa.MetaData(engine) Rule = sa.Table( 'rule', metadata, autoload=True, autoload_with=engine ) 89 бонус
90. reflection import sqlalchemy as sa engine = \ sa.create_engine('...') metadata = sa.MetaData(engine) Rule = sa.Table( 'rule', metadata, autoload=True, autoload_with=engine ) 90 бонус
91. reflection import sqlalchemy as sa engine = \ sa.create_engine('...') metadata = sa.MetaData(engine) Rule = sa.Table( 'rule', metadata, autoload=True, autoload_with=engine ) 91 python manage.py \ inspectdb \ --database rules \ > models.py бонус
92. выводы 92
93. выводы ➜ сложный SQL — реальность 93
94. выводы ✓ сложный SQL — реальность ➜ на SQLAlchemy можно начать 94
95. выводы ✓ сложный SQL — реальность ✓ на SQLAlchemy можно начать ➜ на ней удобно продолжить 95
96. выводы ✓ сложный SQL — реальность ✓ на SQLAlchemy можно начать ✓ на ней удобно продолжить ➜ Django ORM часто не тянет 😒 96
97. выводы ✓ сложный SQL — реальность ✓ на SQLAlchemy можно начать ✓ на ней удобно продолжить ✓ Django ORM часто не тянет 😒 ➜ выбор очевиден 97
98. 98 это всё давайте обсудим