def __init__(self):
super().__init__()
self.objStockChart = win32com.client.Dispatch("CpSysDib.StockChart")
#####################################################################
# logger
self.logger = logging.getLogger("my_setting")
self.logger = LoggerAdapter(CpStockChart.__name__, self.logger)
self.logger.debug(f'CpStockChart Start')
#6:전일대비(long or float) - 주) 대비부호(37)과반드시같이요청해야함
self.rqField = [0, 2, 3, 4, 5, 6, 8, 37] # 요청 필드
def __init__(self):
super().__init__()
self.ts = time.time()
self.ymd = datetime.datetime.fromtimestamp(self.ts).strftime('%Y%m%d')
self.timestamp = datetime.datetime.fromtimestamp(
self.ts).strftime('%Y-%m-%d %H:%M:%S.%f')
self.logger = logging.getLogger("my_setting")
self.logger = LoggerAdapter(CpDB.__name__, self.logger)
# db 정보 초기화
config = ConfigParser()
config.read('setting/init.ini')
db = config['db']
self.logger.debug(
f'db: {db["USR"]} {db["URL"]} {int(db["PORT"])} {db["DB"]}')
self.db_info = {
DataBase.USR: db["USR"],
DataBase.PWD: db["PW"],
DataBase.URL: db["URL"],
DataBase.PORT: int(db["PORT"]),
DataBase.DB: db["DB"]
}
###############################################################################
# Connect
os.environ["NLS_LANG"] = ".AL32UTF8"
hostname = os.environ['COMPUTERNAME']
self.logger.debug(f'db: {self.db_info[DataBase.USR]}')
# Open database connection
self.con = pymysql.connect(host=self.db_info[DataBase.URL],
port=self.db_info[DataBase.PORT],
user=self.db_info[DataBase.USR],
passwd=self.db_info[DataBase.PWD],
db=self.db_info[DataBase.DB],
charset='utf8',
autocommit=False)
# Connection 으로부터 Dictoionary Cursor 생성
self.cur = self.con.cursor(pymysql.cursors.DictCursor)
def __init__(self):
super().__init__()
# plus 상태 체크
if com.InitPlusCheck() == False:
exit()
if com.InitTradeInit() == False:
exit()
#####################################################################
# account info
self.acc_no = com.g_objCpTrade.AccountNumber[0] # 계좌번호
self.acc_tp = com.g_objCpTrade.GoodsList(self.acc_no, 1) # 주식상품 구분
#####################################################################
# stock object
self.stkCurData = CpData.StkCurData()
self.stkTickData = CpData.StkTickData() # 주문 현재가/10차 호가 저장
self.objSBCur = CpData.CpSBStockCur()
self.objSBid = CpData.CpSBStockBid()
#####################################################################
# 시세종목
#self.codelist = ['A05930', 'A151860']
self.codelist = ['A151860']
#####################################################################
# logger
self.logger = logging.getLogger("my_setting")
self.logger = LoggerAdapter(CpDataWindow.__name__, self.logger)
self.logger.debug(f'CpDataWindow Start')
#####################################################################
# db object
self.objDB = CpDB.CpDB()
#####################################################################
# 이동평균
self.cntTick = 0
self.stkCur = []
self.futCur = []
self.stkShortOdrTP = 'N'
self.stkLongOdrTP = 'N'
self.setWindowTitle(sys.argv[0])
self.setGeometry(300, 300, 300, 700)
nH = 20
btnBuy = QPushButton("종목 분 데이터 조회", self)
btnBuy.move(20, nH)
# btnBuy.resize(200,30)
btnBuy.clicked.connect(self.btnBuy_clicked)
nH += 50
btnExit = QPushButton("종료", self)
btnExit.move(20, nH)
# btnExit.resize(200,30)
btnExit.clicked.connect(self.btnExit_clicked)
nH += 50
btnMst = QPushButton("기본정보", self)
btnMst.move(20, nH)
# btnExit.resize(200,30)
btnMst.clicked.connect(self.btnMst_clicked)
nH += 50
btnInv = QPushButton("투자자종합", self)
btnInv.move(20, nH)
btnInv.clicked.connect(self.btnInv_clicked)
class CpDataWindow(QMainWindow):
def __init__(self):
super().__init__()
# plus 상태 체크
if com.InitPlusCheck() == False:
exit()
if com.InitTradeInit() == False:
exit()
#####################################################################
# account info
self.acc_no = com.g_objCpTrade.AccountNumber[0] # 계좌번호
self.acc_tp = com.g_objCpTrade.GoodsList(self.acc_no, 1) # 주식상품 구분
#####################################################################
# stock object
self.stkCurData = CpData.StkCurData()
self.stkTickData = CpData.StkTickData() # 주문 현재가/10차 호가 저장
self.objSBCur = CpData.CpSBStockCur()
self.objSBid = CpData.CpSBStockBid()
#####################################################################
# 시세종목
#self.codelist = ['A05930', 'A151860']
self.codelist = ['A151860']
#####################################################################
# logger
self.logger = logging.getLogger("my_setting")
self.logger = LoggerAdapter(CpDataWindow.__name__, self.logger)
self.logger.debug(f'CpDataWindow Start')
#####################################################################
# db object
self.objDB = CpDB.CpDB()
#####################################################################
# 이동평균
self.cntTick = 0
self.stkCur = []
self.futCur = []
self.stkShortOdrTP = 'N'
self.stkLongOdrTP = 'N'
self.setWindowTitle(sys.argv[0])
self.setGeometry(300, 300, 300, 700)
nH = 20
btnBuy = QPushButton("종목 분 데이터 조회", self)
btnBuy.move(20, nH)
# btnBuy.resize(200,30)
btnBuy.clicked.connect(self.btnBuy_clicked)
nH += 50
btnExit = QPushButton("종료", self)
btnExit.move(20, nH)
# btnExit.resize(200,30)
btnExit.clicked.connect(self.btnExit_clicked)
nH += 50
btnMst = QPushButton("기본정보", self)
btnMst.move(20, nH)
# btnExit.resize(200,30)
btnMst.clicked.connect(self.btnMst_clicked)
nH += 50
btnInv = QPushButton("투자자종합", self)
btnInv.move(20, nH)
btnInv.clicked.connect(self.btnInv_clicked)
###############################################################################
# 주식 현재가 업데이트
# thread 3개 생성시 3개 동시 업데이트 됨
def monitorCurPriceChange(self):
print("=====================================================")
print("monitorCurPriceChange ", self.stkCurData.item,
self.stkCurData.item_nm, self.stkCurData.close)
print("=====================================================")
self.cntTick = self.cntTick + 1
self.stkCur.append(self.stkCurData.close)
self.stkCurnp = np.array(self.stkCur, dtype=float) # double ->float
# 틱갯수로 이동평균 / self.futCurnp.shape 배열갯수
if self.cntTick >= 20 and self.cntTick % 20 == 0:
tama5 = ta.MA(self.stkCurnp, 5)
tama5 = np.nan_to_num(tama5)
tama10 = ta.MA(self.stkCurnp, 10)
tama10 = np.nan_to_num(tama10)
tama20 = ta.MA(self.stkCurnp, 20)
tama20 = np.nan_to_num(tama20)
tama60 = ta.MA(self.stkCurnp, 60)
tama60 = np.nan_to_num(tama60)
tama120 = ta.MA(self.stkCurnp, 120)
tama120 = np.nan_to_num(tama120)
tama240 = ta.MA(self.stkCurnp, 240)
tama240 = np.nan_to_num(tama240)
#tastddev = ta.STDDEV(self.stkCurnp)
strtgyData = CpOdr.StrtgyData()
strtgyData.ymd = com.dtymd
strtgyData.strtgy_no = 100
strtgyData.item = self.stkCurData.item
strtgyData.tp = 'T'
strtgyData.term = 20
strtgyData.ma5 = float(tama5[-1])
strtgyData.ma10 = float(tama10[-1])
strtgyData.ma20 = float(tama20[-1])
strtgyData.ma60 = float(tama60[-1])
strtgyData.ma120 = float(tama120[-1])
strtgyData.ma240 = float(tama240[-1])
#strtgyData.vol_avg = 0
#strtgyData.vol_std = tastddev[-1]
self.objDB.stkindt('I', strtgyData, strtgyData)
self.logger.error(
f"CUR {tama5[-1]} {tama20[-1]} {tama5[-1]/tama20[-1]} {tama60[-1]/tama20[-1]}"
)
###############################################################################
# 매수 주문
# 잔고 보유 여부 확인
# 5일선이 20일선 보다 10% 이하 만큼 높은 경우
if tama5[-1] > tama20[-1] and tama5[-1] / tama20[
-1] <= 1.1 and self.stkLongOdrTP == 'N':
print("if self.cntTick >= 100 and self.cntTick % 20 == 0: ",
self.cntTick, type(self.stkCurnp), self.stkCurnp.shape,
self.stkCurnp.mean(), tama5[-1], tama20[-1], tama60[-1],
tama120[-1])
strtgyData.qty = 1
strtgyData.prc = self.stkCurData.close
strtgyData.odr_tp = '2'
strtgyData.stgy_msg = '매수 : ' + str(tama5[-1]) + ' ' + str(
tama20[-1]) + ' ' + str(tama60[-1]) + ' ' + str(
tama120[-1]) + ' ' + self.stkLongOdrTP
self.objDB.stkstrtgy('I', strtgyData, strtgyData)
self.stkLongOdrTP = 'Y'
###############################################################################
# 매도
# 필히 잔고 유무 확인
# 20일선이 60일선 보다 10% 낮게 있는 경우 매도
# TRD.STKCON 확인, 종목이 여러개 인 경우 실시간 잔고 조회
if tama20[-1] < tama60[-1] and tama60[-1] / tama20[
-1] >= 1.1 and self.stkShortOdrTP == 'N':
strtgyData.qty = 1
strtgyData.prc = self.stkCurData.close
strtgyData.odr_tp = '1'
strtgyData.stgy_msg = '매도 : ' + str(tama5[-1]) + ' ' + str(
tama20[-1]) + ' ' + str(tama60[-1]) + ' ' + str(
tama120[-1]) + ' ' + self.stkShortOdrTP
self.objDB.stkstrtgy('I', strtgyData, strtgyData)
self.stkShortOdrTP = 'Y'
print("if self.cntTick >= 100 and self.cntTick % 20 == 0: ",
self.cntTick, type(self.stkCurnp), self.stkCurnp.shape,
self.stkCurnp.mean(), tama5[-1], tama20[-1], tama60[-1],
tama120[-1])
print(self.cntTick, type(self.stkCurnp), self.stkCurnp.shape,
self.stkCurnp.mean())
self.objDB.stkcurt('I', self.stkCurData, self.stkCurData)
#COLUMNS_CHART_DATA = ['date', 'open', 'high', 'low', 'close', 'volume']
###############################################################################
# 강화학습 통신
# self.objTcpClient = TcpClient.TcpClient()
# send_list = [self.stkCurData.ymd,
# self.stkCurData.open,
# self.stkCurData.high,
# self.stkCurData.low,
# self.stkCurData.close,
# self.stkCurData.acc_vol
# ]
# req_data = {
# gd.KEY_NM_EVT: gd.EVT_TYPE_GET_KP200_FUT,
# gd.KEY_NM_DATA: send_list
# }
#
# self.objTcpClient.tcpClient(req_data)
return
###############################################################################
# self.parent.monitorTickChange()
def monitorTickChange(self):
(self.stkTickData.ask_vwap, self.stkTickData.bid_vwap,
self.stkTickData.mid_vwap, self.stkTickData.mid_prc,
self.stkTickData.prc_diff) = com.getFutVwap(self.stkTickData)
print("*****************************************************")
print("monitorTickChange ", self.stkTickData.ask_num[0],
self.stkTickData.ask_qty[0], self.stkTickData.ask_prc[0])
print("monitorTickChange ", self.stkTickData.ask_vwap,
self.stkTickData.bid_vwap, self.stkTickData.mid_vwap,
self.stkTickData.mid_prc, self.stkTickData.prc_diff)
print("*****************************************************")
self.objDB.stktick('I', self.stkTickData, self.stkTickData)
return
###############################################################################
# stock subscribe
# def Subscribe(self, item, stkCurData, parent):
def itemSubscribe(self, item):
# 실시간 통신 요청
objSBCur = CpData.CpSBStockCur()
objSBCur.Subscribe(item, self.stkCurData, self)
objSBTick = CpData.CpSBStockBid()
objSBTick.Subscribe(item, self.stkTickData, self)
def dataRequest(self):
# 실시간 현재가 요청
self.objThread = {}
self.objThreadFut = {}
#############################################################
cnt = len(self.codelist)
for i in range(cnt):
item = self.codelist[i]
# thread 미사용
self.itemSubscribe(item)
time.sleep(1)
# thread 사용
# self.objThread[item] = threading.Thread(target=self.itemSubscribe, args=(item,))
# self.objThread[item].start()
# time.sleep(1)
#codelist = CpData.StkDataHist()
#self.objDB.stkmsttgt('S', codelist, codelist)
#cnt = len(codelist.item)
#for i in range(cnt):
# item_list = list(codelist.item[i])
# item = item_list[0]
# print(cnt, item, item_list, type(item_list))
#
# self.itemSubscribe(item)
# time.sleep(1)
# codelistf = ['101P3','101P6']
# cntf = len(codelistf)
# for i in range(cntf):
# itemf = codelistf[i]
#
# # thread 미사용
# self.itemFutSubscribe(itemf)
# time.sleep(1)
# #
# # self.objThreadFut[itemf] = threading.Thread(target=self.itemFutSubscribe, args=(itemf,))
# # self.objThreadFut[itemf].start()
# # time.sleep(1)
# 투자자정보
def btnInv_clicked(self):
return
# 매수 주문
def btnBuy_clicked(self):
self.dataRequest()
# #####################################################################
# # 전략, 체결
# # def Request(self, item, sel_tp, term, count, start_ymd, end_ymd, hist):
# # sel_tp : 1 기간, 2 개수
# self.stkDataHist = CpData.StkDataHist()
# self.objStockChart = CpData.CpStockChart()
# ymd = com.dtymd
# if self.objStockChart.Request('A005930', '1', 'T', 100000, ymd, ymd, self.stkDataHist) == False:
# exit()
#
# # cnt = len(self.stkDataHist.ymd)
# # for i in range(0, cnt, 1):
# # if i < 100 :
# # print("self.objStockChart.Request > ", cnt, i, self.stkDataHist.ymd[i], self.stkDataHist.time[i], self.stkDataHist.open[i])
#
# i=0
# cnt = len(self.stkDataHist.ymd)
# print("self.objStockChart.Request > ", cnt, i, self.stkDataHist.ymd[i], self.stkDataHist.time[i], self.stkDataHist.open[i])
#
# self.objDB.stkcurt('I', self.stkDataHist, self.stkDataHist)
#
# return
# 정정주문
def btnModify_clicked(self):
return
# 취소주문
def btnCancel_clicked(self):
return
# 종료
def btnExit_clicked(self):
exit()
# 종목정보
def btnMst_clicked(self):
return
def __init__(self):
super().__init__()
pythoncom.CoInitialize()
self.odrExec0 = CpOdr.CpOrderExec()
self.odrExec1 = CpOdr.CpOrderExec()
self.odrExec2 = CpOdr.CpOrderExec()
self.odrExec3 = CpOdr.CpOrderExec()
# plus 상태 체크
if com.InitPlusCheck() == False:
exit()
if com.InitTradeInit() == False:
exit()
self.acc_no = com.g_objCpTrade.AccountNumber[0] # 계좌번호
self.acc_tp = com.g_objCpTrade.GoodsList(self.acc_no, 1) # 주식상품 구분
#####################################################################
# logger
self.logger = logging.getLogger("my_setting")
self.logger = LoggerAdapter(CpOdrWindow.__name__, self.logger)
self.logger.debug(f'CpOdrWindow Start {self.acc_no} {self.acc_tp}')
#####################################################################
# 전략, 체결
self.strtgyData = CpOdr.StrtgyData()
#####################################################################
# 체결 data class
self.stkConData = CpOdr.StkConData()
self.objSBCon = CpOdr.CpSBConclusion()
#####################################################################
# 체겷 Subscribe
# def monitorCon(self): 실시간 업데이트
self.objSBCon.Subscribe(self.stkConData, self)
#####################################################################
# 잔고
self.objBlnc = CpOdr.Cp6033()
#####################################################################
# stock object
self.stkCurData = CpData.StkCurData()
self.stkTickData = CpData.StkTickData() # 주문 현재가/10차 호가 저장
self.objSBCur = CpData.CpSBStockCur()
self.objSBbid = CpData.CpSBStockBid()
#####################################################################
# db object
self.objDB = CpDB.CpDB()
self.setWindowTitle("PLUS API TEST")
self.setGeometry(300, 300, 300, 700)
nH = 20
btnBuy = QPushButton("매수/매도 주문", self)
btnBuy.move(20, nH)
# btnBuy.resize(200,30)
btnBuy.clicked.connect(self.btnBuy_clicked)
nH += 50
btnExit = QPushButton("종료", self)
btnExit.move(20, nH)
# btnExit.resize(200,30)
btnExit.clicked.connect(self.btnExit_clicked)
nH += 50
btnMst = QPushButton("기본정보", self)
btnMst.move(20, nH)
# btnExit.resize(200,30)
btnMst.clicked.connect(self.btnMst_clicked)
nH += 50
btnInv = QPushButton("투자자종합", self)
btnInv.move(20, nH)
btnInv.clicked.connect(self.btnInv_clicked)
class CpOdrWindow(QMainWindow):
def __init__(self):
super().__init__()
pythoncom.CoInitialize()
self.odrExec0 = CpOdr.CpOrderExec()
self.odrExec1 = CpOdr.CpOrderExec()
self.odrExec2 = CpOdr.CpOrderExec()
self.odrExec3 = CpOdr.CpOrderExec()
# plus 상태 체크
if com.InitPlusCheck() == False:
exit()
if com.InitTradeInit() == False:
exit()
self.acc_no = com.g_objCpTrade.AccountNumber[0] # 계좌번호
self.acc_tp = com.g_objCpTrade.GoodsList(self.acc_no, 1) # 주식상품 구분
#####################################################################
# logger
self.logger = logging.getLogger("my_setting")
self.logger = LoggerAdapter(CpOdrWindow.__name__, self.logger)
self.logger.debug(f'CpOdrWindow Start {self.acc_no} {self.acc_tp}')
#####################################################################
# 전략, 체결
self.strtgyData = CpOdr.StrtgyData()
#####################################################################
# 체결 data class
self.stkConData = CpOdr.StkConData()
self.objSBCon = CpOdr.CpSBConclusion()
#####################################################################
# 체겷 Subscribe
# def monitorCon(self): 실시간 업데이트
self.objSBCon.Subscribe(self.stkConData, self)
#####################################################################
# 잔고
self.objBlnc = CpOdr.Cp6033()
#####################################################################
# stock object
self.stkCurData = CpData.StkCurData()
self.stkTickData = CpData.StkTickData() # 주문 현재가/10차 호가 저장
self.objSBCur = CpData.CpSBStockCur()
self.objSBbid = CpData.CpSBStockBid()
#####################################################################
# db object
self.objDB = CpDB.CpDB()
self.setWindowTitle("PLUS API TEST")
self.setGeometry(300, 300, 300, 700)
nH = 20
btnBuy = QPushButton("매수/매도 주문", self)
btnBuy.move(20, nH)
# btnBuy.resize(200,30)
btnBuy.clicked.connect(self.btnBuy_clicked)
nH += 50
btnExit = QPushButton("종료", self)
btnExit.move(20, nH)
# btnExit.resize(200,30)
btnExit.clicked.connect(self.btnExit_clicked)
nH += 50
btnMst = QPushButton("기본정보", self)
btnMst.move(20, nH)
# btnExit.resize(200,30)
btnMst.clicked.connect(self.btnMst_clicked)
nH += 50
btnInv = QPushButton("투자자종합", self)
btnInv.move(20, nH)
btnInv.clicked.connect(self.btnInv_clicked)
def monitorCurPriceChange(self):
print("=====================================================")
print("monitorCurPriceChange ", self.stkCurData.item,
self.stkCurData.item_nm, self.stkCurData.close)
print("=====================================================")
self.objDB.stkcurt('I', self.stkCurData, self.stkCurData)
return
###############################################################################
# 체결 정보 업데이트
# 클래스 실행 하는 곳이 아님 실행 되는 클래스에서 업데이트
def monitorCon(self):
print("=====================================================")
print("monitorCon ", self.stkConData.item, self.stkConData.blnc_qty,
self.stkConData.con_prc, self.stkConData.strtgy_no)
print("=====================================================")
self.objDB.stkcon('I', self.stkConData, self.stkConData)
# 체결 데이터인 경우
if self.stkConData.con_tp == '1': #체결
###############################################################################
# creon_cpodr.py : 정상 주문시 insert
# 체결시 주문내역 업데이트
self.objDB.stkodr('U', self.stkConData, self.stkConData)
###############################################################################
# 잔고 정보 업데이트
self.stkBlncData = CpOdr.StkBlncData()
self.odrExec0.getBlncData('S', self.acc_no, self.stkBlncData)
self.objDB.stkblnc('I', self.stkBlncData, self.stkBlncData)
return
# 투자자정보 잔고 테스트
def btnInv_clicked(self):
# creon_cpodr.py
self.stkBlncData = CpOdr.StkBlncData()
self.odrExec0.getBlncData('S', self.acc_no, self.stkBlncData)
cnt = len(self.stkBlncData.acc_no)
print("BLNC CNT", cnt)
for i in range(cnt):
self.logger.debug(
f' {self.stkBlncData.acc_no[i]} {self.stkBlncData.item[i]}')
self.objDB.stkblnc('I', self.stkBlncData, self.stkBlncData)
# self.investor = CpData.CpInvestor()
# self.investor.Request()
#
# self.SBinvestor = CpData.CpSBInvestor()
# self.SBinvestor.Subscribe("001", self)
return
def my_coroutine(self, task_name, seconds_to_sleep=3):
print('{0} sleeping for: {1} seconds'.format(task_name,
seconds_to_sleep))
time.sleep(seconds_to_sleep)
print('{0} is finished'.format(task_name))
###############################################################################
# stock subscribe
def item_subscribe(self, item):
# 실시간 통신 요청
self.objSBCur.Subscribe(item, self.stkCurData, self)
###############################################################################
#
def item_request(self, item, tt):
pythoncom.CoInitialize()
print("def item_request(self, item):")
# print('#####################################')
# objStockMst = win32com.client.Dispatch("DsCbo1.StockMst")
# objStockMst.SetInputValue(0, code)
# objStockMst.BlockRequest()
# print('BlockRequest 로 수신 받은 데이터')
# item = {}
# item['현재가'] = objStockMst.GetHeaderValue(11) # 종가
# item['대비'] = objStockMst.GetHeaderValue(12) # 전일대비
# print(item)
objStockMst = win32com.client.Dispatch("DsCbo1.StockMst")
while True:
if self.objCurBid2.Request(objStockMst, item,
self.stkTickData) == False:
print("현재가 통신 실패")
time.sleep(tt)
# aa = CpData.CpSBStockCurBid()
# objStockMst = win32com.client.Dispatch("DsCbo1.StockMst")
# aa.Subscribe(objStockMst, item, self.stkTickData, self)
#
# print("self.stkTickData.cur", self.stkTickData.cur)
#J57DM09
def btnBuy_clicked(self):
t4 = threading.Thread(target=self.odrExec3.order_thread,
args=(self.acc_no, ))
t4.start()
# 정정주문
def btnModify_clicked(self):
self.bsMonitor()
# self.odrExec.ModifyOrder()
return
# 취소주문
def btnCancel_clicked(self):
# self.odrExec.CancelOrder()
return
# 종료
def btnExit_clicked(self):
exit()
# 종목정보
def btnMst_clicked(self):
# self.stockMst = CpData.CpStockMst()
# self.stockMst.Request("A005930")
self.dicOdrList = {}
self.odrList = CpOdr.StkUnConData()
self.odrExec0.canorder_async(self.acc_no, self.dicOdrList,
self.odrList)
cnt = len(self.odrList.item)
for i in range(cnt):
print("주문정보 : %s, %s" %
(self.odrList.item[i], self.odrList.item_nm[i]))
return
def signal_handler(signal, frame): # SIGINT handler정의
print('You pressed Ctrl+C!')
sys.exit(0)
class CpStockChart:
def __init__(self):
super().__init__()
self.objStockChart = win32com.client.Dispatch("CpSysDib.StockChart")
#####################################################################
# logger
self.logger = logging.getLogger("my_setting")
self.logger = LoggerAdapter(CpStockChart.__name__, self.logger)
self.logger.debug(f'CpStockChart Start')
#6:전일대비(long or float) - 주) 대비부호(37)과반드시같이요청해야함
self.rqField = [0, 2, 3, 4, 5, 6, 8, 37] # 요청 필드
# 차트 요청 - 기간 기준으로
def RequestFromTo(self, code, fromDate, toDate, caller):
print(code, fromDate, toDate)
# plus 상태 체크
if com.InitPlusCheck() == False:
exit()
self.objStockChart.SetInputValue(0, code) # 종목코드
self.objStockChart.SetInputValue(1, ord('1')) # 기간으로 받기
self.objStockChart.SetInputValue(2, toDate) # To 날짜
self.objStockChart.SetInputValue(3, fromDate) # From 날짜
#self.objStockChart.SetInputValue(4, 500) # 최근 500일치
self.objStockChart.SetInputValue(5, self.rqField) # 날짜,시가,고가,저가,종가,거래량
self.objStockChart.SetInputValue(6, ord('D')) # '차트 주기 - 일간 차트 요청
self.objStockChart.SetInputValue(9, ord('1')) # 수정주가 사용
self.objStockChart.BlockRequest()
rqStatus = self.objStockChart.GetDibStatus()
rqRet = self.objStockChart.GetDibMsg1()
print("통신상태", rqStatus, rqRet)
if rqStatus != 0:
exit()
len = self.objStockChart.GetHeaderValue(3)
sign = self.objStockChart.GetHeaderValue(8)
print("수신갯수", len, "부호", chr(sign))
caller.dates = []
caller.opens = []
caller.highs = []
caller.lows = []
caller.closes = []
caller.vols = []
caller.diff = []
caller.sign = []
caller.item = []
# 요청필드 순서와 관계없음
# self.rqField = [0, 2, 3, 4, 5, 6, 8, 37] # 요청 필드
for i in range(len):
caller.dates.append(self.objStockChart.GetDataValue(0, i)) # 0: 날짜(ulong)
caller.opens.append(self.objStockChart.GetDataValue(1, i)) # 2:시가(long or float)
caller.highs.append(self.objStockChart.GetDataValue(2, i)) # 3:고가(long or float)
caller.lows.append(self.objStockChart.GetDataValue(3, i)) # 4:저가(long or float)
caller.closes.append(self.objStockChart.GetDataValue(4, i)) # 5:종가(long or float)
caller.diff.append(self.objStockChart.GetDataValue(5, i)) # 8:거래
caller.vols.append(self.objStockChart.GetDataValue(6, i)) # 6:전일
#caller.sign.append(chr(self.objStockChart.GetDataValue(8, i))) # 37:부호
caller.item.append(code)
print("수신갯수", len)
# 차트 요청 - 최근일 부터 개수 기준
def RequestDWM(self, code, dwm, count, caller):
# plus 상태 체크
if com.InitPlusCheck() == False:
exit()
self.objStockChart.SetInputValue(0, code) # 종목코드
self.objStockChart.SetInputValue(1, ord('2')) # 개수로 받기
self.objStockChart.SetInputValue(4, count) # 최근 500일치
self.objStockChart.SetInputValue(5, [0, 2, 3, 4, 5, 8]) # 요청항목 - 날짜,시가,고가,저가,종가,거래량
self.objStockChart.SetInputValue(6, dwm) # '차트 주기 - 일/주/월
self.objStockChart.SetInputValue(9, ord('1')) # 수정주가 사용
self.objStockChart.BlockRequest()
rqStatus = self.objStockChart.GetDibStatus()
rqRet = self.objStockChart.GetDibMsg1()
print("통신상태", rqStatus, rqRet)
if rqStatus != 0:
exit()
len = self.objStockChart.GetHeaderValue(3)
caller.dates = []
caller.opens = []
caller.highs = []
caller.lows = []
caller.closes = []
caller.vols = []
caller.times = []
caller.diff = []
caller.sign = []
for i in range(len):
caller.dates.append(self.objStockChart.GetDataValue(0, i)) # 0: 날짜(ulong)
caller.opens.append(self.objStockChart.GetDataValue(1, i)) # 1:시간(long) - hhmm
caller.highs.append(self.objStockChart.GetDataValue(2, i)) # 2:시가(long or float)
caller.lows.append(self.objStockChart.GetDataValue(3, i)) # 3:고가(long or float)
caller.closes.append(self.objStockChart.GetDataValue(4, i)) # 4:저가(long or float)
caller.vols.append(self.objStockChart.GetDataValue(5, i)) # 5:종가(long or float)
caller.diff.append(self.objStockChart.GetDataValue(6, i)) # 6:전일대비(long or float) - 주) 대비부호(37)과반드시같이요청해야함
caller.sign.append(chr(self.objStockChart.GetDataValue(37, i))) # 37:대비부호(char) - 수신값은 GetHeaderValue 8 대비부호와동일
print(len)
return
# 차트 요청 - 분간, 틱 차트
def RequestMTT(self, code, dwm, count, caller):
# plus 상태 체크
if com.InitPlusCheck() == False:
exit()
self.objStockChart.SetInputValue(0, code) # 종목코드
self.objStockChart.SetInputValue(1, ord('2')) # 개수로 받기
self.objStockChart.SetInputValue(4, count) # 조회 개수
self.objStockChart.SetInputValue(5, [0, 1, 2, 3, 4, 5, 8]) # 요청항목 - 날짜, 시간,시가,고가,저가,종가,거래량
self.objStockChart.SetInputValue(6, dwm) # '차트 주기 - 분/틱
self.objStockChart.SetInputValue(7, 1) # 분틱차트 주기
self.objStockChart.SetInputValue(9, ord('1')) # 수정주가 사용
totlen = 0
while True:
self.objStockChart.BlockRequest()
rqStatus = self.objStockChart.GetDibStatus()
rqRet = self.objStockChart.GetDibMsg1()
print("통신상태", rqStatus, rqRet)
if rqStatus != 0:
exit()
len = self.objStockChart.GetHeaderValue(3)
totlen += len
print('날짜', '시간', '시가', '고가', '저가', '종가', '거래량')
print("==============================================-")
caller.dates = []
caller.opens = []
caller.highs = []
caller.lows = []
caller.closes = []
caller.vols = []
caller.times = []
for i in range(len):
caller.dates.append(self.objStockChart.GetDataValue(0, i))
caller.times.append(self.objStockChart.GetDataValue(1, i))
caller.opens.append(self.objStockChart.GetDataValue(2, i))
caller.highs.append(self.objStockChart.GetDataValue(3, i))
caller.lows.append(self.objStockChart.GetDataValue(4, i))
caller.closes.append(self.objStockChart.GetDataValue(5, i))
caller.vols.append(self.objStockChart.GetDataValue(6, i))
print(totlen, len, self.objStockChart.Continue, caller.dates[i], caller.times[i])
if (self.objStockChart.Continue == False):
break
if (totlen >= 100000000):
break
# 차트 요청 - 분간, 틱 차트
def RequestMT(self, code, dwm, count, caller):
# plus 상태 체크
if com.InitPlusCheck() == False:
exit()
self.objStockChart.SetInputValue(0, code) # 종목코드
self.objStockChart.SetInputValue(1, ord('2')) # 개수로 받기
self.objStockChart.SetInputValue(4, count) # 조회 개수
self.objStockChart.SetInputValue(5, [0, 1, 2, 3, 4, 5, 8]) # 요청항목 - 날짜, 시간,시가,고가,저가,종가,거래량
self.objStockChart.SetInputValue(6, dwm) # '차트 주기 - 분/틱
self.objStockChart.SetInputValue(7, 1) # 분틱차트 주기
self.objStockChart.SetInputValue(9, ord('1')) # 수정주가 사용
self.objStockChart.BlockRequest()
rqStatus = self.objStockChart.GetDibStatus()
rqRet = self.objStockChart.GetDibMsg1()
print("통신상태", rqStatus, rqRet)
if rqStatus != 0:
exit()
len = self.objStockChart.GetHeaderValue(3)
caller.dates = []
caller.opens = []
caller.highs = []
caller.lows = []
caller.closes = []
caller.vols = []
caller.times = []
for i in range(len):
caller.dates.append(self.objStockChart.GetDataValue(0, i))
caller.times.append(self.objStockChart.GetDataValue(1, i))
caller.opens.append(self.objStockChart.GetDataValue(2, i))
caller.highs.append(self.objStockChart.GetDataValue(3, i))
caller.lows.append(self.objStockChart.GetDataValue(4, i))
caller.closes.append(self.objStockChart.GetDataValue(5, i))
caller.vols.append(self.objStockChart.GetDataValue(6, i))
#print(self.objStockChart.GetDataValue(0, i))
# df1 =pd.DataFrame("2018", columns=['일자'])
data = {"date" : caller.dates,
"times" : caller.times,
"closes" : caller.closes,
}
print(data)
df1 = pd.DataFrame(data, columns=['date','times','closes'])
print(df1)
print(len)
ma5 = df1['closes'].rolling(window=5).mean()
ma10 = df1['closes'].rolling(window=10).mean()
ma20 = df1['closes'].rolling(window=20).mean()
ma60 = df1['closes'].rolling(window=60).mean()
ma120 = df1['closes'].rolling(window=120).mean()
data_ma = {"ma5" : ma5,
"ma10" : ma10,
"ma20" : ma20,
}
print(data_ma)
df_ma = pd.DataFrame(data_ma, columns=['ma5','ma10','ma20'])
print(df_ma)
# 가격 비교
# 매수
# 체결내역확인
# 잔고확인
return
def __init__(self):
super().__init__()
# 기본 변수들
self.dates = []
self.opens = []
self.highs = []
self.lows = []
self.closes = []
self.vols = []
self.times = []
self.diff = []
self.sign = []
self.item = []
self.objChart = CpStockChart()
from_day = dt.today() - timedelta(days=7)
from_day = from_day.strftime("%Y%m%d")
today = dt.today().strftime("%Y%m%d")
# 윈도우 버튼 배치
self.setWindowTitle("PLUS API TEST")
nH = 20
self.codeEdit = QLineEdit("", self)
self.codeEdit.move(20, nH)
self.codeEdit.setText('005930')
self.codeEdit.textChanged.connect(self.codeEditChanged)
self.label = QLabel('종목코드', self)
self.label.move(140, nH)
nH += 50
btchart1= QPushButton("기간(일간) 요청", self)
btchart1.move(20, nH)
btchart1.clicked.connect(self.btchart1_clicked)
self.fromdtEdit = QLineEdit("", self)
self.fromdtEdit.setText(from_day)
self.fromdtEdit.move(140, nH)
self.todtEdit = QLineEdit("", self)
self.todtEdit.setText(today)
self.todtEdit.move(250, nH)
self.tickEdit = QLineEdit("", self)
self.tickEdit.setText("D")
self.tickEdit.move(350, nH)
self.fromdtEdit.textChanged.connect(self.fromdtEditChanged)
self.todtEdit.textChanged.connect(self.todtEditChanged)
self.tickEdit.textChanged.connect(self.tickEditChanged)
nH += 50
btchart2 = QPushButton("개수(일간) 요청", self)
btchart2.move(20, nH)
btchart2.clicked.connect(self.btchart2_clicked)
nH += 50
btchart3 = QPushButton("분차트 요청", self)
btchart3.move(20, nH)
btchart3.clicked.connect(self.btchart3_clicked)
nH += 50
btchart4 = QPushButton("틱차트 요청", self)
btchart4.move(20, nH)
btchart4.clicked.connect(self.btchart4_clicked)
nH += 50
btchart5 = QPushButton("주간차트 요청", self)
btchart5.move(20, nH)
btchart5.clicked.connect(self.btchart5_clicked)
nH += 50
btchart6 = QPushButton("월간차트 요청", self)
btchart6.move(20, nH)
btchart6.clicked.connect(self.btchart6_clicked)
nH += 50
btchart7 = QPushButton("엑셀로 저장", self)
btchart7.move(20, nH)
btchart7.clicked.connect(self.btchart7_clicked)
nH += 50
btnExit = QPushButton("종료", self)
btnExit.move(20, nH)
btnExit.clicked.connect(self.btnExit_clicked)
nH += 50
#####################################################################
# UI size
self.setGeometry(200, 400, 400, nH)
#####################################################################
# 초기값 설정
self.setCode('005930')
self.fromdt=self.fromdtEdit.text()
self.todt=self.todtEdit.text()
self.ticktp = self.tickEdit.text()
#####################################################################
# Tcp 통신
"""
self.config = ConfigParser()
self.config.read('config.ini')
order = self.config['ORDER']
print('order["IP"]', order["IP"], int(order["PORT"]))
ord_form = (order["IP"], int(order["PORT"]))
self.ord_sock = TcpClient.OrderPort(ord_form)
"""
#####################################################################
# Order
# plus 상태 체크
pythoncom.CoInitialize()
if com.InitPlusCheck() == False:
exit()
if com.InitTradeInit() == False:
exit()
self.objOdr = CpOdr.CpOrder()
self.acc_no = com.g_objCpTrade.AccountNumber[0] # 계좌번호
#####################################################################
# 체결
self.stkConData = CpOdr.StkConData()
self.objSBCon = CpOdr.CpSBConclusion()
self.objSBCon.Subscribe(self.stkConData, self)
#####################################################################
# logger
self.logger = logging.getLogger("my_setting")
self.logger = LoggerAdapter(CpChartWindow.__name__, self.logger)
self.logger.debug(f'CpChartWindow Start')
print("CpChartWindow Start")
class CpChartWindow(QMainWindow):
def __init__(self):
super().__init__()
# 기본 변수들
self.dates = []
self.opens = []
self.highs = []
self.lows = []
self.closes = []
self.vols = []
self.times = []
self.diff = []
self.sign = []
self.item = []
self.objChart = CpStockChart()
from_day = dt.today() - timedelta(days=7)
from_day = from_day.strftime("%Y%m%d")
today = dt.today().strftime("%Y%m%d")
# 윈도우 버튼 배치
self.setWindowTitle("PLUS API TEST")
nH = 20
self.codeEdit = QLineEdit("", self)
self.codeEdit.move(20, nH)
self.codeEdit.setText('005930')
self.codeEdit.textChanged.connect(self.codeEditChanged)
self.label = QLabel('종목코드', self)
self.label.move(140, nH)
nH += 50
btchart1= QPushButton("기간(일간) 요청", self)
btchart1.move(20, nH)
btchart1.clicked.connect(self.btchart1_clicked)
self.fromdtEdit = QLineEdit("", self)
self.fromdtEdit.setText(from_day)
self.fromdtEdit.move(140, nH)
self.todtEdit = QLineEdit("", self)
self.todtEdit.setText(today)
self.todtEdit.move(250, nH)
self.tickEdit = QLineEdit("", self)
self.tickEdit.setText("D")
self.tickEdit.move(350, nH)
self.fromdtEdit.textChanged.connect(self.fromdtEditChanged)
self.todtEdit.textChanged.connect(self.todtEditChanged)
self.tickEdit.textChanged.connect(self.tickEditChanged)
nH += 50
btchart2 = QPushButton("개수(일간) 요청", self)
btchart2.move(20, nH)
btchart2.clicked.connect(self.btchart2_clicked)
nH += 50
btchart3 = QPushButton("분차트 요청", self)
btchart3.move(20, nH)
btchart3.clicked.connect(self.btchart3_clicked)
nH += 50
btchart4 = QPushButton("틱차트 요청", self)
btchart4.move(20, nH)
btchart4.clicked.connect(self.btchart4_clicked)
nH += 50
btchart5 = QPushButton("주간차트 요청", self)
btchart5.move(20, nH)
btchart5.clicked.connect(self.btchart5_clicked)
nH += 50
btchart6 = QPushButton("월간차트 요청", self)
btchart6.move(20, nH)
btchart6.clicked.connect(self.btchart6_clicked)
nH += 50
btchart7 = QPushButton("엑셀로 저장", self)
btchart7.move(20, nH)
btchart7.clicked.connect(self.btchart7_clicked)
nH += 50
btnExit = QPushButton("종료", self)
btnExit.move(20, nH)
btnExit.clicked.connect(self.btnExit_clicked)
nH += 50
#####################################################################
# UI size
self.setGeometry(200, 400, 400, nH)
#####################################################################
# 초기값 설정
self.setCode('005930')
self.fromdt=self.fromdtEdit.text()
self.todt=self.todtEdit.text()
self.ticktp = self.tickEdit.text()
#####################################################################
# Tcp 통신
"""
self.config = ConfigParser()
self.config.read('config.ini')
order = self.config['ORDER']
print('order["IP"]', order["IP"], int(order["PORT"]))
ord_form = (order["IP"], int(order["PORT"]))
self.ord_sock = TcpClient.OrderPort(ord_form)
"""
#####################################################################
# Order
# plus 상태 체크
pythoncom.CoInitialize()
if com.InitPlusCheck() == False:
exit()
if com.InitTradeInit() == False:
exit()
self.objOdr = CpOdr.CpOrder()
self.acc_no = com.g_objCpTrade.AccountNumber[0] # 계좌번호
#####################################################################
# 체결
self.stkConData = CpOdr.StkConData()
self.objSBCon = CpOdr.CpSBConclusion()
self.objSBCon.Subscribe(self.stkConData, self)
#####################################################################
# logger
self.logger = logging.getLogger("my_setting")
self.logger = LoggerAdapter(CpChartWindow.__name__, self.logger)
self.logger.debug(f'CpChartWindow Start')
print("CpChartWindow Start")
# server 에서만 사용
# input_ready, write_ready, except_ready = select.select(input_list, [], [])
def recvloop(self):
while True:
try:
pythoncom.CoInitialize()
data = ''
data = self.ord_sock.recv_pckt()
print("recvloop", data[0], data[1], data[2], data[3])
# 주문로직
# def buyOrder(self, acc_no, item, qty, price, odata):
item = data[1]
qty = 1
prc = data[3]
ord_no = ''
bResult = self.objOdr.buyOrder(self.acc_no, item, qty, prc, ord_no)
if bResult == False:
print("주문 실패")
return
except Exception as e:
print("except Exception as e", e)
break
# 기간(일간) 으로 받기
def btchart1_clicked(self):
pythoncom.CoInitialize()
print("def btchart1_clicked(self) ", self.ticktp, self.code, self.fromdt, self.todt)
if self.ticktp == 'D':
if self.objChart.RequestFromTo(self.code, self.fromdt, self.todt, self) == False:
exit()
# 분봉 데이터
if self.ticktp == 'M':
if self.objChart.RequestMTT(self.code, ord('m'), 100, self) == False:
exit()
# 틱 데이터
if self.ticktp == 'T':
if self.objChart.RequestMTT(self.code, ord('T'), 100, self) == False:
exit()
self.stkDataHist = CpData.StkDataHist()
self.stkDataHist.ymd = self.dates
self.stkDataHist.item = self.item
for i in range(len(self.dates)):
self.stkDataHist.item_nm.append(self.item_nm)
self.stkDataHist.open = self.opens
self.stkDataHist.high = self.highs
self.stkDataHist.low = self.lows
self.stkDataHist.close = self.closes
self.stkDataHist.acc_vol = self.vols
self.stkDataHist.diff = self.diff
print("def btchart1_clicked(self) ", self.code, self.item_nm, len(self.dates))
self.cpdb = CpDB.CpDB()
self.con = self.cpdb.connect()
self.cpdb.stkhistprc(self.con, 'I', self.stkDataHist)
#####################################################################
# 호가 데이터 TCP 통신
"""
for i in range(len(self.dates)):
send_list = [ self.stkDataHist.item[i],
self.stkDataHist.open[i],
self.stkDataHist.ymd[i],
self.stkDataHist.high[i],
self.stkDataHist.close[i] ]
req_data = {
gd.KEY_NM_EVT: gd.EVT_TYPE_GET_KP200_FUT,
gd.KEY_NM_DATA: send_list
}
#self.objTcpClient.tcpClient(req_data)
self.ord_sock.send_pckt(req_data)
time.sleep(3)
#data = self.ord_sock.recv_pckt()
#print(data.decode())
"""
# 개수(일간) 으로 받기
def btchart2_clicked(self):
if self.objChart.RequestDWM(self.code, ord('D'), 100, self) == False:
exit()
# 분차트 받기
def btchart3_clicked(self):
if self.objChart.RequestMTT(self.code, ord('m'), 100, self) == False:
exit()
# if self.objChart.RequestMT(self.code, ord('m'), 10000, self) == False:
# exit()
# 틱차트 받기
def btchart4_clicked(self):
if self.objChart.RequestMTT(self.code, ord('T'), 100, self) == False:
exit()
# if self.objChart.RequestMT(self.code, ord('T'), 500, self) == False:
# exit()
# 주간차트
def btchart5_clicked(self):
if self.objChart.RequestDWM(self.code, ord('W'), 100, self) == False:
exit()
# 월간차트
def btchart6_clicked(self):
if self.objChart.RequestDWM(self.code, ord('M'), 100, self) == False:
exit()
def btchart7_clicked(self):
charfile = 'chart.xlsx'
print("btchart7_clicked")
print(self.dates, len(self.dates))
for i in range(len(self.dates)):
print(self.dates[i])
if (len(self.times) == 0):
chartData = {'일자' : self.dates,
'시가' : self.opens,
'고가' : self.highs,
'저가' : self.lows,
'종가' : self.closes,
'거래량' : self.vols,
'전일대비' : self.diff,
'부호' : self.sign,
}
df =pd.DataFrame(chartData, columns=['일자','시가','고가','저가','종가','거래량','전일대비','부호'])
else:
chartData = {'일자' : self.dates,
'시간' : self.times,
'시가' : self.opens,
'고가' : self.highs,
'저가' : self.lows,
'종가' : self.closes,
'전일대비' : self.diff,
'거래량' : self.vols,
'부호' : self.sign,
}
df=pd.DataFrame(chartData, columns=['일자','시간','시가','고가','저가','종가','거래량','전일대비','부호'])
df = df.set_index('일자')
# create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter(charfile, engine='xlsxwriter')
# Convert the dataframe to an XlsxWriter Excel object.
df.to_excel(writer, sheet_name='Sheet1')
# Close the Pandas Excel writer and output the Excel file.
writer.save()
os.startfile(charfile)
return
def tickEditChanged(self):
self.ticktp = self.tickEdit.text()
print("def tickEditChanged(self)", self.ticktp)
def fromdtEditChanged(self):
self.fromdt = self.fromdtEdit.text()
print("def fromdtEditChanged(self)", self.fromdt)
def todtEditChanged(self):
self.todt= self.todtEdit.text()
def codeEditChanged(self):
code = self.codeEdit.text()
self.setCode(code)
print("codeEditChanged", code)
def setCode(self, code):
if len(code) < 6:
return
print(code)
if not (code[0] == "A"):
code = "A" + code
name = com.g_objCodeMgr.CodeToName(code)
if len(name) == 0:
print("종목코드 확인")
return
self.label.setText(name)
self.item_nm = name
self.code = code
def btnExit_clicked(self):
exit()
class CpDB:
def __init__(self):
super().__init__()
self.ts = time.time()
self.ymd = datetime.datetime.fromtimestamp(self.ts).strftime('%Y%m%d')
self.timestamp = datetime.datetime.fromtimestamp(
self.ts).strftime('%Y-%m-%d %H:%M:%S.%f')
self.logger = logging.getLogger("my_setting")
self.logger = LoggerAdapter(CpDB.__name__, self.logger)
# db 정보 초기화
config = ConfigParser()
config.read('setting/init.ini')
db = config['db']
self.logger.debug(
f'db: {db["USR"]} {db["URL"]} {int(db["PORT"])} {db["DB"]}')
self.db_info = {
DataBase.USR: db["USR"],
DataBase.PWD: db["PW"],
DataBase.URL: db["URL"],
DataBase.PORT: int(db["PORT"]),
DataBase.DB: db["DB"]
}
###############################################################################
# Connect
os.environ["NLS_LANG"] = ".AL32UTF8"
hostname = os.environ['COMPUTERNAME']
self.logger.debug(f'db: {self.db_info[DataBase.USR]}')
# Open database connection
self.con = pymysql.connect(host=self.db_info[DataBase.URL],
port=self.db_info[DataBase.PORT],
user=self.db_info[DataBase.USR],
passwd=self.db_info[DataBase.PWD],
db=self.db_info[DataBase.DB],
charset='utf8',
autocommit=False)
# Connection 으로부터 Dictoionary Cursor 생성
self.cur = self.con.cursor(pymysql.cursors.DictCursor)
def connect(self):
con = pymysql.connect(host=self.db_info[DataBase.URL],
port=self.db_info[DataBase.PORT],
user=self.db_info[DataBase.USR],
passwd=self.db_info[DataBase.PWD],
db=self.db_info[DataBase.DB],
charset='utf8',
autocommit=False)
return con
def stkhistprc(self, icon, tp, idata):
if tp == 'I':
print("*******************************************************")
print("def STKHISTPRC(self, tp, idata, odata): ")
print("*******************************************************")
sql = """INSERT INTO TRD.STKHISTPRC
(YMD, ITEM, OPEN, HIGH,
LOW, CLOSE, ACC_VOL, DIFF,
item_nm
)
VALUES
(%s, %s, %s, %s,
%s, %s, %s, %s,
%s
)
"""
cnt = len(idata.ymd)
print("def STKHISTPRC(self, tp, idata, odata): > ", cnt, idata.ymd)
idatam = []
for i in range(cnt):
idatam.append(
(idata.ymd[i], idata.item[i], idata.open[i], idata.high[i],
idata.low[i], idata.close[i], idata.acc_vol[i],
idata.diff[i], idata.item_nm[i]))
try:
with icon.cursor() as cur:
cur.executemany(sql, idatam)
except:
icon.rollback()
icon.commit()
return
if tp == 'S':
print("*******************************************************")
print("def STKHISTPRC Select ")
print("*******************************************************")
sql = """SELECT YMD DATE, OPEN, HIGH, LOW, close, ACC_VOL VOLUME
FROM TRD.STKHISTPRC
WHERE YMD BETWEEN '20110714' AND '20110720'
ORDER BY YMD
"""
odata = pd.read_sql_query(sql, icon)
print(odata)
"""
try:
with self.con.cursor() as self.cur:
self.cur.execute(sql)
# 데이타 Fetch
rows = self.cur.fetchall()
for date, open, high, low, close, volume in rows:
odata.ymd.append(date)
odata.open.append(open)
odata.high.append(high)
odata.low.append(low)
odata.close.append(close)
odata.acc_vol.append(volume)
except:
self.icon.rollback()
self.icon.commit()
"""
return odata
def futchartdata(self, icon, tp, idata, odata):
if tp == 'I':
print("*******************************************************")
print("def futchartdata(self, icon, tp, idata, odata): ")
print("*******************************************************")
sql = """INSERT INTO TRD.FUTCHARTDATA
(YMD , ITEM , HHMM , TM_TP ,
OPEN , HIGH , LOW , CLOSE , ACC_VOL
)
VALUES
(%s, %s, %s, %s,
%s, %s, %s, %s, %s
)
"""
cnt = len(idata.ymd)
print("def futchartdata(self, icon, tp, idata, odata): > ", cnt)
idatam = []
for i in range(cnt):
idatam.append((idata.ymd[i], idata.item[i], idata.hhmm[i],
idata.tm_tp[i], idata.open[i], idata.high[i],
idata.low[i], idata.close[i], idata.acc_vol[i]))
try:
with icon.cursor() as cur:
cur.executemany(sql, idatam)
except icon.Error as error:
print(
"*******************************************************")
print("Error: {}", error)
print(
"*******************************************************")
icon.rollback()
icon.commit()
def tivtt(self, icon, tp, idata, odata):
if tp == 'I':
print("*******************************************************")
print("def tivtt(self, tp, idata, odata): ")
print("*******************************************************")
sql = """INSERT INTO TRD.TIVTT
(MKT_TP , MKT_NM , IVT_TP , IVT_NM , TIME ,
ASK_QTY , ASK_AMT , BID_QTY , BID_AMT ,
NET_BID_QTY , NET_BID_AMT
)
VALUES
(%s, %s, %s, %s, %s,
%s, %s, %s, %s,
%s, %s
)
"""
cnt = len(idata.mkt_tp)
print("def tivtt(self, tp, idata, odata): > ", cnt)
idatam = []
for i in range(cnt):
idatam.append(
(idata.mkt_tp[i], idata.mkt_nm[i], idata.ivt_tp[i],
idata.ivt_nm[i], idata.time[i], idata.ask_qty[i],
idata.ask_amt[i], idata.bid_qty[i], idata.bid_amt[i],
idata.net_bid_qty[i], idata.net_bid_amt[i]))
try:
with icon.cursor() as cur:
cur.executemany(sql, idatam)
except icon.Error as error:
print(
"*******************************************************")
print("Error: {}", error)
print(
"*******************************************************")
icon.rollback()
icon.commit()
def stkindt(self, tp, idata, odata):
if tp == 'I':
print("*******************************************************")
print("def stkindt(self, tp, idata, odata): ")
print("*******************************************************")
sql = """INSERT INTO TRD.STKINDT
(YMD , TP , TERM , ITEM ,
MA5 , MA10 , MA20 , MA60 , MA120 , MA240 ,
VOL_AVG , VOL_STD
)
VALUES
(%s, %s, %s, %s,
%s, %s, %s, %s, %s, %s,
%s, %s
)
"""
cnt = len(idata.ymd)
print("def stkindt(self, tp, idata, odata): > ", cnt, idata.ymd)
idatam = []
idatam.append(
(idata.ymd, idata.tp, idata.term, idata.item, idata.ma5,
idata.ma10, idata.ma20, idata.ma60, idata.ma120, idata.ma240,
idata.vol_avg, idata.vol_std))
try:
with self.con.cursor() as self.cur:
self.cur.executemany(sql, idatam)
except self.con.Error as error:
print(
"*******************************************************")
print("Error: {}", error)
print(
"*******************************************************")
self.con.rollback()
self.con.commit()
def stkidxmst(self, tp, icon, idata, odata):
if tp == 'S':
sql = """SELECT ITEM
FROM TRD.STKIDXMST
"""
try:
with icon.cursor() as cur:
cur.execute(sql)
odata.item = []
# 데이타 Fetch
rows = cur.fetchall()
for item in rows:
odata.item.append(item)
except icon.Error as error:
print(
"*******************************************************")
print("Error: {}".format(error))
print(
"*******************************************************")
icon.rollback()
icon.commit()
if tp == 'I':
print("*******************************************************")
print("def stkidxmst(self, tp, icon, idata, odata): ", idata.item,
idata.item_nm)
print("*******************************************************")
sql = """INSERT INTO TRD.STKIDXMST
(ITEM , ITEM_NM
)
VALUES
(%s, %s
)
"""
idatam = []
idatam.append((idata.item, idata.item_nm))
try:
with icon.cursor() as cur:
cur.executemany(sql, idatam)
except icon.Error as error:
print(
"*******************************************************")
print("Error: {}", error)
print(
"*******************************************************")
icon.rollback()
icon.commit()
return
def stkidxd(self, tp, icon, idata, odata):
if tp == 'I':
cnt = len(idata.ymd)
print("*******************************************************")
print("def stkidxd(self, tp, idata, odata): ", idata.ymd,
idata.item, idata.item_nm)
print("*******************************************************")
sql = """INSERT INTO TRD.STKIDXD
(YMD , ITEM , ITEM_NM ,
BF_CLOSE , CLOSE , DIFF , OPEN , HIGH , LOW ,
ACC_VOL
)
VALUES
(%s, %s, %s,
%s, %s, %s, %s, %s, %s,
%s
)
"""
idatam = []
idatam.append((idata.ymd, idata.item, idata.item_nm,
idata.bf_close, idata.close, idata.diff, idata.open,
idata.high, idata.low, idata.acc_vol))
try:
with icon.cursor() as cur:
cur.executemany(sql, idatam)
except icon.Error as error:
print(
"*******************************************************")
print("Error: {}", error)
print(
"*******************************************************")
icon.rollback()
icon.commit()
print("icon.commit()", cur.lastrowid)
return
def stkidxt(self, tp, icon, idata, odata):
if tp == 'I':
print("*******************************************************")
print("def stkidxt(self, tp, idata, odata): ")
print("*******************************************************")
sql = """INSERT INTO TRD.STKIDXT
(YMD , ITEM , ITEM_NM ,
BF_CLOSE , CLOSE , DIFF , OPEN , HIGH , LOW ,
ACC_VOL, TIME
)
VALUES
(%s, %s, %s,
%s, %s, %s, %s, %s, %s,
%s, %s
)
"""
cnt = len(idata.ymd)
print("def stkindt(self, tp, idata, odata): > ", cnt, idata.ymd)
idatam = []
idatam.append((idata.ymd, idata.item, idata.item_nm,
idata.bf_close, idata.close, idata.diff, idata.open,
idata.high, idata.low, idata.acc_vol, idata.time))
# Open database connection
#con = pymysql.connect(host='localhost', port=3306, user='******', passwd='root', db='MySql', charset='utf8', autocommit=False)
# Connection 으로부터 Dictoionary Cursor 생성
#cur = con.cursor(pymysql.cursors.DictCursor)
try:
with icon.cursor() as cur:
cur.executemany(sql, idatam)
except icon.Error as error:
print(
"*******************************************************")
print("Error: {}", error)
print(
"*******************************************************")
icon.rollback()
return
icon.commit()
return
def stkodr(self, tp, idata, odata):
if tp == 'I':
sql = """INSERT INTO TRD.STKODR
(YMD ,ACC_NO , ITEM ,
ODR_NO ,QTY , PRC ,
ODR_TP ,COND_TP , ODR_TICK_TP
)
VALUES
(%s, %s, %s,
%s, %s, %s,
%s, %s, %s
)
"""
cnt = len(idata.ymd)
print("def stkodr(self, tp, idata, odata): > ", cnt, idata.ymd)
idatam = []
idatam.append(
(idata.ymd, idata.acc_no, idata.item, idata.odr_no, idata.qty,
idata.prc, idata.odr_tp, idata.cond_tp, idata.odr_tick_tp))
try:
with self.con.cursor() as self.cur:
self.cur.executemany(sql, idatam)
except:
self.con.rollback()
self.con.commit()
if tp == 'U':
sql = """UPDATE TRD.STKODR
SET CON_QTY = %s
, CON_PRC = %s
WHERE YMD = %s
AND ITEM = %s
AND ACC_NO = %s
AND ODR_NO = %s
"""
try:
with self.con.cursor() as self.cur:
self.cur.execute(sql,
(idata.con_qty, idata.con_prc, idata.ymd,
idata.item, idata.acc_no, idata.odr_no))
except:
self.con.rollback()
self.con.commit()
def stkcurt(self, tp, idata, odata):
if tp == 'I':
sql = """INSERT INTO TRD.STKCURT
(YMD ,ITEM ,
OPEN ,HIGH , LOW ,
CLOSE ,DIFF , VOL ,
ACC_VOL ,AMT , ODR_TP ,
PRC_SIGN ,
TIME , TIMES
)
VALUES
(%s, %s,
%s, %s, %s,
%s, %s, %s,
%s, %s, %s,
%s,
%s, %s
)
"""
print("def stkcurt(self, tp, idata, odata):", idata.ymd,
idata.item, idata.close, idata.odr_tp)
idatam = []
idatam.append(
(idata.ymd, idata.item, idata.open, idata.high, idata.low,
idata.close, idata.diff, idata.vol, idata.acc_vol, idata.amt,
idata.odr_tp, idata.prc_sign, idata.time, idata.times))
try:
with self.con.cursor() as self.cur:
self.cur.executemany(sql, idatam)
except self.con.Error as error:
print(
"*******************************************************")
print("Error: {}", error)
print(
"*******************************************************")
os._exit(1)
self.con.rollback()
self.con.commit()
print(self.cur.lastrowid)
return
def stktick(self, tp, idata, odata):
if tp == 'I':
sql = """INSERT INTO TRD.STKTICK
(YMD , ITEM , ITEM_NM ,
TIME , ACC_VOL ,
ASK_QTY10 , ASK_QTY9 , ASK_QTY8 , ASK_QTY7 , ASK_QTY6 ,
ASK_QTY5 , ASK_QTY4 , ASK_QTY3 , ASK_QTY2 , ASK_QTY1 ,
ASK_PRC10 , ASK_PRC9 , ASK_PRC8 , ASK_PRC7 , ASK_PRC6 ,
ASK_PRC5 , ASK_PRC4 , ASK_PRC3 , ASK_PRC2 , ASK_PRC1 ,
BID_QTY10 , BID_QTY9 , BID_QTY8 , BID_QTY7 , BID_QTY6 ,
BID_QTY5 , BID_QTY4 , BID_QTY3 , BID_QTY2 , BID_QTY1 ,
BID_PRC10 , BID_PRC9 , BID_PRC8 , BID_PRC7 , BID_PRC6 ,
BID_PRC5 , BID_PRC4 , BID_PRC3 , BID_PRC2 , BID_PRC1 ,
ASK_TOT_QTY , BID_TOT_QTY , EXTIME_ASK_TOT_QTY, EXTIME_BID_TOT_QTY ,
ASK_VWAP , BID_VWAP , MID_VWAP , MID_PRC , PRC_DIFF
)
VALUES
(%s, %s, %s,
%s, %s,
%s, %s, %s, %s, %s,
%s, %s, %s, %s, %s,
%s, %s, %s, %s, %s,
%s, %s, %s, %s, %s,
%s, %s, %s, %s, %s,
%s, %s, %s, %s, %s,
%s, %s, %s, %s, %s,
%s, %s, %s, %s, %s,
%s, %s, %s, %s,
%s, %s, %s, %s, %s
)
"""
idatam = []
idatam.append(
(idata.ymd, idata.item, idata.item_nm, idata.time,
idata.acc_vol, idata.ask_qty[9], idata.ask_qty[8],
idata.ask_qty[7], idata.ask_qty[6], idata.ask_qty[5],
idata.ask_qty[4], idata.ask_qty[3], idata.ask_qty[2],
idata.ask_qty[1], idata.ask_qty[0], idata.ask_prc[9],
idata.ask_prc[8], idata.ask_prc[7], idata.ask_prc[6],
idata.ask_prc[5], idata.ask_prc[4], idata.ask_prc[3],
idata.ask_prc[2], idata.ask_prc[1], idata.ask_prc[0],
idata.bid_qty[9], idata.bid_qty[8], idata.bid_qty[7],
idata.bid_qty[6], idata.bid_qty[5], idata.bid_qty[4],
idata.bid_qty[3], idata.bid_qty[2], idata.bid_qty[1],
idata.bid_qty[0], idata.bid_prc[9], idata.bid_prc[8],
idata.bid_prc[7], idata.bid_prc[6], idata.bid_prc[5],
idata.bid_prc[4], idata.bid_prc[3], idata.bid_prc[2],
idata.bid_prc[1], idata.bid_prc[0], idata.ask_tot_qty,
idata.bid_tot_qty, idata.extime_ask_tot_qty,
idata.extime_bid_tot_qty, idata.ask_vwap, idata.bid_vwap,
idata.mid_vwap, idata.mid_prc, idata.prc_diff))
try:
with self.con.cursor() as self.cur:
self.cur.executemany(sql, idatam)
except self.con.Error as error:
print(
"*******************************************************")
print("Error: {}", error)
print(
"*******************************************************")
self.con.rollback()
self.con.commit()
return
def futtick(self, tp, idata, odata):
if tp == 'I':
sql = """INSERT INTO TRD.FUTTICK
(YMD , ITEM , ITEM_NM ,
TIME , ACC_VOL ,
ASK_NUM5 , ASK_NUM4 , ASK_NUM3 , ASK_NUM2 , ASK_NUM1 ,
ASK_QTY5 , ASK_QTY4 , ASK_QTY3 , ASK_QTY2 , ASK_QTY1 ,
ASK_PRC5 , ASK_PRC4 , ASK_PRC3 , ASK_PRC2 , ASK_PRC1 ,
BID_PRC5 , BID_PRC4 , BID_PRC3 , BID_PRC2 , BID_PRC1 ,
BID_QTY5 , BID_QTY4 , BID_QTY3 , BID_QTY2 , BID_QTY1 ,
BID_NUM5 , BID_NUM4 , BID_NUM3 , BID_NUM2 , BID_NUM1 ,
ASK_TOT_NUM , ASK_TOT_QTY , BID_TOT_NUM , BID_TOT_QTY , MKT_STAT_TP ,
ASK_VWAP , BID_VWAP , MID_VWAP , MID_PRC , PRC_DIFF
)
VALUES
(%s, %s, %s,
%s, %s,
%s, %s, %s, %s, %s,
%s, %s, %s, %s, %s,
%s, %s, %s, %s, %s,
%s, %s, %s, %s, %s,
%s, %s, %s, %s, %s,
%s, %s, %s, %s, %s,
%s, %s, %s, %s, %s,
%s, %s, %s, %s, %s
)
"""
idatam = []
idatam.append(
(idata.ymd, idata.item, idata.item_nm, idata.time,
idata.acc_vol, idata.ask_num[4], idata.ask_num[3],
idata.ask_num[2], idata.ask_num[1], idata.ask_num[0],
idata.ask_qty[4], idata.ask_qty[3], idata.ask_qty[2],
idata.ask_qty[1], idata.ask_qty[0], idata.ask_prc[4],
idata.ask_prc[3], idata.ask_prc[2], idata.ask_prc[1],
idata.ask_prc[0], idata.bid_prc[4], idata.bid_prc[3],
idata.bid_prc[2], idata.bid_prc[1], idata.bid_prc[0],
idata.bid_qty[4], idata.bid_qty[3], idata.bid_qty[2],
idata.bid_qty[1], idata.bid_qty[0], idata.bid_num[4],
idata.bid_num[3], idata.bid_num[2], idata.bid_num[1],
idata.bid_num[0], idata.ask_tot_num, idata.ask_tot_qty,
idata.bid_tot_qty, idata.bid_tot_num, idata.mkt_stat_tp,
idata.ask_vwap, idata.bid_vwap, idata.mid_vwap, idata.mid_prc,
idata.prc_diff))
print("def futtick(self, tp, idata, odata):", idata.ymd,
idata.item, idata.ask_vwap, idata.bid_vwap, idata.mid_vwap,
idata.mid_prc, idata.prc_diff)
try:
with self.con.cursor() as self.cur:
self.cur.executemany(sql, idatam)
except self.con.Error as error:
print(
"*******************************************************")
print("Error: {}", error)
print(
"*******************************************************")
self.con.rollback()
self.con.commit()
return
def futcurt(self, tp, idata, odata):
if tp == 'I':
sql = """INSERT INTO TRD.FUTCURT
(YMD ,ITEM ,
OPEN ,HIGH , LOW ,
CLOSE ,DIFF ,
ACC_VOL ,
PRC_SIGN ,
TIME ,
K200_IDX ,
OPEN_INTEREST , FST_OFFER_PRC , FST_BID_PRC , FST_OFFER_VOL , FST_BID_VOL ,
ACC_OFFER_VOL , ACC_BID_VOL
)
VALUES
(%s, %s,
%s, %s, %s,
%s, %s,
%s,
%s,
%s,
%s,
%s, %s, %s, %s, %s,
%s, %s
)
"""
print("def futcurt(self, tp, idata, odata):", idata.ymd,
idata.item, idata.close)
idatam = []
idatam.append(
(idata.ymd, idata.item, idata.open, idata.high, idata.low,
idata.close, idata.diff, idata.acc_vol, idata.prc_sign,
idata.time, idata.k200_idx, idata.open_interest,
idata.fst_offer_prc, idata.fst_bid_prc, idata.fst_offer_vol,
idata.fst_bid_vol, idata.acc_offer_vol, idata.acc_bid_vol))
try:
with self.con.cursor() as self.cur:
self.cur.executemany(sql, idatam)
except self.con.Error as error:
print(
"*******************************************************")
print("Error: {}", error)
print(
"*******************************************************")
self.con.rollback()
self.con.commit()
return
def stkmst(self, tp, idata, odata):
print("def stkmst(self, tp, idata, odata):", tp)
if tp == 'I':
sql = """DELETE FROM TRD.STKMST
"""
try:
with self.con.cursor() as self.cur:
self.cur.execute(sql)
except self.con.Error as error:
print(
"*******************************************************")
print("Error: {}".format(error))
print(
"*******************************************************")
self.con.rollback()
return
sql = """INSERT INTO TRD.STKMST
(ITEM, ITEM_NM, STK_TP, BASE_ITEM, BASE_ITEM_NM
)
VALUES
(%s, %s, %s, %s, %s
)
"""
cnt = len(idata.item)
print("def stkmst(self, tp, idata, odata):", cnt)
idatam = []
for i in range(cnt):
idatam.append(
(idata.item[i], idata.item_nm[i], idata.stk_tp[i],
idata.base_item[i], idata.base_item_nm[i]))
try:
with self.con.cursor() as self.cur:
self.cur.executemany(sql, idatam)
except self.con.Error as error:
print(
"*******************************************************")
print("Error: {}".format(error))
print(
"*******************************************************")
self.con.rollback()
return
self.con.commit()
def stkblnc(self, tp, idata, odata):
cnt = len(idata.acc_no)
for i in range(cnt):
print("STKBLNC idata.ymd", self.ymd, idata.acc_no[i],
idata.item[i], idata.con_qty[i], idata.td_qty[i],
idata.con_prc[i], idata.val_amt[i])
if tp == 'I':
sql = """DELETE FROM TRD.STKBLNC
WHERE YMD = %s
"""
try:
with self.con.cursor() as self.cur:
self.cur.execute(sql, (self.ymd))
except:
self.con.rollback()
print(
"*******************************************************")
print("Error: {}".format(error))
print(
"*******************************************************")
return
sql = """INSERT INTO TRD.STKBLNC
(ACC_NO, ITEM, ITEM_NM,
CON_QTY, CON_PRC, TD_QTY, YD_QTY,
VAL_AMT, PL_AMT
)
VALUES
(%s, %s, %s,
%s, %s, %s, %s,
%s, %s
)
"""
cnt = len(idata.acc_no)
idatam = []
for i in range(cnt):
idatam.append(
(idata.acc_no[i], idata.item[i], idata.item_nm[i],
idata.con_qty[i], idata.con_prc[i], idata.td_qty[i],
idata.yd_qty[i], idata.val_amt[i], idata.pl_amt[i]))
try:
with self.con.cursor() as self.cur:
self.cur.executemany(sql, idatam)
except:
self.con.rollback()
print(
"*******************************************************")
print("Error: {}".format(error))
print(
"*******************************************************")
return
self.con.commit()
if tp == 'S':
sql = """SELECT ACC_NO, ITEM, ITEM_NM, QTY, TD_QTY, YD_QTY, CON_PRC, AMT, VAL_AMT, PL_AMT
FROM TRD.STKBLNC
ORDER BY ITEM
"""
try:
with self.con.cursor() as self.cur:
self.cur.execute(sql)
# 데이타 Fetch
rows = self.cur.fetchall()
for acc_no, item, item_nm, qty, td_qty, yd_qty, con_prc, amt, val_amt, pl_amt in rows:
odata.acc_no.append(acc_no)
odata.item.append(item)
odata.item_nm.append(item_nm)
odata.qty.append(qty)
odata.td_qty.append(td_qty)
odata.yd_qty.append(yd_qty)
odata.con_prc.append(con_prc)
odata.amt.append(amt)
odata.val_amt.append(val_amt)
odata.pl_amt.append(pl_amt)
except:
self.con.rollback()
self.con.commit()
return
def stkcon(self, tp, idata, odata):
if tp == 'S':
sql = """SELECT YMD ,RTIME ,ACC_NO ,STRTGY_NO,ITEM ,
ODR_NO ,ORG_NO ,CON_QTY ,CON_PRC ,ODR_TP ,
CON_TP ,CNCL_TP ,SHORT_QTY,BLNC_QTY ,ODR_QTY ,
ODR_NM ,CON_NM ,
ODR_PRC ,ACC_NM ,ITEM_NM ,MTIME
FROM TRD.STKCON WHERE YMD = %s
ORDER BY RTIME DESC
"""
try:
with self.con.cursor() as self.cur:
self.cur.execute(sql, (self.ymd))
odata.ymd = []
odata.rtime = []
odata.acc_no = []
odata.strtgy_no = []
odata.item = []
odata.odr_no = []
odata.org_no = []
odata.con_qty = []
odata.con_prc = []
odata.odr_tp = []
odata.con_tp = []
odata.cncl_tp = []
odata.short_qty = []
odata.blnc_qty = []
odata.odr_qty = []
odata.odr_prc = []
odata.acc_nm = []
odata.item_nm = []
odata.mtime = []
# 데이타 Fetch
rows = self.cur.fetchall()
for ymd ,rtime ,acc_no ,strtgy_no,item , \
odr_no ,org_no ,con_qty ,con_prc ,odr_tp , \
con_tp ,cncl_tp ,short_qty,blnc_qty ,odr_qty , \
odr_prc ,acc_nm ,item_nm ,mtime in rows:
odata.ymd.append(ymd)
odata.rtime.append(rtime)
odata.acc_no.append(acc_no)
odata.strtgy_no.append(strtgy_no)
odata.item.append(item)
odata.odr_no.append(odr_no)
odata.org_no.append(org_no)
odata.con_qty.append(con_qty)
odata.con_prc.append(con_prc)
odata.odr_tp.append(odr_tp)
odata.con_tp.append(con_tp)
odata.cncl_tp.append(cncl_tp)
odata.short_qty.append(short_qty)
odata.blnc_qty.append(blnc_qty)
odata.odr_qty.append(odr_qty)
odata.odr_prc.append(odr_prc)
odata.acc_nm.append(acc_nm)
odata.item_nm.append(item_nm)
odata.mtime.append(mtime)
except self.con.Error as error:
print(
"*******************************************************")
print("Error: {}".format(error))
print(
"*******************************************************")
if tp == 'I':
print("STKCON idata.ymd", self.ymd)
sql = """INSERT INTO TRD.STKCON
(YMD ,ACC_NO ,ITEM ,
ODR_NO ,ORG_NO ,CON_QTY ,CON_PRC ,ODR_TP ,
CON_TP ,CNCL_TP ,SHORT_QTY,BLNC_QTY ,ODR_QTY ,
ODR_NM , CON_NM
)
VALUES
(%s, %s,%s,
%s, %s,%s, %s, %s,
%s, %s,%s, %s, %s,
%s, %s
)
"""
idatam = []
idatam.append(
(self.ymd, idata.acc_no, idata.item, idata.odr_no,
idata.org_no, idata.con_qty, idata.con_prc, idata.odr_tp,
idata.con_tp, idata.cncl_tp, idata.short_qty, idata.blnc_qty,
idata.odr_qty, idata.odr_nm, idata.con_nm))
try:
with self.con.cursor() as self.cur:
self.cur.executemany(sql, (idatam))
except self.con.Error as error:
print(
"*******************************************************")
print("Error: {}".format(error))
print(
"*******************************************************")
self.con.rollback()
self.con.commit()
# sql = """INSERT INTO TRD.STKCON
# (YMD ,ACC_NO ,ITEM ,
# ODR_NO ,ORG_NO ,CON_QTY ,CON_PRC ,ODR_TP ,
# CON_TP ,CNCL_TP ,SHORT_QTY,BLNC_QTY ,ODR_QTY ,
# ODR_NM , CON_NM
# )
# VALUES
# (%s, %s,%s,
# %s, %s,%s, %s, %s,
# %s, %s,%s, %s, %s,
# %s, %s
# )
# """
# try:
# with con.cursor() as cur:
# cur.execute(sql, (idata.ymd ,idata.acc_no ,idata.item ,
# idata.odr_no ,idata.org_no ,idata.con_qty ,idata.con_prc ,idata.odr_tp ,
# idata.con_tp ,idata.cncl_tp ,idata.short_qty,idata.blnc_qty ,idata.odr_qty ,
# idata.odr_nm ,idata.con_nm ))
#
# except con.Error as error:
# print("*******************************************************")
# print("Error: {}".format(error))
# print("*******************************************************")
#
# con.rollback()
#
# finally:
# con.commit()
# pass
return
def stkstrtgy(self, tp, idata, odata):
if tp == 'I':
sql = """INSERT INTO TRD.STKSTRTGY
(YMD, STRTGY_NO, ITEM, ODR_TP, QTY,
PRC, MSG
)
VALUES
(%s, %s, %s, %s, %s,
%s, %s
)
"""
idatam = []
idatam.append((idata.ymd, idata.strtgy_no, idata.item,
idata.odr_tp, idata.qty, idata.prc, idata.stgy_msg))
try:
with self.con.cursor() as self.cur:
self.cur.executemany(sql, (idatam))
except self.con.Error as error:
print(
"*******************************************************")
print("Error: {}".format(error))
print(
"*******************************************************")
self.con.rollback()
self.con.commit()
# 최초 주문 접수
if tp == 'F':
print("==============================================")
print("def stkstrtgy(self, tp, idata, odata): tp ", tp,
idata.exec_tp, idata.rslt_tp, idata.con_tp, idata.can_tp,
idata.item)
print("==============================================")
sql = """UPDATE TRD.STKSTRTGY
SET EXEC_TP = %s
, RSLT_TP = %s
, CON_TP = %s
, CAN_TP = %s
, MSG = %s
, ODR_NO = %s
WHERE YMD = %s
AND STRTGY_NO = %s
AND item = %s
"""
try:
with self.con.cursor() as self.cur:
self.cur.execute(
sql, (idata.exec_tp, idata.rslt_tp, idata.con_tp,
idata.can_tp, idata.msg, idata.odr_no, idata.ymd,
idata.strtgy_no, idata.item))
except self.con.Error as error:
print(
"*******************************************************")
print("Error: {}".format(error))
print(
"*******************************************************")
self.con.rollback()
self.con.commit()
if tp == 'U':
print("==============================================")
print("def stkstrtgy(self, tp, idata, odata): tp ", tp,
idata.exec_tp, idata.rslt_tp, idata.con_tp, idata.can_tp,
idata.item)
print("==============================================")
sql = """UPDATE TRD.STKSTRTGY
SET EXEC_TP = %s
, RSLT_TP = %s
, CON_TP = %s
, CAN_TP = %s
, MSG = %s
WHERE YMD = %s
AND STRTGY_NO = %s
AND item = %s
AND ODR_NO = %s
"""
try:
with self.con.cursor() as self.cur:
self.cur.execute(
sql, (idata.exec_tp, idata.rslt_tp, idata.con_tp,
idata.can_tp, idata.msg, idata.ymd,
idata.strtgy_no, idata.item, idata.odr_no))
except self.con.Error as error:
print(
"*******************************************************")
print("Error: {}".format(error))
print(
"*******************************************************")
self.con.rollback()
self.con.commit()
if tp == 'S':
sql = """SELECT YMD, STRTGY_NO, ITEM, ODR_TP, EXEC_TP, RSLT_TP, CON_TP, CAN_TP
, QTY, PRC, MSG
FROM TRD.STKSTRTGY
WHERE YMD = %s
AND EXEC_TP = 'N'
ORDER BY SEQ
"""
try:
with self.con.cursor() as self.cur:
self.cur.execute(sql, (self.ymd))
odata.ymd = []
odata.strtgy_no = []
odata.item = []
odata.odr_tp = []
odata.exec_tp = []
odata.rslt_tp = []
odata.con_tp = []
odata.can_tp = []
odata.qty = []
odata.prc = []
odata.msg = []
# 데이타 Fetch
rows = self.cur.fetchall()
for ymd, strtgy_no, item, odr_tp, exec_tp, rslt_tp, con_tp, can_tp, qty, prc, msg in rows:
odata.ymd.append(ymd)
odata.strtgy_no.append(strtgy_no)
odata.item.append(item)
odata.odr_tp.append(odr_tp)
odata.exec_tp.append(exec_tp)
odata.rslt_tp.append(rslt_tp)
odata.con_tp.append(con_tp)
odata.can_tp.append(can_tp)
odata.qty.append(qty)
odata.prc.append(prc)
odata.msg.append(msg)
except self.con.Error as error:
print(
"*******************************************************")
print("Error: {}".format(error))
print(
"*******************************************************")
self.con.rollback()
self.con.commit()
return
def stkmsttgt(self, tp, idata, odata):
print("==============================================")
print("def stkmsttgt(self, tp, idata, odata): tp ", tp)
print("==============================================")
if tp == 'S':
sql = """SELECT ITEM
FROM TRD.STKMSTTGT
WHERE USE_TP = '1'
"""
try:
with self.con.cursor() as self.cur:
self.cur.execute(sql)
odata.item = []
# 데이타 Fetch
rows = self.cur.fetchall()
for item in rows:
odata.item.append(item)
except self.con.Error as error:
print(
"*******************************************************")
print("Error: {}".format(error))
print(
"*******************************************************")
self.con.rollback()
self.con.commit()
return