# -*- coding: utf-8 -*- import sys # for test import pymysql import json import time from sqlalchemy import create_engine import warnings # 기본 테이블 추후 필요시 머신러닝 관련 테이블 추가 default_tables = ['base', 'finance', 'exchange', 'score', 'bot', 'cron', 'cron_log', 'item', 'simulation', 'trade', 'allow_item', 'user', 'sumul_item_for_time'] # 기본테이블 쿼리 작성부 _make_query_default_table_for_type() ''' 완료 작업 - 디비 클래스 재구성 => 단발성 요청(커넥트 - 디스커넥트) 방식으로 변경 + 인터페이스를 통해 싱글톤 가능하도록 부수적인 기능 구현 => 타임아웃 이슈, 버퍼 이슈, 배치 프로세스(크론) 실행 시 커넥션 부조화 발생 - 변경 된 DB 클래스 디버깅 - 잡 스케쥴링 테스트 진행중 작업 - 기존 데이터 수집(거래소별) -> 거래소 클래스에 함수 추가 및 테이블 생성 시 자동 삽입 기능 로직 추가 - 시간 설정값 보완 => 디비에 삽입해서 사용하도록 수정하기 - 종목 추가하는 부분을 배치로(일단위 배치로 구현)하여 자동으로 종목 추가, 삭제 구현 예정 작업 - exchange 테이블 필드 추가 : api_url, f_id(finance), 1일 거래량, 1일 거래금액(종목 추가/삭제 시) => 업빗 기준 5000백만 이상 거래금액 - 거래소별 종목테이블 생성 : 상폐 또는 거래랑이 낮을 시 거래종목에서 제외(분기 또는 반기 후 거래량이 급감한 종목 테이블 삭제) ''' warnings.simplefilter("ignore") unique_field = {'upbit': 'candle_date_time_utc'} db_info = {'host':'192.168.0.2', 'port': 3306, 'user': 'javamon', 'passwd': '@Wtkdwns117424', 'db': 'oh_my_bot_admin', 'charset': 'utf8', 'max_allowed_packet': '67108864'} default_data_tables = ['allow_item', 'user'] class DB: engine = None db = None # def __init__(self): # self.engine = self.get_db_engine() # self.db = self.get_db_instance() # def get_db_engine(self): # return create_engine("mysql://%s:%s@%s/%s" % ( # db_info['user'], # db_info['passwd'], # db_info['host'], # db_info['db'], # )) # 싱글톤 요청 def get_db_instance(self): db = 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'") return db.cursor() # 단발성 요청 def execute(self, sql = None, data = None): res = None try: 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: print(e) time.sleep(3) return self.execute(sql, data) def insert_finance_to_base (self, finance, exchange): # 금융 종목 추가 return self.execute( ''' INSERT INTO `oh_my_bot_admin`.`base` (`finance_name`, `exchange_name`) VALUES ('%s', '%s'); ''' % (finance, exchange) ) def select_base_table(self): return self.execute("SELECT `finance_name`, `exchange_name`, `trade_type` FROM base;") def create_default_tables(self): # need_tables = self._is_default_tables() for tb in self._is_default_tables() : # 기본 테이블들 생성 tb_create_sql = self._make_query_default_table_for_type(tb) self.execute(tb_create_sql) def insert_default_tables(self): self._insert_default_allow_item() def _insert_default_allow_item(self): for tb in default_data_tables: i_null = self._is_void_table_data(tb) if i_null: self._insert_default_data_to_table(tb) def _insert_default_data_to_table(self, table_name): sql = '' if table_name == 'allow_item': sql = ''' REPLACE INTO `oh_my_bot_admin`.`allow_item` (`item_code`) VALUES ('%s'), ('%s'); ''' % ('BTC', 'ETH') elif table_name == 'user': key_data = { 'bithumb': { 'access': 'f557cba1cbc531b0c51e699266f9868f', 'secret': '8078dcf58f344afbd3f2cf0df60db1b3', }, 'upbit': { 'access': 'IdO6IXpgC07 XB2KWoID37jxvFXgpLMkxZRTxHViI', 'secret': 'QteNfsNZly1kZ1t3MGAc9bOxMDiIouozuQCZVVJI', }, 'binance': { 'access': 'DPob3MlV51nb55D6OovjKTNRiyoiMWihX2phcunUNxI73Z7gSyo2ALX87dxcmuXB', 'secret': 'qgd5YHf4TiWvD8KjOL1qoPz9QX354mYMIoQ6FBt5VCv1tswQq3X6eGaFFrHZ7a7a', }, } sql = ''' REPLACE INTO `oh_my_bot_admin`.`user` (`email`, `password`, `grade`, `auth_info`) VALUES ('javamon1174@gmail.com', '1111', '0', '%s'); ''' % json.dumps(key_data) self.execute(sql) def _is_void_table_data(self, table_name): sql = "SELECT `id` FROM oh_my_bot_admin.%s LIMIT 1;" % table_name res = self.execute(sql) if res is (): return True else: return False def select_allow_items_all(self): sql = "SELECT `item_code` FROM oh_my_bot_admin.allow_item;" return self.execute(sql) def _get_one_row_from_rows(self, tuple): if tuple is (): return () return tuple[0] def _is_table(self, table_name): is_table = self._get_one_row_from_rows(self.execute("check table `%s`;" % table_name)) return 'OK' in is_table['Msg_text'] def is_table(self, table_name): return self._is_table(table_name) # 기본 테이블 존재 유뮤 체크 def _is_default_tables(self): need_tables = [] for tb in default_tables : if not self._is_table(tb) : need_tables.append(tb) return need_tables # 기본 테이블 생성을 위한 쿼리 리턴 def _make_query_default_table_for_type(self, table_name): tb_sql = '' if table_name == 'base': tb_sql = ''' CREATE TABLE `%s` ( `id` INT AUTO_INCREMENT, `finance_name` VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, `exchange_name` VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, `trade_type` VARCHAR(100) NULL DEFAULT 'single' COMMENT '단반향/양방향 트레이딩 여부', `added_date` DATETIME NULL DEFAULT NOW(), PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; ''' % table_name elif table_name == 'finance': tb_sql = ''' CREATE TABLE `%s` ( `id` int(100) AUTO_INCREMENT, `name` varchar(100) CHARACTER SET utf8mb4 NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; ''' % table_name elif table_name == 'exchange' : tb_sql = ''' CREATE TABLE `%s` ( `id` int(100) AUTO_INCREMENT, `f_id` int(100) NOT NULL, `name` varchar(100) CHARACTER SET utf8mb4 NOT NULL, `standard_volume` varchar(255) CHARACTER SET utf8mb4 NOT NULL COMMENT '기준 거래 금액', `api_url` varchar(255) CHARACTER SET utf8mb4 NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; ''' % table_name elif table_name == 'score': tb_sql = ''' CREATE TABLE `oh_my_bot_admin`.`%s` ( `id` INT AUTO_INCREMENT, `i_id` INT NOT NULL COMMENT 'item id', `strategy_score` INT NOT NULL, `model_score` INT NOT NULL, `total` INT NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; ''' % table_name elif table_name == 'bot': tb_sql = ''' CREATE TABLE `oh_my_bot_admin`.`%s` ( `id` INT AUTO_INCREMENT, `f_i` INT NOT NULL COMMENT '금융 아이디', `e_i` INT NOT NULL COMMENT '거래소 아이디', `user_id` INT NULL DEFAULT '1' COMMENT '유저 아이디', `status` VARCHAR(10) NULL DEFAULT 'Y' COMMENT '봇 가동 상태(Y-RUN, N-REMOVED, S-STOP)', `mode` VARCHAR(50) NULL DEFAULT 'test' COMMENT '봇 모드 - test or service', `position` VARCHAR(50) NULL DEFAULT 'None' COMMENT '봇의 현재 포지션', `position_price` FLOAT NULL DEFAULT 0 COMMENT '봇의 현재 포지션 가격', `amount` FLOAT NULL DEFAULT 0 COMMENT '시작 총 보유액', `last_amount` FLOAT NULL DEFAULT 0 COMMENT '현재 총 보유액', `target` VARCHAR(255) NOT NULL COMMENT '타겟 아이템', `time_unit` VARCHAR(100) NOT NULL COMMENT '시간 종류', `duration_days` INT NOT NULL COMMENT '시뮬레이팅 일수', `p_profit` FLOAT NOT NULL COMMENT '예상 수익(Profit Projection)', `profit` FLOAT NULL DEFAULT 0 COMMENT '실제 수익', `last_profit` FLOAT NULL DEFAULT 0 COMMENT '마지막 거래 수익률', `p_winrate` FLOAT NOT NULL COMMENT '예상 승률', `winrate` FLOAT NULL DEFAULT 0 COMMENT '실제 승률', `stop_loss` FLOAT NOT NULL COMMENT '스탑 로스', `stop_profit` FLOAT NOT NULL COMMENT '스탑 프로핏', `trade_cnt` INT NULL DEFAULT 0 COMMENT '거래 수', `win_cnt` INT NULL DEFAULT 0 COMMENT '승 수', `lose_cnt` INT NULL DEFAULT 0 COMMENT '패배 수', `tb_name` VARCHAR(255) NOT NULL COMMENT '타겟 테이블 이름', `strategy` VARCHAR(255) NOT NULL COMMENT '매매 전략', `trade_type` VARCHAR(100) NOT NULL COMMENT '단반향/양방향 트레이딩 여부', `leverage` INT NULL DEFAULT 1 COMMENT '마진(leverage)', `last_signal` DATETIME NULL DEFAULT NOW() COMMENT '추가 일자', `update_date` DATETIME NULL DEFAULT NOW() COMMENT '갱신 일자', `added_date` DATETIME NULL DEFAULT NOW() COMMENT '추가 일자', PRIMARY KEY (`id`), UNIQUE INDEX `target_UNIQUE` (`target`)) COMMENT = 'trading bots' ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; ''' % table_name elif table_name == 'cron': tb_sql = ''' CREATE TABLE `oh_my_bot_admin`.`%s` ( `id` INT NOT NULL AUTO_INCREMENT, `type` VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL COMMENT '작업 종류', `target` VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL COMMENT '금융/거래소/항목 이름', `time_unit` VARCHAR(20) CHARACTER SET utf8mb4 NOT NULL DEFAULT 'min' COMMENT '시간 단위', `repeat_cycle` VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL COMMENT '반복 주기', `job` VARCHAR(255) CHARACTER SET utf8mb4 NOT NULL COMMENT 'Job => Function name', `period_cycle` VARCHAR(255) CHARACTER SET utf8mb4 NULL DEFAULT '2_M' COMMENT '시뮬레이팅 기간 주기', `init_simul` VARCHAR(255) CHARACTER SET utf8mb4 NULL DEFAULT 'N' COMMENT '초기 시뮬레이팅 여부', `trade_type` VARCHAR(100) NULL DEFAULT 'single' COMMENT '단반향/양방향 트레이딩 여부', `etc` VARCHAR(255) CHARACTER SET utf8mb4 NULL DEFAULT 'NULL' COMMENT '잡 호출 함수명', PRIMARY KEY (`id`), UNIQUE INDEX `job_UNIQUE` (`job` ASC)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; ''' % table_name elif table_name == 'cron_log': tb_sql = ''' CREATE TABLE `oh_my_bot_admin`.`%s` ( `id` INT NOT NULL AUTO_INCREMENT, `c_id` INT NOT NULL, `job` VARCHAR(255) CHARACTER SET utf8mb4 NOT NULL, `reason` VARCHAR(255) CHARACTER SET utf8mb4 NULL DEFAULT 'Unknown' COMMENT '실패 사유', `error_msg` VARCHAR(255) CHARACTER SET utf8mb4 NOT NULL, `treat` VARCHAR(255) CHARACTER SET utf8mb4 NULL DEFAULT 'Nothing' COMMENT '후처리', PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; ''' % table_name elif table_name == 'item': tb_sql = ''' CREATE TABLE `oh_my_bot_admin`.`%s` ( `id` INT NOT NULL AUTO_INCREMENT, `name` VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL COMMENT 'item name', `f_id` INT NOT NULL COMMENT 'Finance id', `e_id` INT NOT NULL COMMENT 'Exchange id', `status` VARCHAR(100) CHARACTER SET utf8mb4 NULL DEFAULT 'ready', `acc_trade_price_24h` BIGINT DEFAULT 0 NULL COMMENT '24시간 누적 거래 금액', `acc_trade_volume_24h` BIGINT DEFAULT 0 NULL COMMENT '24시간 누적 거래량', `i_res` INT NULL DEFAULT 0 COMMENT 'Indicate Result', `m_res` INT NULL DEFAULT 0 COMMENT 'Mushine Running Result', `t_res` INT NULL DEFAULT 0 COMMENT 'Total Result(수익성 판단)', PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; ''' % table_name elif table_name == 'simulation': tb_sql = ''' CREATE TABLE `oh_my_bot_admin`.`%s` ( `id` INT AUTO_INCREMENT, `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` VARCHAR(100) NULL DEFAULT 'single' COMMENT '단반향/양방향 트레이딩 여부', `added_date` DATETIME NULL DEFAULT NOW(), PRIMARY KEY (`id`)) COMMENT = 'simulation result table'; ''' % table_name elif table_name == 'trade': # 해당 테이블 사용 안함 tb_sql = ''' CREATE TABLE `oh_my_bot_admin`.`%s` ( `id` INT AUTO_INCREMENT, `f_i` INT NOT NULL COMMENT '금융 아이디', `e_i` INT NOT NULL COMMENT '거래소 아이디', `target` VARCHAR(255) NOT NULL COMMENT '타겟 종목', `position` VARCHAR(30) NOT NULL COMMENT '매매 포지션', `price` FLOAT NOT NULL COMMENT '진입가', `target_price_1` FLOAT NULL COMMENT '1차 목표가', `target_price_2` FLOAT NULL COMMENT '2차 목표가', `strategy` VARCHAR(255) NOT NULL COMMENT '매매전략(JSON)', `added_date` DATETIME NULL DEFAULT NOW() COMMENT '등록 일자', PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; ''' % table_name elif table_name == 'allow_item': tb_sql = ''' CREATE TABLE `oh_my_bot_admin`.`%s` ( `id` INT AUTO_INCREMENT, `item_code` VARCHAR(100) NOT NULL COMMENT '아이템 코드', PRIMARY KEY (`id`), UNIQUE INDEX `item_code_UNIQUE` (`item_code` ASC)) COMMENT = '허용 아이템 목록 테이블'; ''' % table_name elif table_name == 'user': tb_sql = ''' CREATE TABLE `oh_my_bot_admin`.`%s` ( `id` INT AUTO_INCREMENT, `email` VARCHAR(100) NOT NULL COMMENT '이메일 주소', `password` VARCHAR(255) NOT NULL COMMENT '비밀번호', `grade` INT NULL DEFAULT 1 COMMENT '등급', `auth_info` LONGTEXT NULL COMMENT '유저 거래소별 키 정보', `added_date` DATETIME NULL DEFAULT NOW(), PRIMARY KEY (`id`)) COMMENT = '유저 테이블'; ''' % table_name elif table_name == 'sumul_item_for_time': tb_sql = ''' CREATE TABLE `oh_my_bot_admin`.`%s` ( `id` INT NOT NULL AUTO_INCREMENT, `job` VARCHAR(255) NOT NULL, `time_unit` VARCHAR(255) NOT NULL, `added_date` DATETIME NULL DEFAULT NOW(), PRIMARY KEY (`id`)) COMMENT = '시뮬레이팅 완료된 아이템 임시저장 테이블'; ''' % table_name return tb_sql def is_void(self, data): return data is () def select_items_data_from_tb_items_by_exchange_id(self, e_id): res = self.execute("SELECT * FROM `oh_my_bot_admin`.`item` WHERE `e_id` = '%s';"% e_id) return res def select_api_url_from_tb_exchange(self, exchange_name): return self._get_one_row_from_rows(self.execute("SELECT `api_url` FROM `oh_my_bot_admin`.`exchange` WHERE `name` = '%s';"% exchange_name)) def insert_job_data_to_cron(self, finance, exchange, type, market, columns, times, trade_type='single'): target = finance+'/'+exchange+'/'+ market+'/' for col in columns: res = self.execute("SELECT `id` FROM `oh_my_bot_admin`.`cron` WHERE `job` = '%s'" % (target+col)) if self.is_void(res): sql = '''INSERT INTO `oh_my_bot_admin`.`cron` (`type`, `target`, `time_unit`, `repeat_cycle`, `job`, `trade_type`) VALUES ('%s', '%s', '%s', '%s', '%s', '%s'); ''' % (type, market, col, str(times), target+col, str(trade_type)) self.execute(sql) def select_job_list(self): return self.execute("SELECT * FROM `oh_my_bot_admin`.`cron`") def select_exchange_list_for_bot(self): sql = ''' SELECT concat(finance.name, '/', exchange.name) as exchange_info FROM `oh_my_bot_admin`.`exchange` LEFT JOIN `oh_my_bot_admin`.`finance` ON `exchange`.`f_id` = `finance`.`id`; ''' return self.execute(sql) def insert_item_main_data(self, finance, exchange, item, total_trade_price_24h, total_trade_volume_24h): info = self._get_one_row_from_rows(self.execute("SELECT id, f_id FROM oh_my_bot_admin.exchange WHERE `name` = '%s';" % exchange)) res = self.execute("SELECT id FROM oh_my_bot_admin.item " "WHERE `name` = '%s' AND `f_id` = '%s' AND `e_id`= '%s';" % (item, info['f_id'], info['id'])) if self.is_void(res): sql = ''' INSERT INTO `oh_my_bot_admin`.`item` (`name`, `f_id`, `e_id`, `acc_trade_price_24h`, `acc_trade_volume_24h`) VALUES ('%s', '%s', '%s', '%s', '%s'); ''' % (item, info['f_id'], info['id'], total_trade_price_24h, total_trade_volume_24h) self.execute(sql) # 기존 데이터 삽입 def insertItemData(self, finance, exchange, item, t_data, time_type): # 종목별 분-시간-일 데이터에 맞춰 테이블 생성 item = item.replace('-', '_') item += '_' +str(time_type).upper() if not self._is_item_table(finance, exchange, item) : # 테이블 없을 경우 생성 => 금융 종류(finance), 거래소(exchange), 종목(item) self._create_table_for_item(finance, exchange, item, t_data) # 테이블이 있을 경우 바로 데이터 삽입 self._insert_item_data(finance, exchange, item, t_data, time_type) def get_last_idx_from_datetime(self, tb_name, t_data): sql = ''' SELECT `id`, `date` FROM `oh_my_bot_admin`.`%s` order by date desc LIMIT 1; ''' % tb_name res = self._get_one_row_from_rows(self.execute(sql)) # init auto_increment => 안넣을려고 했던 코드 self.execute("ALTER TABLE `oh_my_bot_admin`.`%s` AUTO_INCREMENT = %s" % (tb_name, int(res['id'])+1)) try: return t_data[t_data['date'] > str(res['date'])].index[0] # return t_data.loc[t_data['date'] == res['date']].index[0] + 1 except: return int(t_data.index[-1])+1 # Dataframe 타입 데이터 삽입 def insertItemDataframe(self, finance, exchange, item, t_data, time_type): item = item.replace('-', '_') item += '_' +str(time_type).upper() tb_name = self._get_table_name(finance, exchange, item) t_data['time_type'] = time_type t_data['e_id'] = self._get_exchange_id_by_name(exchange) t_data['f_id'] = self._get_finance_id_by_name(finance) self.data_columns_init(t_data) # 컬럼명 소문자 변환 t_data = t_data.iloc[:, ::-1] # 열 반전 if not self._is_item_table(finance, exchange, item): self._get_dataframe_create_table_sql(tb_name) else: last_idx = self.get_last_idx_from_datetime(tb_name, t_data) t_data = t_data[last_idx:] if t_data.empty: return # Inert Item Data self._insert_dataframe_to_table(tb_name, t_data) del t_data # t_data.to_sql(name=tb_name, # con=self.engine, # if_exists='append', # method='multi', # schema=db_info['db'], # index=False # ) def _insert_dataframe_to_table(self, tb_name, t_data): sql = "REPLACE INTO `oh_my_bot_admin`.`%s` (" % tb_name columns = t_data.columns # index-date / open, high, low, close, volume for col in columns: sql += "`%s`," % str(col) sql = sql[:-1] sql += ")" sql += " VALUES " for i, row in t_data.iterrows(): sql += " (" for col in columns: sql += "'%s'," % str(row[col]) sql = sql[:-1] sql += ")," sql = sql[:-1] sql += ";" self.execute(sql) def _get_dataframe_create_table_sql(self, tb_name): sql = ''' CREATE TABLE IF NOT EXISTS `oh_my_bot_admin`.`%s` ( `id` INT AUTO_INCREMENT, `f_id` INT NOT NULL, `e_id` INT NOT NULL, `time_type` VARCHAR(45) NOT NULL, `open` FLOAT NOT NULL, `high` FLOAT NOT NULL, `low` FLOAT NOT NULL, `close` FLOAT NOT NULL, `volume` FLOAT NOT NULL, `date` DATETIME NOT NULL, PRIMARY KEY (`id`), UNIQUE INDEX `date_UNIQUE` (`date` ASC)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; ''' % tb_name self.execute(sql) def update_standard_options_from_exchange(self, exchange_name, standard_volume): sql = "UPDATE `oh_my_bot_admin`.`exchange` SET `standard_volume`='%s' WHERE `name`='%s';" return self.execute(sql % (standard_volume, exchange_name)) def is_item_table_in_db(self, finance, exchange, item, time_type): item = item.replace('-', '_') item += '_' +str(time_type).upper() tb_name = finance+'_'+exchange+'_'+item return self.is_table_in_db(tb_name) def is_table_in_db(self, tb_name): return self._is_table(tb_name) def _is_item_table(self, finance, exchange, item): tb_name = self._get_table_name(finance, exchange, item) res = self.execute("check table %s;" % tb_name) return self._is_table(tb_name) def _get_table_name(self, finance, exchange, item): return "_".join([finance, exchange, item]) def insert_finance_and_exchange_data(self, finance, exchange, total_trade_price_24h, api_url): self._insert_finanace_data(finance) self._insert_exchange_data(exchange, total_trade_price_24h, api_url) def _create_table_for_item(self, finance, exchange, item, data): # self.insert_finance_and_exchange_data(finance, exchange) # 종목 테이블 생성 table_name = self._get_table_name(finance, exchange, item) if self._is_table(table_name) is False : sql = ''' CREATE TABLE `oh_my_bot_admin`.`%s` ( `id` INT AUTO_INCREMENT, `f_id` INT NOT NULL, `e_id` INT NOT NULL, `i_used` VARCHAR(5) NULL DEFAULT 'N' COMMENT '보조지표 데이터로 활용 유무', `m_used` VARCHAR(5) NULL DEFAULT 'N' COMMENT '머신러닝 데이터로 활용 유무', `time_type` VARCHAR(10) NOT NULL COMMENT '시간 단위', '''.strip() % table_name # .decode('utf-8')\ sql += self._get_sql_for_field(data) sql += " UNIQUE INDEX `%s_UNIQUE` (`%s` ASC), PRIMARY KEY (`id`))" % \ (unique_field[exchange], unique_field[exchange]) sql += " ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;" self.execute(sql) def _insert_finanace_data(self, finance): # 금융 데이터 있는 지 확인 res = self.is_void(self.execute("SELECT `id` FROM `finance` WHERE `name` = '%s'" % finance)) if res : # 금융 데이터 행 추가 self.execute("INSERT INTO `oh_my_bot_admin`.`finance` (`name`) VALUES ('%s');" % finance) def _get_finance_id_by_exchange_name(self, exchange_name): res = self._get_one_row_from_rows(self.execute("SELECT `id` FROM oh_my_bot_admin.finance WHERE `name` = " "(SELECT `finance_name` FROM oh_my_bot_admin.base WHERE `exchange_name` = '%s');"% exchange_name)) return res['id'] def _insert_exchange_data(self, exchange, total_trade_price_24h, api_url): # 거래소 데이터 있는 지 확인 res = self.is_void(self.execute("SELECT `id` FROM `exchange` WHERE `name` = '%s'" % exchange)) if res : f_id = self._get_finance_id_by_exchange_name(exchange) # 거래소 데이터 행 추가 self.execute("INSERT INTO `oh_my_bot_admin`.`exchange` (`name`, `f_id`, `standard_volume`, `api_url`) " "VALUES ('%s', '%s', '%s', '%s');" % (exchange, f_id, total_trade_price_24h, api_url)) def _get_sql_for_field(self, item): temp = list(item) r_sql = '' for index, element in enumerate(temp.pop()): r_sql += "`%s` VARCHAR(255) NOT NULL, " % element return r_sql def _insert_item_data(self, finance, exchange, item, data, time_type): if len(data) > 0 : data.reverse() # 데이터 역순 재정렬 query = self._get_query_for_insert_item_data(finance, exchange, item, data, time_type) try : # 중복 메세지 스킵(duplication) self.execute(query) except pymysql.IntegrityError : pass def select_row_data_from_exchange(self, name): res = self._get_one_row_from_rows(self.execute("SELECT * FROM oh_my_bot_admin.exchange WHERE `name` = '%s';" % name)) return res def _get_finance_id_by_name(self, finance): res = self._get_one_row_from_rows( self.execute("SELECT id FROM oh_my_bot_admin.finance WHERE `name` = '%s';" % finance)) return res['id'] def _get_exchange_id_by_name(self, finance): res = self._get_one_row_from_rows( self.execute("SELECT id FROM oh_my_bot_admin.exchange WHERE `name` = '%s';" % finance)) return res['id'] def get_id_by_exchange_name(self, finance): return self._get_exchange_id_by_name(finance) def _get_query_for_insert_item_data(self, finance, exchange, item, data, time_type): table_name = self._get_table_name(finance, exchange, item) # sql = 'INSERT IGNORE INTO `oh_my_bot_admin`.`%s` ' % table_name sql = 'REPLACE INTO `oh_my_bot_admin`.`%s` ' % table_name res = self._get_one_row_from_rows(self.execute("SELECT id FROM oh_my_bot_admin.finance WHERE `name` = '%s';"% finance)) f_id = res['id'] res = self._get_one_row_from_rows(self.execute("SELECT id FROM oh_my_bot_admin.exchange WHERE `name` = '%s';" % exchange)) e_id = res['id'] fields = '(`f_id`, `e_id`, `time_type`, ' for index, key in enumerate(data[0]): fields += '`%s`, ' % key fields += ') ' fields = fields.replace(', )', ')') sql += fields # add field row sql += "VALUES " for d in data: values = "('%s', '%s', '%s', " % (f_id, e_id, time_type) for key, value in d.items() : values += "'%s'," % value sql += values[:-1] + '),' # add value row sql = sql[:-1] + ';' return sql def data_columns_init(self, data): t_col = [] for c in data.columns: t_col.append(c.lower()) data.reset_index(level=0, inplace=True) t_col.insert(0, 'date') data.columns = t_col ''' for Trade functions ''' def delete_simul_results_by_deadline(self, deadline): sql = "DELETE FROM oh_my_bot_admin.simulation WHERE `added_date` < '%s'" % deadline self.execute(sql) def get_completed_simul_list(self): sql = "SELECT `job` FROM oh_my_bot_admin.cron WHERE `init_simul` = 'Y';" return self.execute(sql) def get_bot_list_by_exchange_id(self, e_i): sql = "SELECT `tb_name` FROM oh_my_bot_admin.bot WHERE `e_i` = '%s' LIMIT 1;" % e_i return self.execute(sql) def get_simul_res_top_row(self, t_name): sql = "SELECT * FROM oh_my_bot_admin.simulation WHERE `t_table_name` like '%s%%' " \ "ORDER BY `win_rate` DESC, `profit_rate` DESC, `added_date` DESC LIMIT 1;" % t_name return self._get_one_row_from_rows(self.execute(sql)) def get_bot_data_by_target(self, target): sql = "SELECT * FROM oh_my_bot_admin.bot WHERE `target` = '%s';" % target return self._get_one_row_from_rows(self.execute(sql)) def upsert_bot_data(self, bot_data): sql = ''' REPLACE INTO `oh_my_bot_admin`.`bot` (`f_i`, `e_i`, `target`, `time_unit`, `duration_days`, `p_profit`, `p_winrate`, `tb_name`, `stop_loss`, `stop_profit`, `strategy`, `status`, `trade_cnt`, `profit`, `trade_type`, `mode`) VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', 'Y', '0', '0', '%s', 'test'); ''' % (bot_data['f_i'], bot_data['e_i'], bot_data['target'], bot_data['time_type'], bot_data['duration_days'], bot_data['profit_rate'],bot_data['win_rate'], bot_data['t_table_name'], bot_data['stop_loss'], bot_data['stop_profit'], bot_data['used_patterns'], bot_data['trade_type']) self.execute(sql) def select_running_bots(self): sql = "SELECT * FROM `oh_my_bot_admin`.`bot` WHERE `status` = 'Y'"; return self.execute(sql) def update_bot_data_for_stop_by_id(self, id): self.execute("UPDATE `oh_my_bot_admin`.`bot` SET `status` = 'N' WHERE (`id` = '%s');" % id) def select_all_users_id_for_trade(self): return self.execute("SELECT id FROM oh_my_bot_admin.user;") def get_bots_by_user_id(self, id): sql = "SELECT * FROM oh_my_bot_admin.bot WHERE `user_id` = '%s' AND `status` = 'Y';" % id return self.execute(sql) def update_tb_cron_simul_status(self, job_string): sql = "UPDATE `oh_my_bot_admin`.`cron` SET `init_simul` = 'N' WHERE `job` LIKE '%s%%';" % job_string return self.execute(sql) def select_tb_cron_simul_status(self, job_string): sql = "SELECT `init_simul` FROM `oh_my_bot_admin`.`cron` WHERE `job` LIKE '%s%%';" % job_string return self.execute(sql) def bot_update_by_trade_data(self, bot_info): sql = ''' UPDATE `oh_my_bot_admin`.`bot` SET `mode` = '%s', `position` = '%s', `position_price` = '%s', `amount` = '%s', `last_amount` = '%s', `profit` = '%s', `winrate` = '%s', `trade_cnt` = '%s', `win_cnt` = '%s',`lose_cnt` = '%s', `last_signal` = '%s', `last_profit` = '%s' WHERE (`target` = '%s'); ''' % (bot_info['mode'], bot_info['position'], bot_info['position_price'], bot_info['amount'], bot_info['last_amount'], bot_info['profit'], bot_info['winrate'], bot_info['trade_cnt'], bot_info['win_cnt'], bot_info['lose_cnt'], bot_info['last_signal'], bot_info['last_profit'], bot_info['target']) return self.execute(sql) def select_user_keys_by_id(self, user_id): # res = self.execute("SELECT `auth_info` FROM oh_my_bot_admin.user WHERE `id` = '2';") res = self.execute("SELECT `auth_info` FROM oh_my_bot_admin.user WHERE `id` = %s;" % user_id) if res is not (): return json.loads(res[0]['auth_info']) # return res[0]['auth_info'] return False def insert_trade_data_to_tb_trade(self, b_info): sql = ''' REPLACE INTO `oh_my_bot_admin`.`trade` (`f_i`, `e_i`, `target`, `position`, `price`, `strategy`) VALUES ('%s', '%s', '%s', '%s', '%s', '%s'); ''' % (b_info['f_i'], b_info['e_i'], b_info['target'], b_info['position'], b_info['position_price'], b_info['strategy']) return self.execute(sql) # 초기 시드 갱신 def update_first_seed_by_now_seed(self, bot_info, updated_date): sql = ''' UPDATE `oh_my_bot_admin`.`bot` SET `amount` = '%s', `update_date` = '%s' WHERE (`id` = '%s'); ''' % (float(bot_info['last_amount']), updated_date, int(bot_info['id'])) return self.execute(sql) ''' User Info ''' ''' 유저정보에 거래소 API 키 추가 add and update key template key = {'binance' :{ 'access': '123', 'secret': '123' }} ''' def upsert_keys_to_user_row(self, user_email, key): sql = "SELECT `auth_info` FROM oh_my_bot_admin.user " \ "WHERE `email` = '%s';" % user_email res = self.execute(sql) if res is not (): auth = json.loads(res[0]['auth_info']) auth.update(key) sql = "UPDATE `oh_my_bot_admin`.`user` SET `auth_info` = '%s' WHERE (`email` = '%s');" % \ (json.dumps(auth), user_email) return self.execute(sql)