def trade(date_seq_start, date_seq_end):
print('clear db...')
DBUtils.clear_db()
DBUtils.truncate('model_ev_mid')
DBUtils.truncate('model_ev_resu')
DBUtils.init_stock_pool()
# 建回测时间序列
date_temp = DBUtils.get_stock_calender(date_seq_start, date_seq_end)
date_seq = [(Utils.d2date(x)) for x in date_temp]
#开始模拟交易
index = 1
day_index = 0
print('begin trade...')
for i in range(1,len(date_seq)):
print('To Date:' + date_seq[i])
day_index += 1
# 每日推进式建模，并获取对下一个交易日的预测结果
daily_trade(date_seq[i])
if divmod(day_index+4,5)[1] == 0:
print('update ratio...')
update_ratio(date_seq[i], date_seq[i-1])
else:
update_only(date_seq[i], date_seq[i-1])
print('ALL FINISHED!!')
sharp,c_std = Utils.get_sharp_rate(DBUtils.select_my_capital())
print('Sharp Rate : ' + str(sharp))
print('Risk Factor : ' + str(c_std))
def __init__(self, state_dt):
try:
wallet = DBUtils.select_my_newest_capital()
if len(wallet) > 0:
self.cur_capital = float(wallet[0][0])
self.cur_money_rest = float(wallet[0][2])
stock_pool = DBUtils.select_my_stock_pool()
if len(stock_pool) > 0:
self.stock_pool = [x[0] for x in stock_pool if x[2] > 0]
self.stock_all = [x[0] for x in stock_pool]
self.stocks_buy_price = {x[0]: float(x[1]) for x in stock_pool}
self.stocks_hold_vol = {x[0]: int(x[2]) for x in stock_pool}
self.stocks_hold_days = {x[0]: int(x[3]) for x in stock_pool}
for i in range(len(stock_pool)):
done_temp = DBUtils.select_stock_info(state_dt,
stock_pool[i][0])
self.cur_money_lock += float(done_temp[0][3]) * float(
stock_pool[i][2])
# sql_select3 = 'select * from ban_list'
# cursor.execute(sql_select3)
# wallet3 = cursor.fetchall()
# if len(wallet3) > 0:
# self.ban_list = [x[0] for x in wallet3]
except Exception as excp:
print('ERROR:', excp)
def filter_main(stock_new, state_dt, predict_dt, poz):
#先更新持股天数
DBUtils.update_hold_days()
#根据最新的仓位调整相应持股比例
#先卖出
deal = Deal.Deal(state_dt)
stock_pool_local = deal.stock_pool
for stock in stock_pool_local:
predict = DBUtils.get_stock_predict(state_dt, stock)
ans = Operator.sell(stock, state_dt, predict)
#后买入
for stock_index in range(len(stock_new)):
deal_buy = Deal.Deal(state_dt)
# # 如果模型f1分值低于50则不买入
# sql_f1_check = "select * from model_ev_resu a where a.stock_code = '%s' and a.state_dt < '%s' order by a.state_dt desc limit 1"%(stock_new[stock_index],state_dt)
# cursor.execute(sql_f1_check)
# done_check = cursor.fetchall()
# db.commit()
# if len(done_check) > 0:
# if float(done_check[0][4]) < 0.5:
# print('F1 Warning !!')
# continue
ans = Operator.buy(stock_new[stock_index], state_dt,
poz[stock_index] * deal_buy.cur_money_rest)
del deal_buy
def get_tc_dict(db, tc):
tc_setup_steps_sql = 'select * from ui_step where istc=1 and tcortsid={0} and type=1 order by seq asc'.format(
tc['id'])
tc_teardown_steps_sql = 'select * from ui_step where istc=1 and tcortsid={0} and type=3 order by seq asc'.format(
tc['id'])
tc_steps_sql = 'select * from ui_step where istc=1 and tcortsid={0} and type=2 order by seq asc'.format(
tc['id'])
tc_setup_steps = DBUtils.db_query(db, tc_setup_steps_sql, step_fileds)
tc_teardown_steps = DBUtils.db_query(db, tc_teardown_steps_sql,
step_fileds)
tc_steps = DBUtils.db_query(db, tc_steps_sql, step_fileds)
tc_data = {
'tc_name': tc['name'],
'tc_description': tc['description'],
'setup': [],
'steps': [],
'teardown': []
}
for step in tc_setup_steps:
tc_data['setup'].append(get_step_dict(step))
for step in tc_steps:
tc_data['steps'].append(get_step_dict(step))
for step in tc_teardown_steps:
tc_data['teardown'].append(get_step_dict(step))
return tc_data
def _storeArticleToDB(article):
"""
_storeArticleToDB(Dict)
Stores the article to the SQL database
param article:Dict
"""
DB.storeToDB(article)
def record_sell(deal, opdate, stock_code, init_price, sell_price, hold_vol,
act, desc):
new_money_lock = deal.cur_money_lock - sell_price * hold_vol
new_money_rest = deal.cur_money_rest + sell_price * hold_vol
new_capital = deal.cur_capital + (sell_price - init_price) * hold_vol
new_profit = (sell_price - init_price) * hold_vol
new_profit_rate = sell_price / init_price
DBUtils.insert_my_capital(new_capital, new_money_lock, new_money_rest, act,
stock_code, hold_vol, new_profit,
new_profit_rate, desc, opdate, sell_price)
DBUtils.delete_my_stock_poll(stock_code)
def get_portfolio(stock_list, state_dt, para_window):
portfilio = stock_list
# 建评估时间序列, para_window参数代表回测窗口长度
model_test_date_start =Utils.to_date(state_dt) - datetime.timedelta(days=para_window)
date_temp = DBUtils.get_stock_calender(model_test_date_start, state_dt)
model_test_date_seq = [(Utils.d2date(x)) for x in date_temp]
stock_matrix = []
for i in range(len(model_test_date_seq)-Utils.OVER_DUE_DAYS):
ri = []
for j in range(len(portfilio)):
stocks = DBUtils.select_stock(portfilio[j],
model_test_date_seq[i],
model_test_date_seq[i+Utils.OVER_DUE_DAYS] )
prices = [x[3] for x in stocks]
base_price = 0.00
after_mean_price = 0.00
if len(prices) <= 1:
r = 0.00
else:
# 风险：当i天的股价与接下来持有股票期价股价均值的比值
base_price = prices[0]
after_mean_price = np.array(prices[1:]).mean()
r = (float(after_mean_price/base_price)-1.00)*100.00
ri.append(r)
del stocks
del prices
del base_price
del after_mean_price
stock_matrix.append(ri)
# 求协方差矩阵
cov = count_cov(stock_matrix)
ans = count_ans(cov)
ans_index = count_ans_index(ans)
resu = calculate(ans, ans_index, stock_matrix)
return resu
def test04_modify_emp(self, username, http_code, success, code, message):
# 调用修改员工
response_modify = self.emp_aip.modify_emp(app.EMPID, username,
app.HEADERS)
logging.info("修改员工的结果为:{}".format(response_modify))
# 建立连接
with DBUtils() as db:
# 执行SQL语句(根据添加员工返回的EMPID,查询数据库员工表表的username)
# 查询语句
SQL1 = "select username from bs_user where id={};".format(
app.EMPID)
logging.info("要查询的SQL语句为:{}".format(SQL1))
# 执行语句
db.execute(SQL1)
# 获取结果
result1 = db.fetchone()
logging.info("SQL查询出来的结果为:{}".format(result1))
# 断言修改结果是否正确
self.assertEqual(username, result1[0])
# 断言结果: 响应状态码,success,code,message
assert_common_utils(self, response_modify, http_code, success, code,
message)
def test04_modify_emp(self, username, http_code, success, code, message):
# 调用修改员工
response_modify = self.emp_api.modify_emp(app.EMPID,
username,
headers=app.HEADERS)
logging.info("修改员工结果为：{}".format(response_modify.json()))
# 建立连接 username：readuser
# password：iHRM_user_2019
with DBUtils() as db:
# 执行SQL语句
# 根据添加员工返回的id查询数据库中员工表的username，这样就能获取到修改之后的数据
sql = "select username from bs_user where id = {};".format(
app.EMPID)
logging.info("要查询的SQL语句为：{}".format(sql))
# 执行查询的sql语句
db.execute(sql)
# 获取返回结果
result = db.fetchone()
logging.info("SQL查询出来的结果为：{}".format(result)) # ('new_tom',)
# 断言修改结果是否正确
# 注意：如果是用fetchall()取出的数据，那么取出result时，需要有两个下标result[0][0]
self.assertEqual(username, result[0])
# 断言结果：响应状态码，success，code，message
assert_common_utils(self, response_modify, http_code, success, code,
message)
def test03_modify_emp(self, username, success, code, message, http_code):
"""调用修改员工接口"""
response = self.emp_api.modify_emp(username)
# 获取修改员工接口返回的json数据
jsondata = response.json()
# 输出json数据
logging.info("修改员工接口的返回数据为：{}".format(jsondata))
# # 建立连接
# conn = pymysql.connect("182.92.81.159", "readuser", 'iHRM_user_2019', 'ihrm')
#
# # 获取游标
# cursor = conn.cursor()
#
# # 执行查询语句，查询出添加的员工的username是不是修改的username
# sql = "select username from bs_user where id={}".format(app.EMP_ID)
# cursor.execute(sql)
#
# # 获取执行结果
# result = cursor.fetchone()[0]
# logging.info("从数据库中查询出的员工的用户名是：{}".format(result))
# self.assertEqual(username, result)
# cursor.close()
# conn.close()
with DBUtils() as db_utils:
sql = "select username from bs_user where id={}".format(app.EMP_ID)
db_utils.execute(sql)
result = db_utils.fetchone()[0]
logging.info("从数据库中查询出的员工的用户名是：{}".format(result))
# 断言
assert_common(self, response, http_code, success, code, message)
def getAgents(self,role,host,user,password,database,port): filterAgentsQuery = 'SELECT user_id, role_id FROM bima_user_role_permission WHERE user_id > 0 and role_id = '+role logging.debug(filterAgentsQuery) validAgents = DBUtils.executeSelectQuery(filterAgentsQuery, host, user, password, database, port) return validAgents
def setRemWelCalToAgent(self,agentId, rem, allocatingStatus, welCalStatusToPick,host,user,password,database,port): qry = 'UPDATE welcome_calls SET agent_id='+str(agentId)+',status='+str(allocatingStatus)+' WHERE ' qry = qry + self.getMultipleStatusQryStr(welCalStatusToPick) qry = qry + ' limit '+str(rem) logging.debug(qry) updatedCount = DBUtils.executeUpdateQuery(qry, host, user, password, database, port) return updatedCount
def test03_modify_emp(self,username,success,code,message,http_code):
# 调用修改员工接口
response = self.emp_api.modify_emp(username)
jsonData = response.json()
logging.info("修改员工接口的返回数据为:{}".format(jsonData))
# #建立连接
# conn = pymysql.connect("182.92.81.159","readuser","iHRM_user_2019","ihrm",charset = "utf8")
# #获取游标
# cursor = conn.cursor()
# #执行
# sql = "select username from bs_user where id={}".format(app.Emp_ID)
# cursor.execute(sql)
# result = cursor.fetchone()[0]
# print(result)
# logging.info("从数据库中查询出的员工的用户名是:{}".format(result))
# self.assertEqual(username,result)
# #关闭游标
# cursor.close()
# #关闭连接
# conn.close()
with DBUtils() as db_utils:
sql = "select username from bs_user where id={}".format(app.Emp_ID)
db_utils.execute(sql)
result = db_utils.fetchone()[0]
logging.info("从数据库只中查询出的员工的用户名是:{}".format(result))
#断言
asser_common(self, response, http_code, success, code, message)
def tearDownClass(cls) -> None:
sql1 = "delete log.* from mb_member_login_log log inner join mb_member ber on log.member_id = ber.id where log.member_name = '18212345676';"
DBUtils.execute_sql(sql1)
sql2 = "delete reg.* from mb_member_register_log reg inner join mb_member ber on reg.member_name = ber.phone where reg.phone = '18212345676';"
DBUtils.execute_sql(sql2)
sql3 = "delete fo.* from mb_member_info fo left join mb_member ber on fo.member_id = ber.id where fo.member_name = '18212345676';"
DBUtils.execute_sql(sql3)
sql4 = "delete from mb_member where name = '18212345676';"
DBUtils.execute_sql(sql4)
def show_pic(date_seq_start, date_seq_end):
done_set_show_btc = DBUtils.select_index(date_seq_start, date_seq_end)
btc_x = list(range(len(done_set_show_btc)))
btc_y = [x[3] / done_set_show_btc[0][3] for x in done_set_show_btc]
dict_anti_x = {}
dict_x = {}
for a in range(len(btc_x)):
dict_anti_x[btc_x[a]] = done_set_show_btc[a][0]
dict_x[done_set_show_btc[a][0]] = btc_x[a]
done_set_show_profit = DBUtils.select_profit()
profit_x = [dict_x[x[1]] for x in done_set_show_profit]
profit_y = [x[0] / done_set_show_profit[0][0] for x in done_set_show_profit]
# 绘制收益率曲线（含大盘基准收益曲线）
PltUtils.show_pic(btc_x, btc_y, profit_x, profit_y, dict_anti_x)
def collectDATA(self, in_code, start_dt, end_dt):
# 建立数据库连接，获取日线基础行情(开盘价，收盘价，最高价，最低价，成交量，成交额)
done_set = DBUtils.select_stock(in_code, start_dt, end_dt)
if len(done_set) == 0:
print("empty data...")
print('param: %s, %s, %s' % (in_code, start_dt, end_dt))
raise Exception
self.date_seq = []
self.open_list = []
self.close_list = []
self.high_list = []
self.low_list = []
self.vol_list = []
self.amount_list = []
for i in range(len(done_set)):
self.date_seq.append(done_set[i][0])
self.open_list.append(float(done_set[i][2]))
self.close_list.append(float(done_set[i][3]))
self.high_list.append(float(done_set[i][4]))
self.low_list.append(float(done_set[i][5]))
self.vol_list.append(float(done_set[i][6]))
self.amount_list.append(float(done_set[i][7]))
# 将日线行情整合为训练集(其中self.train是输入集，self.target是输出集，self.test_case是end_dt那天的单条测试输入)
self.data_train = []
self.data_target = []
self.data_target_onehot = []
self.cnt_pos = 0
self.test_case = []
for i in range(1, len(self.close_list)):
one_train_case = self.__put_all_datas(i - 1)
self.data_train.append(np.array(one_train_case))
if self.__up(i) == 1:
self.data_target.append(float(1.00))
self.data_target_onehot.append([1, 0, 0])
elif self.__up(i) == -1:
self.data_target.append(float(-1.00))
self.data_target_onehot.append([0, 0, 1])
else:
self.data_target.append(float(0.00))
self.data_target_onehot.append([0, 1, 0])
self.cnt_pos = len([x for x in self.data_target if x == 1.00])
self.test_case = np.array([
self.open_list[-1], self.close_list[-1], self.high_list[-1],
self.low_list[-1], self.vol_list[-1], self.amount_list[-1]
])
self.data_train = np.array(self.data_train)
self.data_target = np.array(self.data_target)
return 1
def test03_modify_emp(self, username, success, code, message, http_code):
response = self.emp_api.modify_emp(username)
data = response.json()
logging.info("修改员工返回的数据{}".format(data))
with DBUtils() as db_utils:
sql = "select username from bs_user where id={}".format(app.EMP_ID)
db_utils.execute(sql)
result = db_utils.fetchone(sql)[0]
logging.info("数据库中数据为：{}".format(result))
assert_common(self, response, http_code, success, code, message)
def setUpClass(cls) -> None:
sql1 = "delete log.* from mb_member_login_log log inner join mb_member ber on log.member_id = ber.id where log.member_name in ('18212345670','18212345671','18212345672','18212345673','18212345674','18212345675','18212345676');"
DBUtils.execute_sql(sql1)
logging.info("sql1={}".format(sql1))
sql2 = "delete reg.* from mb_member_register_log reg inner join mb_member ber on reg.member_name = ber.phone where reg.phone in ('18212345670','18212345671','18212345672','18212345673','18212345674','18212345675','18212345676');"
DBUtils.execute_sql(sql2)
logging.info("sql2={}".format(sql2))
sql3 = "delete fo.* from mb_member_info fo left join mb_member ber on fo.member_id = ber.id where fo.member_name in ('18212345670','18212345671','18212345672','18212345673','18212345674','18212345675','18212345676');"
DBUtils.execute_sql(sql3)
logging.info("sql3={}".format(sql3))
sql4 = "delete from mb_member where name in ('18212345670','18212345671','18212345672','18212345673','18212345674','18212345675','18212345676');"
DBUtils.execute_sql(sql4)
logging.info("sql4={}".format(sql4))
def test05_modify_emp(self):
response_modify_emp = self.emp_api.modify_emp(app.EMP_ID,"happy",app.HEADERS)
logging.info("修改员工的结果为:{}".format(response_modify_emp.json()))
common_assert(self,response_modify_emp,200,True,10000,"操作成功")
with DBUtils() as db:
sql = 'select username from bs_user where id ={}'.format(app.EMP_ID)
db.execute(sql)
result = db.fetchone()
self.assertEqual("happy", result[0])
logging.info("数据库查询结果为:{}".format(result[0]))
def findAllJson2():
db = DBUtils.DBUtils("localhost", "python", "python", "movie", 3306)
sql = 'SELECT DISTINCT h.uid,h.score,m.movieName from history h ,movie m where h.mid=m.id'
result = db.query(sql)
dic0 = {}
for line in result:
dic1 = {line[2]: line[1]}
try:
dic0[line[0]].update(dic1)
except Exception as e:
dic0[line[0]] = dic1
return dic0
def test04_updata_member_success(self, new_name, http_code, success, code,
message):
response_updata_member = self.memberapi.updata_member(
member_id, new_name, headers)
logging.info("修改的员工信息为: {}".format(response_updata_member.json()))
assert_utils(self, response_updata_member, http_code, success, code,
message)
with DBUtils() as db:
db.execute(
"select username from bs_user where id = {}".format(member_id))
result = db.fetchone()[0]
self.assertEqual(new_name, result)
logging.info("数据库查询的结果为: {}".format(result))
def test03_update_emp(self, username, success, code, message, http_code):
response = self.emp_api.update_emp(username)
jsonData = response.json()
logging.info('修改员工接口返回数据{}'.format(jsonData))
with DBUtils() as db_utis:
# 执行查询语句，查询出添加的员工的username 是不是修改的username
sql = "select username form bs_user where id={}".format(app.EMP_ID)
db_utis.execute(sql)
# 获取执行结果
result = db_utis.fetchone()[0]
logging.info("从数据库中查询出的员工用户名是：{}".format(result))
# 断言
assert_common(self, response, code, success, http_code, message)
def test4_update_emp_api(self, departmentName, status_code, success, code,
message):
"""更新员工"""
resp = self.emp_api.update_emp({"departmentName": departmentName})
jsonData = resp.json()
logging.info('更新员工接口返回的数据为：{}'.format(jsonData))
sql = 'select * from bs_user where id={}'.format(app.EMP_ID)
with DBUtils() as db_utils:
db_utils.execute(sql)
result = db_utils.fetchone()
self.assertEqual(departmentName, result[-3])
assert_commen(self, resp, status_code, success, code, message)
def test03_modify_emp(self, username, success, code, message, http_code):
"""修改员工测试方法"""
response = self.emp_api.modify_emp(username)
json_data = response.json()
logging.info("修改员工接口返回的数据为：{}".format(json_data))
with DBUtils() as db_utils:
# 执行查询语句，查询修改之后的username
sql = "select username from bs_user where id={}".format(app.EMP_ID)
db_utils.execute(sql)
# 获取执行结果
result = db_utils.fetchone()[0]
logging.info("从数据库中查询出的员工的用户名是：{}".format(result))
# 断言
assert_common(self, response, http_code, success, code, message)
def insertWelcomeCalls(count):
total = 0
user_id = 3210
agent_id = 0
status = 1
partner_country_id = 111
created_by = 1010
created_date = 'NOW()'
policy_id = 3333
valStr = ''
while (total < count):
user_id = user_id + total
valStr = valStr + '(' + str(user_id) + ',' + str(agent_id) + ',' + str(
status) + ',' + str(partner_country_id) + ',' + str(
created_by) + ',' + str(created_date) + ',' + str(
policy_id) + '),'
total = total + 1
valStr = valStr[:-1]
qry = 'INSERT INTO welcome_calls (user_id,agent_id,status,partner_country_id,created_by,created_date,policy_id) values ' + valStr
print 'insertWelcomeCalls qry: ' + qry
DBUtils.executeInsertQuery(qry)
return
def test05_modify_emp(self, case_name, new_name, http_code, success, code,
message):
response_modify_emp = self.emp_api.modify_emp(app.EMP_ID, new_name,
app.HEADERS)
logging.info("修改员工的结果为:{}".format(response_modify_emp.json()))
common_assert(self, response_modify_emp, http_code, success, code,
message)
with DBUtils() as db:
sql = 'select username from bs_user where id ={}'.format(
app.EMP_ID)
db.execute(sql)
result = db.fetchone()
self.assertEqual("apple", result[0])
logging.info("数据库查询结果为:{}".format(result[0]))
def do_insert(first_name, last_name, age, sex, income):
try:
db = DBUtils.db_connect()
cursor = db.cursor()
query = "insert into employee values(%s,%s,%s,%s,%s)"
cursor.execute(query, (first_name, last_name, age, sex, income))
db.commit()
print("insert success")
except TypeError as e:
db.rollback()
print("insert failed, do rollback", e)
finally:
db.close()
print("Insert Process Done")
def do_delete(first_name):
try:
db = DBUtils.db_connect()
cursor = db.cursor()
query = "delete from employee where username = %s"
cursor.execute(query, first_name)
db.commit()
print("delete success")
except TypeError as e:
db.rollback()
print("delete failed, do rollback", e)
finally:
db.close()
print("Delete Process Done")
def test03_modify_emp(self, username, success, code, message, http_code):
# 调用修改员工接口的返回json数据
response = self.emp_api.modify_emp(username)
# 获取修改员工接口的返回json数据
jsonData = response.json()
logging.info('修改员工接口的返回数据为{}'.format(jsonData))
# conn=pymysql.connect("182.92.81.159","readuser","iHRM_user_2019","ihrm")
# cursor=conn.cursor()
with DBUtils() as db_utils:
sql = "select username from bs_user where id={}".format(app.EMP_ID)
db_utils.execute(sql)
result = db_utils.fetchone()[0]
logging.info("从数据库查询的员工是：{}".format(result))
# self.assertEqual(username, result)
# 断言
assert_common(self, response, http_code, success, code, message)
def test03_modify_emp(self, username, success, code, message, http_code):
# 调用修改员工接口
response = self.emp_api.modify_emp(username)
# 获取修改员工接口的返回json数据
jsonData = response.json()
# 输出json数据
logging.info("修改员工接口的返回数据为:{}".format(jsonData))
with DBUtils() as db_utils:
sql = "select username from bs_user where id={}".format(app.EMP_ID)
db_utils.execute(sql)
# 获取执行结果
result = db_utils.fetchone()[0]
logging.info("从数据库中查询出的员工用户名是{}".format(result))
# 断言
assert_common(self, response, http_code, success, code, message)
def testSelectQuery():
role = ConfigReader.getValue('db', 'role')
filterAgentsQuery = 'SELECT user_id, role_id FROM bima_user_role_permission WHERE user_id < 0 and role_id = ' + role + ' limit 10'
res = DBUtils.executeSelectQuery(filterAgentsQuery)
print 'Result is None for ' + filterAgentsQuery
if res is None:
pass
print 'Zero results found for ' + filterAgentsQuery
if (len(res) == 0):
pass
print 'res: '
for row in res:
print 'user_id - ' + str(row[0]) + ', role_id - ' + str(row[1])
def artExists(link):
if DB.execSelectQuery('select * from articles\
where link=\'{}\''.format(link)) == []:
return False
return True