def getStrategyLogsByStrategyId(creator, strategy_id): strategyLogList = [] # SQL 查询语句 sql = " SELECT strategy_log_id," \ " strategy_id," \ " start_date," \ " end_date," \ " init_balance," \ " coin_category," \ " creator," \ " create_time," \ " execution_result," \ " (select strategy_name from strategy where strategy.strategy_id = strategy_log.strategy_id) strategy_name," \ " final_margin ," \ " benchmark,max_drawdown " \ " FROM strategy_log" \ " where creator=%s and strategy_id =%s and del_flag = 0 " \ " order by create_time desc" # 执行SQL语句 param = (creator, strategy_id) # print(sql) results = db.fetch_db_with_param(sql, param) for row in results: # 打印结果 pro = StrategyLog(row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11], row[12]) # print("row[0]:"+str(row[0])+"|row[a]:"+row[a]+"|row[b]:"+str(row[b])+"|row[3]:"+str(row[3])+"|row[4]:"+row[4]+"|row[5]:"+str(row[5])+"|row[6]:"+row[6]+"|row[7]:"+str(row[7])) strategyLogList.append(pro) return strategyLogList
def getALLStrategy(creator): strategyList = [] # SQL 查询语句 sql = "SELECT strategy_id," \ " strategy_name," \ " description," \ " create_time," \ " update_time," \ " loading_times," \ " creator," \ " script_url," \ " peroid," \ " method_name," \ " coin_category, " \ " init_balance," \ " start_time," \ " end_time," \ " (select create_time from strategy_log sl where sl.strategy_id=s.strategy_id order by create_time desc limit 1) last_run," \ " (select count(1) from strategy_log sl where sl.strategy_id=s.strategy_id) run_times," \ " duration,benchmark,drawdown,status FROM strategy s" \ " where creator=%s and del_flag = 0" # 执行SQL语句 # 获取所有记录列表 results = db.fetch_db_with_param(sql, creator) for row in results: # 打印结果 strategy = Strategy(row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11], row[12], row[13], row[14], row[15], row[16], row[17], row[18], row[19]) strategyList.append(strategy) return strategyList
def getStrategyDetail(creator, strategy_id): # SQL 查询语句 sql = "SELECT strategy_id," \ " strategy_name," \ " description," \ " create_time," \ " update_time," \ " loading_times," \ " creator," \ " script_url," \ " peroid," \ " method_name," \ " coin_category, " \ " init_balance," \ " start_time," \ " end_time," \ " (select create_time from strategy_log sl where sl.strategy_id=s.strategy_id order by create_time desc limit 1) last_run," \ " (select count(1) from strategy_log sl where sl.strategy_id=s.strategy_id) run_times," \ " duration,benchmark,drawdown,status FROM strategy s" \ " where creator=%s and strategy_id = %s " # 执行SQL语句 param = (creator, strategy_id) rows = db.fetch_db_with_param(sql, param) # 获取所有记录列表 row = rows[0] strategy = Strategy(row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11], row[12], row[13], row[14], row[15], row[16], row[17], row[18], row[19]) return strategy
def getStrategy(userId, strategyId): strategyList = [] # SQL 查询语句 sql = " SELECT strategy_id," \ " strategy_name," \ " description," \ " create_time," \ " update_time," \ " loading_times," \ " creator," \ " script_url," \ " peroid," \ " method_name," \ " coin_category, " \ " init_balance," \ " start_time," \ " end_time," \ " (select create_time from strategy_log sl where sl.strategy_id=s.strategy_id order by create_time desc limit 1) last_run," \ " (select count(1) from strategy_log sl where sl.strategy_id=s.strategy_id) run_times," \ " duration,benchmark,drawdown,status" \ " FROM strategy s where creator=%s and strategy_id=%s and del_flag = 0 " # 执行SQL语句 param = (userId, strategyId) rows = db.fetch_db_with_param(sql, param) # 获取所有记录列表 # 打印结果 row = rows[0] strategy = Strategy(row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11], row[12], row[13], row[14], row[15], row[16], row[17], row[18], row[19]) # print(strategy) strategy.set_strategy_conf_items(getStrategyConfItem(row[0])) # strategyConf.printAll() return strategy
def checkStrategyName(strategy_name, creator): strategyConfList = [] # SQL 查询语句 sql = " select * from strategy where strategy_name=%s and creator=%s" param = (strategy_name, creator) results = db.fetch_db_with_param(sql, param) if (len(results) > 0): return True else: return False
def checkPreviousStrategyName(strategy_id, creator, strategy_name): # sql 查询语句 sql = "select strategy_name from strategy where strategy_id=%s and creator=%s" params = (strategy_id, creator) while True: result = db.fetch_db_with_param(sql, params) if result == None: break if result[0][0] == strategy_name: return True else: return False
def get_strategy_profit_list(strategy_log_id): strategy_profit_list = [] sql = " SELECT current_total_margin_rate FROM strategy_account where strategy_log_id = %s " \ " order by strategy_account_id asc " params = (strategy_log_id) results = db.fetch_db_with_param(sql, params) for item in results: strategy_profit_list.append(item[0]) return strategy_profit_list
def get_all_strategy_name(creator): strategy_name_list = [] sql = " SELECT strategy_name FROM strategy where del_flag = 0 and creator = %s " \ " order by strategy_name asc " params = (creator) results = db.fetch_db_with_param(sql, params) for item in results: strategy_name_list.append(item[0]) return strategy_name_list
def mob_my_strategy_list(creator): my_strategy_list = [] sql0 = "CREATE OR REPLACE VIEW MY_STRATEGY_LIST AS " \ "SELECT s.strategy_id, s.strategy_name, sl.create_time from strategy s " \ "inner join strategy_log sl on sl.strategy_id = s.strategy_id " \ "where s.creator = %s " \ "order by sl.create_time desc; " sql1 = "select strategy_id,strategy_name,max(create_time),count(create_time)" \ " from MY_STRATEGY_LIST " \ "group by strategy_id;" sql2 = "DROP VIEW MY_STRATEGY_LIST" params = (creator) db.fetch_db_with_param(sql0, params) results = db.fetch_db(sql1) db.update_db(sql2) for item in results: strategy = MyStrategy(strategy_id=item[0], strategy_name=item[1], create_time=item[2], run_times=item[3]) my_strategy_list.append(strategy) return my_strategy_list
def getUserByPhone(phone): UserList = [] # SQL 查询语句 sql = "SELECT u.user_id,u.phone,u.password,u.nick_name,u.open_id,u.age,u.gender," \ "u.avator,u.levels," \ "style,experience FROM user_info u WHERE u.phone = %s" # 执行SQL语句 param = (phone) results = db.fetch_db_with_param(sql, param) for row in results: # 打印结果 item = UserByPhone(row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10]) UserList.append(item) return UserList
def getStrategyConfItem(strategy_id): strategyConfItemList = [] # SQL 查询语句 sql = " SELECT strategy_conf_item_id,strategy_id,index_label,formular,price,direction" \ " FROM strategy_conf_item where strategy_id=%s" # 执行SQL语句 param = (strategy_id) results = db.fetch_db_with_param(sql, param) # 获取所有记录列表 for row in results: # 打印结果 item = StrategyConfItem(row[0], row[1], row[2], row[3], row[4], row[5]) strategyConfItemList.append(item) return strategyConfItemList
def getUser(user_id): UserList = [] # SQL 查询语句 sql = "SELECT u.user_id,u.phone,u.password,u.nick_name,u.open_id,u.age,u.gender," \ "u.avator,u.levels," \ "(SELECT COUNT(s.strategy_id) FROM strategy s WHERE s.creator = u.user_id and s.del_flag = 0) strategy_amount," \ "(SELECT COUNT(sl.strategy_log_id) FROM strategy_log sl WHERE sl.creator = u.user_id and sl.del_flag = 0) history_amount," \ "style,experience FROM user_info u WHERE u.user_id = %s" # 执行SQL语句 param = (user_id) results = db.fetch_db_with_param(sql, param) for row in results: # 打印结果 item = User(row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11], row[12]) UserList.append(item) return UserList
def mob_get_strategy_log_list(strategy_id, creator): strategy_logs = [] sql = "select sl.create_time,s.strategy_name, " \ "sl.final_margin, sl.strategy_log_id," \ "sl.benchmark,sl.start_date,sl.end_date,sl.max_drawdown " \ "from strategy_log sl " \ "inner join strategy s on sl.strategy_id = s.strategy_id " \ "where sl.strategy_id = %s and s.creator = %s " \ "order by sl.create_time desc ;" params = (strategy_id, creator) results = db.fetch_db_with_param(sql, params) for item in results: strategy_log = StrategyLog(strategy_log_id=item[3], create_time=item[0], strategy_name=item[1], final_margin=item[2], benchmark=item[4], strategy_id=strategy_id, start_date=item[5], end_date=item[6], creator=creator, coin_category=None, execution_result=None, init_balance=None, max_drawdown=item[7]) strategy_logs.append(strategy_log) return strategy_logs
def mob_trade_history(strategy_log_id): trade_historys = [] sql = "select st.t, st.flag, " \ "st.pre_position, " \ "st.post_position, " \ "st.pre_balance," \ "st.post_balance," \ "st.flag from strategy_log sl " \ "inner join strategy s on sl.strategy_id = s.strategy_id " \ "inner join strategy_account sa on sl.strategy_log_id = sa.strategy_log_id " \ "inner join strategy_transaction st on sa.strategy_account_id = st.strategy_account_id " \ "where sl.strategy_log_id = %s;" params = (strategy_log_id) results = db.fetch_db_with_param(sql, params) for item in results: trade_history = TradeHistory(item[0], item[1], item[2], item[3], item[4], item[5], item[6]) trade_historys.append(trade_history) return trade_historys