854 lines
36 KiB
Python
854 lines
36 KiB
Python
# -*- 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)
|