def test_db():
from data_model.db import DbHandler
db_hdl = DbHandler()
#db_hdl.set_dataflow_info('dataflow_19', {'e_id':'v', 'l_id':'vv'})
#r = db_hdl.query_group_id_of_dataflow('dataflow_10')
#r = db_hdl.query_group_datafolw_count('group_25')
#r = db_hdl.delete_dataflow_info('dataflow_5')
#r = db_hdl.delete_dataflow_config('dataflow_5')
#db_hdl.remove_loader_table(2, [('KYLIND', 'KYLIN', 'TEST', None)])
#db_hdl.remove_loader_table(2, [('KYLIN', 'KYLIN', 'TEST', None)])
#r = db_hdl.get_group_loader_list('group_1')
#r = db_hdl.query_dataflow_scn('dataflow_1')
r = db_hdl.query_group_loader_list('group_1')
print(r)
def init():
db_hdl = DbHandler()
create_table()
# job()
t = threading.Thread(target=crontab, args=(u'0:0', ))
t.setDaemon(True)
t.start()
return
T_Basic_ShopBook.add(
'rmydcbs', '9787115394392',
'https://detail.tmall.com/item.htm?spm=a1z10.3-b.w4011-9986777119.15.79c15d8JGRW05&id=539853070197&rn=934e2d1ecc4ab188c0bb95e26877b606&abbucket=4'
)
exit(0)
from component.shop import Shop
Shop.get_shop_list()
exit(0)
from component.book import Book
#_id = T_Business_Template.add('xxx', [('s1', 'b1'),('s2', 'b2')])
#exit(0)
_id = T_Business_Template.query(1)
print(_id)
exit(0)
Book.get_book()
#r = T_Basic_ShopBook.query_shopbook_id('winshare', '9787115394392')
#print(r)
import datetime
# Book.add_data('winshare', '9787115394392', '销量', datetime.datetime.now(), 8);
r = Book.query_data('winshare', '9787115394392', '销量',
datetime.date(2017, 7, 27), datetime.datetime.now())
print(r)
exit(0)
def create_table():
db_hdl = DbHandler()
try:
T_Basic_Book.metadata.create_all(db_hdl.engine)
except sqlalchemy.exc.ProgrammingError as e:
pass
try:
T_Basic_Shop.metadata.create_all(db_hdl.engine)
except sqlalchemy.exc.ProgrammingError as e:
pass
try:
T_Basic_ShopBook.metadata.create_all(db_hdl.engine)
except sqlalchemy.exc.ProgrammingError as e:
pass
try:
T_Data.metadata.create_all(db_hdl.engine)
except sqlalchemy.exc.ProgrammingError as e:
pass
try:
T_Business_Template.metadata.create_all(db_hdl.engine)
except sqlalchemy.exc.ProgrammingError as e:
pass
try:
T_Business_TemplateData.metadata.create_all(db_hdl.engine)
except sqlalchemy.exc.ProgrammingError as e:
pass
try:
T_Data.metadata.create_all(db_hdl.engine)
except sqlalchemy.exc.ProgrammingError as e:
pass
def __init__():
self.db_hdl = DbHandler()
class T_Base():
db_hdl = DbHandler()
def __init__():
self.db_hdl = DbHandler()
def scan():
# query shopbook
shopbook_list = T_Basic_ShopBook.query_shopbook_list()
formula_list = Formula.query_all()
if not formula_list:
return
#sale|avg|5|>|avg|1|10%
# parse formula
db_hdl = DbHandler()
# gen sql
for f in formula_list:
formula_id = f[0]
formula = f[2].split('|')
datatype = formula[0]
agg1 = formula[1]
agg1 = agg1.replace('avg', 'mean')
duration1 = formula[2]
op = formula[3]
agg2 = formula[4]
agg2 = agg2.replace('avg', 'mean')
duration2 = formula[5]
val = formula[6]
per = True if val.endswith('%') else False
if per:
val = val[:-1]
for i in shopbook_list:
shop = i[0]
book = i[1]
value_his = 0
value = 0
_sql = "select value from t_data where shop='{shop}' and book='{book}' and datatype='{datatype}' order by date desc limit {duration}".format(
shop=shop,
book=book,
datatype=datatype,
duration=duration1)
r = db_hdl.query(_sql)
if not r:
continue
value = r[0][0]
_sql = "select value from t_data where shop='{shop}' and book='{book}' and datatype='{datatype}' order by date desc limit {duration}".format(
shop=shop,
book=book,
datatype=datatype,
duration=duration1)
# query
r = db_hdl.query(_sql)
if not r:
continue
value_his = [i[0] for i in r]
s_his = pd.Series(value_his)
if datatype in ['sale', 'comment']:
s_his_ = s_his.shift(1)
s_his = s_his - s_his_.fillna(0)
value_his = eval('s_his.{}()'.format(agg1))
bingo = False
if per:
val = float(val)
value = float(value)
value_his = float(value_his)
if value == 0:
continue
if eval("100*(value - value_his)/value {op} val".format(
op=op)):
bingo = True
else:
if eval("value - value_his {op} val".format(op=op)):
bingo = True
if bingo:
T_Business_Monitor_Data.add(shop, book, datatype, value,
value_his, formula_id)