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

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})"

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),
)

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()'

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')))"

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

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,)

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

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)),
)

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%""""

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()

instance.pk
instance.remove()
instance.save()
Model._meta
...

session = Session()
session.add(user1) # insert 
session.delete(user2) # delete

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

session.query(User).filter(User.name == 'test')

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'

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")

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()

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)

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>}

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

session.add(user)
session.commit()

assert user.dep.id
assert user.id