import sys import pymysql import json from datetime import date, timedelta, datetime import time db_info = {'host': '192.168.0.2', 'port': 3306, 'user': 'javamon', 'passwd': '@Wtkdwns117424', 'db': 'oh_my_bot_admin', 'charset': 'utf8', 'max_allowed_packet': '67108864'} class DB: def execute(self, sql=None, data=None): try: res = None if not sql is None: connection = pymysql.connect(host=db_info['host'], port=db_info['port'], user=db_info['user'], passwd=db_info['passwd'], db=db_info['db'], charset=db_info['charset'], max_allowed_packet=db_info['max_allowed_packet'], init_command="SET SESSION time_zone='+09:00'", connect_timeout=60) try: with connection.cursor(pymysql.cursors.DictCursor) as cursor: cursor.execute(sql, data) res = cursor.fetchall() connection.commit() finally: connection.close() return res except Exception as e: time.sleep(2) return self.execute(sql, data) def select_only_one_row(self, sql): return self.execute(sql)[0] def select_exchage_id_by_name(self, name): sql = "SELECT `f_id`, `id` FROM oh_my_bot_admin.exchange WHERE `name`= '%s';" % name return self.select_only_one_row(sql) def select_item_id_by_ids_name(self, f_i, e_i, name): sql = "SELECT `id` FROM oh_my_bot_admin.item " \ "WHERE `f_id`= '%s' AND `e_id`= '%s' AND `name`='%s';" % (f_i, e_i, name) return self.select_only_one_row(sql)['id'] def select_top_date(self, item): t_name = str(item).replace('/', '_') profit_sql = "SELECT MAX(profit_rate) FROM oh_my_bot_admin.simulation " \ "WHERE `t_table_name` = '%s';" % t_name win_rate_sql = "SELECT MAX(win_rate) FROM oh_my_bot_admin.simulation " \ "WHERE `t_table_name` = '%s';" % t_name profit = self.execute(profit_sql) win_rate = self.execute(win_rate_sql) return { 'profit_rate': profit[0]['MAX(profit_rate)'], 'win_rate': win_rate[0]['MAX(win_rate)'], } def isNaN(self, num): return num != num def insert_simul_result(self, result): ''' `f_i` INT NOT NULL COMMENT 'finance_id', `e_i` INT NOT NULL COMMENT 'exchage_id', `item_id` INT NOT NULL, `time_type` VARCHAR(100) NOT NULL COMMENT 'item data time type', `t_table_name` VARCHAR(100) NOT NULL COMMENT 'Target item table name', `profit_rate` FLOAT NOT NULL COMMENT 'Return(Profit) rate', `win_rate` FLOAT NOT NULL COMMENT 'Wins rate', `trades` INT NOT NULL, `stop_loss` FLOAT NOT NULL, `stop_profit` FLOAT NOT NULL, `used_patterns` VARCHAR(255) NOT NULL COMMENT 'used indicator and patterns', `file_name` VARCHAR(255) NULL COMMENT 'simulation result file name', `start_date` DATETIME NOT NULL, `end_date` DATETIME NOT NULL, `duration_days` INT NOT NULL COMMENT 'simulation period days', `trade_type` NULL DEFAULT 'single' COMMENT 'trade type(doubly trade)', ''' i_info = result['item'].split('_') ids = self.select_exchage_id_by_name(i_info[1]) item_id = self.select_item_id_by_ids_name(ids['f_id'], ids['id'], i_info[2]) s_d, days = self.get_start_date_on_hour(result['period_cycle']) if self.isNaN(result['results']['Return [%]']) or self.isNaN(result['results']['Win Rate [%]']): return sql = ''' INSERT INTO `oh_my_bot_admin`.`simulation` (`f_i`, `e_i`, `item_id`, `time_type`, `t_table_name`, `profit_rate`, `win_rate`, `trades`, `stop_loss`, `stop_profit`, `used_patterns`, `file_name`, `start_date`, `end_date`, `duration_days`, `trade_type`) VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s'); ''' % (ids['f_id'], ids['id'], item_id, result['time_type'], result['item'], result['results']['Return [%]'], result['results']['Win Rate [%]'], result['results']['# Trades'], result['stop_loss'], result['stop_profit'], str(result['use_patterns']), result['filename'], result['results']['Start'], result['results']['End'], days, str(result['trade_type']), ) self.execute(sql) def update_simul_init_value(self, job): sql = "UPDATE `oh_my_bot_admin`.`cron` SET `init_simul` = 'Y' WHERE (`job` = '%s');" % job self.execute(sql) def get_cron_list(self): # sql = "SELECT `job`, `period_cycle`, `time_unit` FROM oh_my_bot_admin.cron WHERE `type` = 'price' AND `init_simul` = 'N';" sql = "SELECT * FROM oh_my_bot_admin.cron WHERE `type` = 'price' AND `init_simul` = 'N' ORDER BY id ASC;" # sql = "SELECT * FROM oh_my_bot_admin.cron WHERE `type` = 'price';" return self.execute(sql) def get_price_data_from_item_table(self, job_name, period): tb_name = self.job_name_convert_to_tb_name(job_name) start_date, days = self.get_start_date_on_hour(period) date_arr = str(start_date).split('-') start_date = datetime(int(date_arr[0]), int(date_arr[1]), int(date_arr[2]), 1, 0, 0) sql = "SELECT `open`, `high`, `low`, `close`, `volume`, `date` FROM " \ "oh_my_bot_admin.%s WHERE `date` >= '%s'" % (tb_name, start_date) return self.execute(sql) def get_start_date_on_hour(self, period='2_M'): # for period in ['6_M', '3_M', '1_Y', '2_Y', '2_W', '3_W']: d = str(period).split('_') int2type = { "W": 8, "M": 32, "Y": 366, } days = int(d[0]) * int(int2type[d[1]]) return date.today() - timedelta(days=days), days def job_name_convert_to_tb_name(self, job_name): b = '_' _ = str(job_name).split('/') _f = b.join([_[0], _[1]]).lower() _e = b.join([_[2], _[3]]).upper() return b.join([_f, _e]).replace('-', '_') def is_bot_by_exchange_name(self, e_name): sql = "SELECT id FROM oh_my_bot_admin.exchange WHERE `name` = '%s';" % e_name res = self.execute(sql) sql = "SELECT id FROM oh_my_bot_admin.bot WHERE `e_i`='%s' AND `status` = 'Y';" % res[0]['id'] res = self.execute(sql) if res is (): return False return True def is_simulated_item_per_hour(self, job, t_time): sql = "SELECT * FROM oh_my_bot_admin.sumul_item_for_time " \ "WHERE `job`='%s' and `time_unit` = '%s';" % (job, t_time) res = self.execute(sql) if res is (): return False return True def insert_item_from_simul_item_per_hour(self, job, t_time): sql = "INSERT INTO `oh_my_bot_admin`.`sumul_item_for_time` (`job`, `time_unit`) VALUES ('%s', '%s');" % ( job, t_time) return self.execute(sql) def remove_item_from_simul_item_per_hour(self, job): sql = "DELETE FROM `oh_my_bot_admin`.`sumul_item_for_time` WHERE (`job` = '%s');" % job return self.execute(sql)