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