import sqlite3
conn = sqlite3.connect('example.db')

c = conn.cursor()

c.execute("""
CREATE TABLE stocks
    (date text, trans text, symbol text, 
     qty real, price real)
""")

c.execute("""
INSERT INTO stocks VALUES
 ('2006-01-05','BUY','RHAT',100,35.14)
""")

conn.commit()
conn.close()

t = ('RHAT',)
c.execute('SELECT * FROM stocks WHERE symbol=?', t)
print(c.fetchone())

for row in c.execute('SELECT * FROM stocks ORDER BY price'):
    print(row)
('2006-01-05', 'BUY', 'RHAT', 100, 35.14)
('2006-03-28', 'BUY', 'IBM', 1000, 45.0)
('2006-04-06', 'SELL', 'IBM', 500, 53.0)
('2006-04-05', 'BUY', 'MSFT', 1000, 72.0)

a = file.read()
time.sleep(100)
assert a == file.read()

1. if money > price:
2. 
3.    money = money - price


money > 0

1. if money > price:
2.     money = money - price



money > 0

1. if money > price:
2. 
3.    money -= price


money < 0

1. if money > price:
2.     money -= price



money < 0

[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
    [ * | expression [ [ AS ] output_name ] [, ...] ]
    [ FROM from_item [, ...] ]
    [ WHERE condition ]
    [ GROUP BY grouping_element [, ...] ]
    [ HAVING condition [, ...] ]
    [ WINDOW window_name AS ( window_definition ) [, ...] ]
    [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
    [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
    [ LIMIT { count | ALL } ]
    [ OFFSET start [ ROW | ROWS ] ]
    [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
    [ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF table_name [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ]

where from_item can be one of:

    [ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
                [ TABLESAMPLE sampling_method ( argument [, ...] ) [ REPEATABLE ( seed ) ] ]
    [ LATERAL ] ( select ) [ AS ] alias [ ( column_alias [, ...] ) ]
    with_query_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
    [ LATERAL ] function_name ( [ argument [, ...] ] )
                [ WITH ORDINALITY ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
    [ LATERAL ] function_name ( [ argument [, ...] ] ) [ AS ] alias ( column_definition [, ...] )
    [ LATERAL ] function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] )
    [ LATERAL ] ROWS FROM( function_name ( [ argument [, ...] ] ) [ AS ( column_definition [, ...] ) ] [, ...] )
                [ WITH ORDINALITY ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
    from_item [ NATURAL ] join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ]

and grouping_element can be one of:

    ( )
    expression
    ( expression [, ...] )
    ROLLUP ( { expression | ( expression [, ...] ) } [, ...] )
    CUBE ( { expression | ( expression [, ...] ) } [, ...] )
    GROUPING SETS ( grouping_element [, ...] )

and with_query is:

    with_query_name [ ( column_name [, ...] ) ] AS ( select | values | insert | update | delete )

TABLE [ ONLY ] table_name [ * ]

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

query = "SELECT * FROM users WHERE name = '%s'"
cur.execute(query % name)

q1 = [('associated_with', search_for),
      ('profile_type__slug__exact', profile_type),
      ('gender__in', gender),
      ('rank__in', rank),
      ('styles__style__in', styles),
      ('age__gte', age_from),
      ('age__lte', age_to)]
q1_list = [Q(x) for x in q1 if x[1]]

q2 = [('user__first_name__icontains', search_term),
      ('user__last_name__icontains', search_term),
      ('profile_type__name__icontains', search_term),
      ('styles__style__icontains', search_term),
      ('rank__icontains', search_term)]
q2_list = [Q(x) for x in q2 if x[1]]

if q1_list:
    objects = Profile.objects.filter(
        reduce(operator.and_, q1_list))

if q2_list:
    if objects:
        objects = objects.filter(
            reduce(operator.or_, q2_list))
    else:
        objects = Profile.objects.filter(
            reduce(operator.or_, q2_list))

if order_by_ranking_level == 'desc':
    objects = objects.order_by('-ranking_level').distinct()
else:
    objects = objects.order_by('ranking_level').distinct()

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

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'

sa.insert(users).execute([(1,2,3), (2,3,4)])

sa.select([users]).execute()

sa.delete(users).execute()

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'

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

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

session.query(User).filter(User.name == 'test')
session.query(User).filter(
    User.name.in_(['Edwardo', 'fakeuser'])
)

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

alembic revision -m "create account table"
Generating /yourproject/alembic/1975ea83b712_create_account_table.py...done

"""create account table
Revision ID: 1975ea83b712
"""

# revision identifiers, used by Alembic.
revision = '1975ea83b712'
down_revision = None
branch_labels = None

from alembic import op
import sqlalchemy as sa

def upgrade():
    pass

def downgrade():
    pass

def upgrade():
    op.create_table(
        'account',
        sa.Column('id', sa.Integer, primary_key=True),
        sa.Column('name', sa.String(50), nullable=False),
        sa.Column('description', sa.Unicode(200)),
    )

def downgrade():
    op.drop_table('account')

alembic revision --autogenerate

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:////tmp/test.db'
db = SQLAlchemy(app)


class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)

    def __repr__(self):
        return '<User %r>' % self.username

@app.route('/user/<username>')
def show_user(username):
    user = User.query.filter_by(username=username).first_or_404()
    return render_template('show_user.html', user=user)

from project.db import metadata


@pytest.fixture(autouse=True)
def _init_db():
    metadata.create_all()
    yield
    metadata.drop_all()

from project import db


def test_user_create():
    user = db.User('test')
    
    db.session.add(user)
    db.session.commit()
    
    assert user.id
    

def test_a_transaction(db_session):
   row = db_session.query(Table).get(1) 
   row.name = 'testing'

   db_session.add(row)
   db_session.commit()

def test_transaction_doesnt_persist(db_session):
   row = db_session.query(Table).get(1) 
   assert row.name != 'testing'