SQLAlchemy:

raw SQL vs Python

Денис Катаев

Tinkoff.ru

SQL

в 2018 году

LATERAL

GROUPING SETS

WITH

WITH RECURSIVE

FILTER

TABLESAMPLE

ROW_NUMBER

OVER

LIST_AGG

ROLLUP, CUBE

rawSQL

sql = """select * from table where field = 1"""

def some_code():
   ...
   cursor = connection.execute(sql)
   result = cursor.fetchall()
   ...

Эффективность важнее

читаемости ☝️

Сложно читать

select distinct 
 cast(null as integer) NZAP,
 d_ext_uslug_napr_zag.id_doc_public NNAPR,
 d_ext_uslug_napr_zag.date_doc DNAPR, 
 d_ext_uslug_napr_zag.form_med_pom  FOMP,
 tab_lpu.lpu_ext_code MONAPR,
 tab_struct_obos_unit.code_ffoms PMONAPR,
 cast (:lpu_ext_code  as varchar(20)) MO, TSOU1.code_ffoms PMO, 
 tab_other_strings.code_external VPOLIS,patients.polis_s SPOLIS,patients.polis_n NPOLIS, 
 tab_msk.smocod SMO, tab_msk.TF_OKATO TER, 
 patients.TELEFON TEL, 
 d_ext_uslug_napr_zag.ds_osn_mkb DSNAPR,
 TAB_PLACE_PARAM.profil_koek_for_foms PROFK,
 tab_profile.CODE_PROFILE_EGISZ_PRFO PROFO, 
 TAB_PLACE_PARAM.profil_koek_for_foms PROFKNAPR, 
 tab_profile.CODE_PROFILE_EGISZ_PRFO  PROFONAPR,
 users.snils MEDRAB, 
 med_kart.id_kart_public NKART, med_kart.ds_priem_mkb DS,           
 d_ext_uslug_napr_zag.date_plan_gosp  DPGOSP, 
 MED_KART.date_vipis_planed DPOGOSP  
from d_ext_uslug_napr_zag 
 left outer join patients on patients.id_pac=d_ext_uslug_napr_zag.pac_id and patients.dae>='31.12.9999'    
 left outer join tab_other_strings on tab_other_strings.id_string=patients.polis_type             
 left outer join tab_profile on tab_profile.id_profile=d_ext_uslug_napr_zag.TARGET_PODR_PROFILE   
 left outer join R_USERS_DOLGN on R_USERS_DOLGN.ID_KD=d_ext_uslug_napr_zag.NAPR_DOCTOR_KD_ID 
 left outer join users on users.id_user=d_ext_uslug_napr_zag.napr_doctor_user_id                  
 left outer join tab_msk on tab_msk.id_msk=patients.msk                                           
 left outer join tab_place on tab_place.id_place=d_ext_uslug_napr_zag.napr_place_id               
 left outer join tab_struct_obos_unit on tab_struct_obos_unit.lpu_id=d_ext_uslug_napr_zag.from_lpu_id 
 left outer join tab_struct_obos_unit TSOU1 on TSOU1.id_obos_unit=tab_place.obos_unit_id          
 left outer join d_diagn on d_diagn.id_doc = d_ext_uslug_napr_zag.id_doc                          
 left outer join med_kart on med_kart.pac_id = patients.id_pac 
 and med_kart.dt_coming>=:O_BEG and med_kart.dt_coming<=:O_END 
 left outer join d_dvig on d_dvig.med_kart=med_kart.id_kart and d_dvig.type_dvig=31                   
  left outer join  tab_place_param on tab_place_param.id_place=d_dvig.target_place                    
       and tab_place_param.dab<=:O_BEG and tab_place_param.dae>:O_BEG                                 
 left outer join tab_struct_place_har on tab_struct_place_har.place_id=tab_place.id_place         
 left outer join tab_lpu on tab_lpu.lpu_code= med_kart.napr_lpu 
where d_ext_uslug_napr_zag.IS_INCOMING=0 and d_ext_uslug_napr_zag.pay_id=1  --IS_INCOMING=0 только исходящие,      
 and d_ext_uslug_napr_zag.date_doc>=:O_BEG and d_ext_uslug_napr_zag.date_doc<=:O_END

