def test_curd_c_bulk_insert(app_with_db_inited):
""" curd create 高效批量插入
"""
data = {
'user': [
{
'name': 'aaa'
},
{
'name': 'bbb'
},
{
'name': 'ccc'
},
]
}
with app_with_db_inited.app_context():
dbsess = curd.create(data)
csr = dbsess.bind.connect().execute('select name, * from user')
rs = csr.fetchall()
# ars = dbsess.query(Table).all()
# print(ars)
# print(rs)
# print(dir(csr))
# print(csr.keys())
assert len(rs) == 3
assert rs[0][0] == 'aaa'
def test_one(app_with_db_inited, data, create_some_row):
with app_with_db_inited.app_context():
d = data['list']
dbsess = get_dbsess()
rs = OneAction(dbsess, table_map_dict=TABLES, dist_tables=d['dist_tables'],
condition=d['condition'], fields=d['fields']).do()
print([rs.name, rs.id])
def test_list(app_with_db_inited, data, create_some_row):
with app_with_db_inited.app_context():
d = data['list']
dbsess = get_dbsess()
rs = ListAction(dbsess, table_map_dict=TABLES, dist_tables=d['dist_tables'],
condition=d['condition'], order_by=d['order_by'],
fields=d['fields'], limit=d['limit']).do()
print(rs)
def test_update(app_with_db_inited, data):
""" 根据条件update
"""
d = data['update']
with app_with_db_inited.app_context():
dbsess = get_dbsess()
UpdateAction(dbsess, dist_tables=d['dist_tables'],table_map_dict=TABLES,
condition=d['condition'], data=d['data']).do()
def test_update_no_condition_error(app_with_db_inited, data):
""" 缺失condition 报异常 exc.YcmsDangerActionError
"""
d = data['update']
with pytest.raises(exc.YcmsDangerActionError):
with app_with_db_inited.app_context():
dbsess = get_dbsess()
UpdateAction(dbsess, dist_tables=d['dist_tables'],table_map_dict=TABLES,
condition=[], data=d['data']).do()
def test_update_use_wrong_field_name_of_right_table_raise_when_parse_condition_fields_not_exitsts_error(app_with_db_inited, data):
""" 根据条件update
"""
d = data['error_data']['update']['fields_error_2']
with pytest.raises(exc.YcmsSqlConditionParseError):
with app_with_db_inited.app_context():
dbsess = get_dbsess()
UpdateAction(dbsess, dist_tables=d['dist_tables'],table_map_dict=TABLES,
condition=d['condition'], data=d['data']).do()
def test_update_when_exec_update_sqltable_not_exists_error(app_with_db_inited, data):
"""
"""
d = data['error_data']['update']['table_not_exists']
with pytest.raises(exc.YcmsTableNotExistsError):
with app_with_db_inited.app_context():
dbsess = get_dbsess()
UpdateAction(dbsess, dist_tables=d['dist_tables'],table_map_dict=TABLES,
condition=d['condition'], data=d['data']).do()
def test_create_fields_error(app_with_db_inited, data):
""" 仅 bulk=True
"""
d = data['error_data']['create']['fields_error']
with pytest.raises(exc.YcmsDBFieldNotExistsError):
with app_with_db_inited.app_context():
dbsess = get_dbsess()
CreateAction(dbsess, d.keys(), TABLES, bulk=True, data=d).do()
for t in d.keys():
rs = dbsess.query(TABLES[t]).all()
print(rs)
def test_create(app_with_db_inited, data):
"""
"""
d = data
with app_with_db_inited.app_context():
dbsess = get_dbsess()
CreateAction(dbsess, d['create'].keys(), TABLES, data=d['create']).do()
for t in d['create'].keys():
rs = dbsess.query(TABLES[t])\
.filter(text('name in (%s)' %
','.join(['"' + row['name'] + '"' for row in d['create'][t] ]))).all()
assert len(rs) == len(d['create'][t])
def test_curd_c_exc_table_not_exists(app_with_db_inited):
""" 表名称错误 （表不存在）抛出异常 YcmsTableNotExistsError
"""
data = {'some_table': []}
data_01 = {'some_table': [{'a': 'b'}]}
data_02 = {'user': [{'a': 'b'}]}
with app_with_db_inited.app_context():
with pytest.raises(exc.YcmsTableNotExistsError):
dbsess, Table = curd.create(data)
dbsess_01, Table_01 = curd.create(data_01)
with pytest.raises(exc.YcmsDBFieldNotExistsError):
dbsess_02 = curd.create(data_02)
def test_create_table_not_exists_error(app_with_db_inited, data):
"""
"""
d = data['error_data']['create']['table_not_exists']
with pytest.raises(exc.YcmsTableNotExistsError):
with app_with_db_inited.app_context():
dbsess = get_dbsess()
CreateAction(dbsess, d.keys(), TABLES, data=d).do()
for t in d.keys():
rs = dbsess.query(TABLES[t])\
.filter(text('name in (%s)' %
','.join(['"' + row['name'] + '"' for row in d[t] ]))).all()
assert len(rs) == len(d[t])
def test_delete(app_with_db_inited, data):
""" 删除
"""
d = data['delete']
d_2 = data['delete_2']
d_3 = data['delete_3']
d_4 = data['delete_4']
with app_with_db_inited.app_context():
dbsess = get_dbsess()
CreateAction(dbsess, data['create'].keys(), TABLES, data=data['create']).do()
dbsess.commit()
DeleteAction(dbsess, dist_tables=d['dist_tables'],table_map_dict=TABLES,
condition=d['condition']).do()
dbsess.commit()
ids = []
for row in dbsess.query(TABLES[d['dist_tables'][0]]).with_entities(
TABLES[d['dist_tables'][0]].id, TABLES[d['dist_tables'][0]].name
).all():
ids.append(row[0])
print(row)
assert ids == [1, 2, 5, 6, 7, 8, 9]
DeleteAction(dbsess, dist_tables=d_2['dist_tables'],table_map_dict=TABLES,
condition=d_2['condition']).do()
dbsess.commit()
ids = []
for row in dbsess.query(TABLES[d['dist_tables'][0]]).with_entities(
TABLES[d['dist_tables'][0]].id, TABLES[d['dist_tables'][0]].name
).all():
ids.append(row[0])
assert len(ids) == 6 and ids == [2, 5, 6, 7, 8, 9]
DeleteAction(dbsess, dist_tables=d_3['dist_tables'],table_map_dict=TABLES,
condition=d_3['condition']).do()
dbsess.commit()
ids = []
for row in dbsess.query(TABLES[d['dist_tables'][0]]).with_entities(
TABLES[d['dist_tables'][0]].id, TABLES[d['dist_tables'][0]].name
).all():
ids.append(row[0])
assert len(ids) == 6 and ids == [2, 5, 6, 7, 8, 9]
DeleteAction(dbsess, dist_tables=d_4['dist_tables'],table_map_dict=TABLES,
condition=d_4['condition']).do()
dbsess.commit()
ids = []
for row in dbsess.query(TABLES[d['dist_tables'][0]]).with_entities(
TABLES[d['dist_tables'][0]].id, TABLES[d['dist_tables'][0]].name
).all():
ids.append(row[0])
assert ids == [2, 5, 6, 7]
def test_parse_condition_order_by(data, app_with_db_inited):
""" 测试解析orderby
"""
import time
from app.db import get_dbsess
t_map = TABLES['user']
od = ParseOrderBy(data['order_by'], table_map_dict=TABLES)
order_by = od.parse()
with app_with_db_inited.app_context():
dbsess = get_dbsess()
rows = [
t_map(name='aaa'),
t_map(name='bbb'),
t_map(name='ccc'),
t_map(name='bbb'),
t_map(name='aaa'),
t_map(name='fff'),
]
for r in rows:
# time.sleep(1)
dbsess.add(r)
dbsess.commit()
rs = dbsess.query(t_map).order_by(*order_by).all()
# for row in rs:
# print(row)
assert len(rs) == len(rows)
assert rs[0].name == 'fff'
dbsess.query(t_map).filter(t_map.id > 3).update(
{'last_update_time': 999})
dbsess.commit()
rs = dbsess.query(t_map).order_by(*order_by).all()
for row in rs:
print(row)
assert len(rs) == len(rows)
assert rs[0].last_update_time == 999
csr = dbsess.bind.execute(
'select *, avg(last_update_time), count(*) as cnt from user group by name'
)
for i in csr.fetchall():
print(i)
def test_curd_c(app_with_db_inited):
""" curd create dbsess.add_all() 这个效率低是逐条插入
"""
data = {
'user': [
{
'name': 'aaa'
},
{
'name': 'bbb'
},
{
'name': 'ccc'
},
]
}
with app_with_db_inited.app_context():
dbsess = curd.create(data, bulk=False)
csr = dbsess.bind.connect().execute('select name from user')
rs = csr.fetchall()
# print(rs)
assert len(rs) == 3
assert rs[0][0] == 'aaa'
def create_some_row(app_with_db_inited, data):
with app_with_db_inited.app_context():
dbsess = get_dbsess()
CreateAction(dbsess, data['create'].keys(), TABLES, data=data['create']).do()
dbsess.commit()
return dbsess
