# 나스닥 데이터 받기 (2009년 이후)

import win32com.client
import pandas as pd

# 기본 세팅
objCpStatus = win32com.client.Dispatch('CpUtil.CpCybos')

bConnect = objCpStatus.IsConnect
if (bConnect == 0):
    print("PLUS가 정상적으로 연결되지 않음. ")
    exit()
else : print("Plus 연결 성공")

# 지수 코드네임, 이름 찾기
objUscode = win32com.client.Dispatch('CpUtil.CpUsCode')
codelist = objUscode.GetUsCodeList(2)

for i in codelist :
    codename = objUscode.GetNameByUsCode(i)


# COMP : 나스닥 종합

objForeindex = win32com.client.Dispatch('Dscbo1.CpSvr8300')

objForeindex.SetInputValue(0,"COMP") # 나스닥
objForeindex.SetInputValue(1,ord("D")) # 일자별
objForeindex.SetInputValue(3,9999) # 일자별
objForeindex.BlockRequest()

cnt =  objForeindex.GetHeaderValue(3) # 데이터 수

date_list = []
open_list = []
high_list = []
low_list = []
close_list = []
vol_list = []

while True :
    for i in range(0,cnt) :
        date_list.append(objForeindex.GetDataValue(0,i))
        open_list.append(objForeindex.GetDataValue(1,i))
        high_list.append(objForeindex.GetDataValue(2,i))
        low_list.append(objForeindex.GetDataValue(3,i))
        close_list.append(objForeindex.GetDataValue(4,i))
        vol_list.append(objForeindex.GetDataValue(5,i))

    objForeindex.BlockRequest()
    cnt =  objForeindex.GetHeaderValue(3) # 데이터 수
    if  objForeindex.Continue == False : # 더 요청할 데이터가 없으면 break, 있으면 계속 요청!
        break
    response_codes = ["COMP"] * len(date_list)

dict1 = {'logDate' : date_list, 'stockcode': response_codes, 'priceOpen' :open_list, 'priceHigh' : high_list, 'priceLow' : low_list,
         'priceClose' : close_list, 'volume' : vol_list}

df = pd.DataFrame(dict1,
                  columns=['logDate', 'stockcode', 'priceOpen', 'priceHigh', 'priceLow', 'priceClose', 'volume']) # 2. 데이터프레임으로 만들기
df = df.sort_values(by=['logDate'], ascending=True)
df.to_csv("나스닥",header=True,index=False)

# DB에 저장하고 싶을 경우
from sqlalchemy import create_engine
engine = create_engine("mssql+pyodbc://서버명/DB명?driver=SQL+Server", echo=False)
engine.connect() # 연결
df.to_sql(name='LogDay', con=engine, if_exists='append', index=False) # df를 LogDay 테이블에 저장해줌