Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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

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

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

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

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

Moscow Python Meetup
PRO

June 09, 2022
Tweet

More Decks by Moscow Python Meetup

Other Decks in Programming

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 это всё давайте обсудим