def update_db(rows):
err_row = None
try:
i = 0
with db_utils.get_daily_connect() as con:
cur = con.cursor()
for row in rows:
err_row = row
enforce_dt = row.get('enforce_dt')
if enforce_dt is None:
print("not enforce_dt value." + str(row))
continue
result = cur.execute(
"""update cb_history set is_enforce=1 where bond_id=:bond_id and last_chg_dt=:enforce_dt""",
{
'enforce_dt': datetime.strptime(
enforce_dt, '%Y-%m-%d'),
'bond_id': row.get('bond_code')
})
if result.rowcount != 1:
print('update cb_history is failure. row:' + str(row))
else:
i += 1
print("update complete. count:" + str(i))
except Exception as e:
print("db操作出现异常. err_row" + str(err_row), e)
raise e
finally:
# 暂停3s再执行， 避免被网站屏蔽掉
time.sleep(3)
def start_roll(current_day, total_money, old_group=None):
# 买入的组合转债信息 {code:{amount:xxx}}
group = {}
with db_utils.get_daily_connect() as con:
cur = con.cursor()
rows = global_test_context.get_start_rows(cur, current_day)
# 没找到可转债, 或者太贵了, 不满足轮动条件, 不买, 进入下一个交易日
if len(rows) == 0 or \
(global_test_context.need_check_double_low and
is_too_expensive(rows, max_double_low=global_test_context.max_double_low)):
# 取下一个交易日
next_day = get_next_day(current_day, cur)
if next_day is not None:
return start_roll(next_day, total_money, old_group)
else:
# 已经到结束日了
print('not found next day by ' + str(current_day))
return None, current_day, None
# 等权分配资金
remain_money = do_push_bond(group,
rows,
total_money,
old_group=old_group)
return group, current_day, remain_money
def insert_db(rows):
err_row = None
try:
i = 0
with db_utils.get_daily_connect() as con:
cur = con.cursor()
for row in rows:
err_row = row
enforce_dt = row.get('enforce_dt')
if enforce_dt is None:
print("not enforce_dt value." + str(row))
continue
result = cur.execute(
"""insert into cb_enforce (bond_id, bond_name, enforce_dt) VALUES (:bond_id, :bond_name, :enforce_dt)""",
{
'enforce_dt': datetime.strptime(
enforce_dt, '%Y-%m-%d'),
'bond_id': row.get('bond_code'),
'bond_name': row.get('cb_name_id')
})
if result.rowcount != 1:
print('insert cb_enforce is failure. row:' + str(row))
else:
i += 1
print("insert complete. count:" + str(i))
except Exception as e:
print("db操作出现异常. err_row" + str(err_row), e)
raise e
finally:
# 暂停3s再执行， 避免被网站屏蔽掉
time.sleep(3)
def query_database_view():
table_html = ''
sql_code = ''
table_height_style = ''
if len(request.form) > 0:
sql_code = request.form['sql_code']
if sql_code is None or sql_code.strip(' ') == '':
raise Exception('SQL不能为空')
if not sql_code.lower().strip().startswith('select'):
raise Exception("仅允许select操作")
db_type = request.form['db_type']
cur = None
if db_type == 'daily':
with get_daily_connect() as con:
cur = con.cursor()
cur.execute(sql_code)
else:
with get_connect() as con:
cur = con.cursor()
cur.execute(sql_code)
table = from_db_cursor(cur)
if table.rowcount > 10:
table_height_style = """style="height:500px" """
table_html = utils.table_html_utils.build_table_html(table)
return render_template("query_database.html",
table_html=table_html,
sql_code=sql_code,
table_height_style=table_height_style)
def get_back_test_data(name):
with db_utils.get_daily_connect() as con:
cur = con.cursor()
cur.execute("select data, desc from cb_backtest_data where name=:name",
{"name": name})
one = cur.fetchone()
main_content = one[0]
ref = one[1]
return ('' if ref is None else "策略参考: " + ref) + main_content
def do_save_back_test_result(name, data):
with db_utils.get_daily_connect() as con:
cur = con.cursor()
result = cur.execute(
"update cb_backtest_data set data=:data where name=:name", {
"name": name,
"data": data
})
if result.rowcount != 1:
print('update cb_backtest_data is failure. row:' + name)
else:
print('update cb_backtest_data is successful. row:' + name)
def save_cb_daily_data():
# 删除整个db
if os.path.exists(db_daily_file_path):
os.unlink(db_daily_file_path)
# 获取文件(字符串?)
file = request.files['file']
s = file.read().decode('utf-8')
# 灌入上传的数据
with get_daily_connect() as con:
con.executescript(s)
return 'OK'
def download_cb_daily_data():
today = datetime.now()
ymd = today.strftime('%Y-%m-%d')
file_name = 'dump/cb_daily_' + ymd + '.sql'
with open(file_name, 'w') as f:
with get_daily_connect() as con:
for line in con.iterdump():
f.write('%s\n' % line)
# 需要知道2个参数, 第1个参数是本地目录的path, 第2个参数是文件名(带扩展名)
directory = os.getcwd() # 假设在当前目录
return send_from_directory(directory, file_name, as_attachment=True)
def get_next_day(current, cur=None):
if cur is None:
cur = db_utils.get_daily_connect().cursor()
cur.execute(
"""
SELECT min(DISTINCT (last_chg_dt))
from cb_history
where last_chg_dt > :current and price is not NULL
order by last_chg_dt
""", {"current": current})
next_day = cur.fetchone()[0]
if next_day is not None:
return datetime.datetime.strptime(next_day, '%Y-%m-%d %H:%M:%S')
return None
def execute_sql():
sql_code = request.form['sql_code']
db_type = request.form['db_type']
if sql_code is None or sql_code.strip(' ') == '':
raise Exception('SQL不能为空')
if not sql_code.lower().strip().startswith('update') and not sql_code.lower().strip().startswith('insert'):
raise Exception("仅允许update/insert操作")
if db_type == 'daily':
with get_daily_connect() as con:
con.executescript(sql_code)
else:
with get_connect() as con:
con.executescript(sql_code)
return 'OK'
def get_hold_row_total_money(group, current_day, previous_day, test_result):
if len(group) == 0:
return test_result['rows'][previous_day]['total_money']
params = {
"current": current_day,
}
keys = sorted(group.keys())
ids = parse_bond_ids_params(keys, params)
with db_utils.get_daily_connect() as con:
cur = con.cursor()
cur.execute(
"""
select a.bond_id,
a.bond_nm,
a.price,
a.premium_rt
from cb_history a
where a.bond_id in (""" + ids + """)
and a.last_chg_dt = :current
""", params)
new_rows = cur.fetchall()
# 异常数据, 跳过
if new_rows is None:
raise Exception('not get new rows. params:' + str(params))
# 有可能那天停牌了, 导致两边数据不一致
if len(new_rows) != len(group):
print("data is conflict. current_day:" + str(current_day) +
", group size:" + str(len(group)) + ", new_row size:" +
str(len(new_rows)))
total_money = test_result.get('remain_money')
id_rows = {}
for row in new_rows:
bond_id = row[0]
id_rows[bond_id] = row
for bond_id, bond in group.items():
row = id_rows.get(bond_id)
price = bond['price'] if row is None else row[2]
total_money += round(price * bond.get("amount"), 2)
return round(total_money, 2)
def do_trade(current_day, group, previous_day, test_result):
# 用来和七天前的价格进行比较, 如果涨幅过大(30%), 提前止盈
params = {
"current": current_day,
"pre_day": global_test_context.pre_day,
'max_price': global_test_context.max_price,
}
keys = sorted(group.keys())
ids = parse_bond_ids_params(keys, params)
with db_utils.get_daily_connect() as con:
cur = con.cursor()
new_rows = get_hold_rows(cur, ids, params)
# 异常数据, 跳过
if new_rows is None:
print('not get new rows. params:' + str(params))
return False, previous_day, get_pre_total_money(
previous_day, test_result)
# 有可能那天停牌了, 导致两边数据不一致
if len(new_rows) != len(group):
print("data is conflict. current_day:" + str(current_day) +
", group size:" + str(len(group)) + ", new_row size:" +
str(len(new_rows)))
# 为了计算盈亏, 主要更新价格
update_bond(group, new_rows)
total_money = get_total_money(group, test_result)
calc_test_result(test_result, total_money, current_day, previous_day)
# 太贵了, 清仓(并没有实际的卖出操作, 只是总金额不再随价格变动)
if global_test_context.need_check_double_low and \
is_too_expensive(new_rows, group, global_test_context.max_double_low, current_day, test_result,
need_roll_row=global_test_context.one_strategy_with_one_scenario):
print("clean all bonds at " + str(current_day) + " bonds:" +
str(new_rows))
return True, current_day
# 根据条件对转债进行轮换(不影响当天收益)
break_roll = exchange_bond(cur, current_day, group, ids, params,
new_rows, test_result)
return break_roll, current_day