Сложно менять

if, f'string и шаблоны с %s

def hello(key, some_iter):
    columns = [f'{column} IN %({key})s'
               for column in some_iter]
    return [f'({" OR ".join(columns)})']


def hello2(some_iter):
    sql = ','.join(some_iter)
    return f"kind IN ({sql})"
SELECT * FROM table WHERE field in ()

Теория разбитых окон

Объектно-реляционное отображение

Связь БД с концепциями ООП

ORM - Object-Relational Mapping

Объектно-реляционное несовпадение потенциалов

  • Рассогласованность объектного и реляционного подхода
  • Разница в типах данных
  • Транзакционность и ACID

Статья «Вьетнам компьютерной науки»

citforum.ru/database/articles/vietnam/

en.wikipedia.org/wiki/Object-relational_impedance_mismatch

SQLAlchemy

Работает с базой данных как с движком реляционной алгебры, а не просто с набором таблиц.

SQLAlchemy

2005 год, Michael Bayer

SQLAlchemy

SQLAlchemy CORE

  • Подключение к базе данных
  • Пулы подключений
  • Описание таблиц
  • Составление запросов

Описание таблиц

import sqlalchemy as sa

metadata = sa.MetaData()
users = sa.Table(
    'users', metadata,
    sa.Column('user_id', sa.Integer, primary_key=True),
    sa.Column('name', sa.String),
    sa.Column('fullname', sa.String),
)
users = sa.table(
    "users",
    sa.column("user_id"),
    sa.column("name"),
    sa.column("description"),
)
import sqlalchemy as sa

metadata = sa.MetaData()
users = sa.Table(
    'users', metadata,
    sa.Column('user_id', sa.Integer, primary_key=True),
    sa.Column('name', sa.String),
    sa.Column('fullname', sa.String),
    sa.ForeignKeyConstraint(['user_id'], ['t1.user_id']),
    sa.CheckConstraint('name != "kek"', name='check1')
)

Целостность данных

Index('idx1', func.lower(t1.c.somecol))
Index('idx2', t1.c.data,postgresql_with={"fillfactor":50})
Index('idx3', t1.c.data,postgresql_using='gin')
import sqlalchemy as sa

metadata = sa.MetaData()

metadata.reflect(bind=someengine)
users_table = metadata.tables['users']
addresses_table = metadata.tables['addresses']

sa.select([users_table, addresses_table])

Отображение

Запросы

ins = sa.insert(users)

str(ins)
'INSERT INTO users (id, name, fullname) '
'VALUES (:id, :name, :fullname)'

s = sa.select([users])
str(s)
'SELECT users.id, users.name, users.fullname '
'FROM users'

d = sa.delete(users)
str(d)
'DELETE FROM users'

Запросы сложнее

import sqlalchemy as sa

tmp_users = sa.table(
    'tmp_users',
    sa.column('name'),
    sa.column('fullname')
)
select = sa.select([tmp_users])
names = [users.c.name, users.c.fullname]

insert = sa.insert(users).from_select(names, select)

str(insert)
'INSERT INTO users (name, fullname) '
'SELECT tmp_users.name, tmp_users.fullname\n'
'FROM tmp_users'
SELECT m.*
FROM t1 m
  JOIN t2 th ON m.threadid = th.threadid
  JOIN t3 s ON th.visitsessionid = s.visitsessionid
WHERE m.kind NOT IN %s
AND s.visitorid = %s
AND th.state != %s

Пример

import sqlalchemy as sa
from .database import Chat, Message, VisitSession

def query():
    kinds = sa.bindparam('kinds', expanding=True)
    visitor_id = sa.bindparam('visitor_id')
    time = sa.bindparam('time')
    state = sa.bindparam('state')

    join = sa.join(Message, Chat).join(VisitSession)

    clause = Message.kind.notin_(kinds)
    clause &= VisitSession.visitor_guid == visitor_id
    clause &= Chat.state != state

    return sa.select([Message], clause, from_obj=join)

Пример

Диалекты

