195 lines
7.4 KiB
Python
195 lines
7.4 KiB
Python
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)
|