Simulator_Api.py
4.83 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
import datetime
from sqlalchemy import *
import pymysql
from pandas import DataFrame
import config
class Simulator_Api:
def __init__(self,simul_num,op,db_name):
self.simul_num=int(simul_num)
if op=="real":
self.op='real'
self.simul_reset=False
self.db_name=db_name
self.set_variable()
def set_date(self):
self.today=datetime.datetime.today().strftime("%Y%m%d")
def set_variable(self):
self.set_date()
self.set_database()
# 매수/매도 알고리즘 선택 -> 차후 알고리즘 별로 구현 및 재설정
self.buy_algorithm=1
self.sell_algorithm=1
# 투자에서 사용할 변수 설정
self.per_invest=1000000 # 한 항목 당 구매할 금액
self.sell_point=10 # 매도 수익률
self.limit_money=1000000 # 최소로 남겨놓을 금액
# 특정 데이터베이스 내에 특정 테이블이 존재하는지 확인하는 함수
def is_table_exist(self,db_name,table_name):
query="select 1 from information_schema.tables where table_schema='%s' and table_name='%s'"
result=self.engine_simul.execute(query%(db_name,table_name)).fetchall()
if len(result)==1:
return True
else:
return False
# 데이터베이스 연결 설정
def set_database(self):
if self.op=="real":
self.engine_simul=create_engine("mysql+pymysql://"+config.db_id+":"+config.db_pw+"@"+config.db_ip+
":"+config.db_port+"/"+str(self.db_name),encoding='utf-8')
else:
self.db_name='simulator'+str(self.simul_num)
self.engine_simul=create_engine("mysql+pymysql://"+config.db_id+":"+config.db_pw+"@"+config.db_ip+
":"+config.db_port+"/"+str(self.db_name),encoding='utf-8')
self.engine_daily = create_engine("mysql+pymysql://" + config.db_id + ":" + config.db_pw + "@" + config.db_ip +
":" + config.db_port + "/daily_info" , encoding='utf-8')
self.engine_stock = create_engine("mysql+pymysql://" + config.db_id + ":" + config.db_pw + "@" + config.db_ip +
":" + config.db_port + "/stock_info", encoding='utf-8')
self.engine_minute = create_engine("mysql+pymysql://" + config.db_id + ":" + config.db_pw + "@" + config.db_ip +
":" + config.db_port + "/minute_info", encoding='utf-8')
self.conn=pymysql.connect(
host=config.db_ip,
port=int(config.db_port),
user=config.db_id,
password=config.db_pw,
charset='utf8'
)
# 날짜별 주식 데이터를 저장해 놓은 데이터베이스에서 가장 최근 날짜를 가져오는 함수
def get_latest_date(self):
query="select table_name from information_schema.tables where table_schema='daily_info' and " \
"table_name like '%s' order by table_name desc limit 1"
result=self.engine_daily.execute(query%("20%%")).fetchall()
if len(result)==0:
return False
else:
return result[0][0]
# transaction_history 테이블의 Dataframe 생성
def df_transaction_history(self):
df_temp={'id':[]}
self.df_th=DataFrame(df_temp,
columns=['id','order_num','code','code_name','rate','purchase_rate',
'purchase_price','present_price','valuation_price','valuation_profit',
'holding_amount','buy_date','total_purchase_price','contract_check','per_invest',
'sell_date','sell_price','sell_rate'
])
# daily_info 데이터베이스에서 특정 날짜에서 특정 코드에 해당하는 정보만 가져오는 함수수
def get_daily_info_by_code(self,code,date):
query="select * from {} where code='{}' group by code"
daily_info=self.engine_daily.execute(query.format(date,code)).fetchall()
df_daily_info=DataFrame(daily_info,
columns=['index','index2','date','check_item','code',
'code_name','dff_rate',
'close','open','high','low','volume',
'avg5','avg10','avg20','avg60','avg120',
'prev_avg5','prev_avg10','prev_avg20','prev_avg60','prev_avg120',
'avg5_diff_rate','avg10_diff_rate','avg20_diff_rate','avg60_diff_rate',
'avg120_diff_rate',
'vol5','vol10','vol20','vol60','vol120'])
return df_daily_info