import sqlalchemy as sa
from sqlalchemy.dialects import sqlite, postgresql

f = sa.func.now()

str(f.compile(dialect=sqlite.dialect()))
'CURRENT_TIMESTAMP'

str(f.compile(dialect=postgresql.dialect()))
'now()'

Поддержка на Python

enum, json, fts, array types и т.д

import enum
import sqlalchemy as sa
from sqlalchemy.schema import CreateTable

class MyEnum(enum.Enum):
    one = 1
    two = 2

t = sa.Table('data', sa.MetaData(), 
             sa.Column('value', sa.Enum(MyEnum)))
str(CreateTable(t))
"CREATE TABLE data (\n"
"	value VARCHAR(5),\n"
"	CONSTRAINT myenum CHECK (value IN ('one', 'two')))"

Расширяемость

Пример: sql view

from sqlalchemy.schema import DDLElement

class CreateView(DDLElement):
    def __init__(self, name, selectable):
        self.name = name
        self.selectable = selectable
class DropView(DDLElement):
    def __init__(self, name):
        self.name = name

Пример: sql view

from sqlalchemy.ext import compiler

@compiler.compiles(CreateView)
def compile(element, compiler, **kw):
    select = compiler.sql_compiler\
                .process(element.selectable)
    return "CREATE VIEW %s AS %s" % (element.name, select)

@compiler.compiles(DropView)
def compile(element, compiler, **kw):
    return "DROP VIEW %s" % (element.name,)

Пример: sql view

import sqlalchemy as sa

def view(name, metadata, select):
    t = sa.table(name)

    for c in select.c:
        c._make_proxy(t)

    CreateView(name, select).execute_at('after-create',
                                            metadata)
    DropView(name).execute_at('before-drop',
                              metadata)
    return t

Пример: sql view

metadata = sa.MetaData(engine)
stuff = sa.Table(
    'stuff', metadata,
    sa.Column('id', sa.Integer, primary_key=True),
    sa.Column('data', sa.String(50)),
)

more_stuff = sa.Table(
    'more_stuff', metadata,
    sa.Column('id', sa.Integer, primary_key=True),
    sa.Column('stuff_id', sa.Integer, 
              sa.ForeignKey(stuff.c.id)),
    sa.Column('data', sa.String(50)),
)

Пример: sql view

columns = [
    stuff.c.id.label('id'),
    stuff.c.data.label('data'),
    more_stuff.c.data.label('moredata')
]
where = stuff.c.data.like(sa.text('"%orange%"'))
select = sa.select(columns, where, 
                   from_obj=sa.join(stuff, more_stuff))

stuff_view = view("stuff_view", metadata, select)
metadata.create_all()
...
"""CREATE VIEW stuff_view AS
SELECT stuff.id AS id, stuff.data AS data, 
more_stuff.data as moredata
FROM stuff JOIN more_stuff 
ON stuff.id = more_stuff.stuff_id
WHERE stuff.data LIKE "%orange%""""
  • Генерируем запросы SQL код с помощью Python
  • Описываем структуру базу с помощью Python
  • Нет RAW-запросов
  • Результаты запросов — это простые типы

Итог

SQLAlchemy ORM

  • Mapper
  • Declarative
  • Session
  • Query
  • Relationships

Mapper

Можно незаметно прикрутить

любой класс к базе

Declarative

import sqlalchemy as sa
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class User(Base):
    __tablename__ = 'some_table'

    id = sa.Column(sa.Integer, primary_key=True)
    name =  sa.Column(sa.String(50))


c = User()

Описываем таблицу как класс

Declarative

instance.pk
instance.remove()
instance.save()
Model._meta
...
  • Не добавляет публичных методов
  • Из коробки нет популярного паттерна active record
  • Близнец транзакции БД на стороне Python
  • Явный объект, создаваемый по необходимости, а не на каждый HTTP-запрос
session = Session()
session.add(user1) # insert 
session.delete(user2) # delete

user3.name = 'new name' 
session.add(user3) # update
session.commit()

Session

session.query(User).filter(User.name == 'test')
  • Позволяет делать запросы в базу
  • Результаты запросов — классы
  • Работает и с запросами из CORE

Query

Query

У колонок переопределены многие операторы:

where = (User.name == 'foo') | (User.name == 'bar')

str(where.compile())
'some_table.name = :name_1 OR some_table.name = :name_2'
where = User.id.op('#')(0xff) # bitwise xor postgresql

str(where.compile())
'some_table.id # :id_1'

Можно использовать любой доступный в базе оператор:

Нет встроенного get_or_create и не будет,

т.к он завязан на изоляцию транзакций READ COMMITTED

Query

Альтернативы:

  • session.merge()
  • блокировки на уровне строк
  • retry кода

Relationships

  • Связи между таблицами из разных схем
  • Даже если нет Foreign Key
  • Сложные отношения через другие таблицы
  • Можно добавлять where, несколько join'ов и т.д
  • Один ко многим
  • Многие к одному
  • Один к одному
  • Многие ко многим

Relationships

import sqlalchemy as sa
from sqlalchemy import orm
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
class Parent(Base):
    __tablename__ = 'parent'
    id = sa.Column(sa.Integer, primary_key=True)

    children = orm.relationship("Child")

class Child(Base):
    __tablename__ = 'child'
    id = sa.Column(sa.Integer, primary_key=True)
    parent_id = sa.Column(sa.Integer, 
                          sa.ForeignKey(Parent.id))

    parent = orm.relationship("Parent")
  1. lazy
  2. nolazy
  3. readonly
  4. raiseload

Когда загружать

class Parent(Base):
    __tablename__ = 'child'
    id = sa.Column(sa.Integer, primary_key=True)

    childs = relationship(Child, lazy="joined")


p = session.query(Parent).get(1) # загрузит отношения в т.ч
assert p.childs

session.query(Parent)\
    .options(lazyload(Parent.children)).all()

Как загружать

  1. select
  2. join
  3. subselect
  4. selectin
  5. dynamic
  6. noload
class Parent(Base):
    __tablename__ = 'child'
    id = sa.Column(sa.Integer, primary_key=True)

    childs = orm.relationship(Child, lazy="dynamic")

p = session.query(Parent).get(1)
c = p.childs.filter(Child.name == 'foo').limit(1)
  1. list
  2. set
  3. dict
  4. что угодно

В какую структуру данных

class Parent(Base):
    ...
    childs = orm.relationship("Child",collection_class=
                attribute_mapped_collection('name'))

p = Parent()
p.childs['foo'] = Child('foo')
p.childs
{'foo': <__main__.Child object at 0x2eaaf0>}

Создание отношений, когда Primary Key еще неизвестен

user = User('foo')
user.dep = Department('bar')

session.add(user)
session.commit()

assert user.dep.id
assert user.id

Relationships

  • Описываем таблицы
  • Настраиваем отношения
  • Интеграция c CORE
  • Управляем транзакциями с помощью Session

Итог

Код и документация

on_conflict = {'not_null':'FAIL','primary_key':'IGNORE'}

meta = MetaData()
t = Table(
    'n', meta,
    Column('test', Integer, nullable=False, 
    primary_key=True, sqlite_on_conflict=on_conflict)
)

str(CreateTable(t))
"CREATE TABLE n ("
"test INTEGER NOT NULL ON CONFLICT FAIL, "
"PRIMARY KEY (test) ON CONFLICT IGNORE)"

SQLite ON CONFLICT

  • sqlalchemy-utils
  • alembic
  • dogpile
  • ...​

Другое

AsyncIO

Статья «Asynchronous Python and Databases»

https://goo.gl/AwJbEq

  • CORE работает со стратегией sqlalchemy_aio
  • ORM только синхронно
  • Можно использовать в потоках

Когда нужно использовать

  • Противопоказаний нет
  • Когда ожидаются сложные выборки
  • Когда много М2М связей
  • Микросервисы

RedBook.io

5-ое издание

Комментарии к академическим статьям по базам данных

Спасибо

kataev

mr_apt

denis.a.kataev@gmail.com

SQLAlchemy - raw SQL vs Python

By Denis Kataev

SQLAlchemy - raw SQL vs Python

  • 2,081