[Python]주식 종목 데이터 및 일별 시세 데이터 DB적재

1. 파이썬을 활용한 테이블 적재 방법

  - 파이썬을 이용하여 한국증권 거래소에서 제공하는 주식 종목 데이터(CSV)를 테이블에 적재하는 로직을 구현

import csv
import pymysql
import socket

#한국증권거래소에서 제공받은 csv파일을 읽어옴
stock_code = open('/root/data.csv', 'r')
#배열에 넣는다.
csvReader = csv.reader(stock_code)


#데이터 베이스를 연결
IpAddr = socket.gethostbyname(socket.gethostname())
conn = pymysql.connect(host=IpAddr, port=3306, user='root', passwd='root',charset='utf8',autocommit=True)

cur = conn.cursor()

cur.execute("CREATE DATABASE IF NOT EXISTS STOCK")
cur.execute("USE STOCK")

#주식 종목 데이터를 삽입 할 테이블을 생성 
cur.execute("CREATE TABLE IF NOT EXISTS `J_MASTER`(`ISSUE_CODE` VARCHAR(10) BINARY NOT NULL ,`ENTP_NM` VARCHAR(30) BINARY NOT NULL ,`INDTP_CODE` VARCHAR(20) BINARY NOT NULL ,`INDTP_NM` VARCHAR(1000) BINARY NOT NULL  ,`LSTD_STOCK_CNT` VARCHAR(1000) NOT NULL  ,`CPMNY` VARCHAR(1000) NOT NULL  ,`PARPRC` VARCHAR(1000) NOT NULL  ,`CURRENCY_TP` VARCHAR(20) BINARY NULL ,`DLG_TEL` VARCHAR(50) BINARY NULL  ,`ADDR` VARCHAR(200) BINARY NULL ,PRIMARY KEY(`ISSUE_CODE`))")

#반복문을 돌려서 1행씩 테이블에 삽입 
for st in csvReader:
    cur.execute("insert into J_MASTER(ISSUE_CODE, ENTP_NM, INDTP_CODE, INDTP_NM, LSTD_STOCK_CNT, CPMNY, PARPRC, CURRENCY_TP, DLG_TEL, ADDR ) values(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",(st[1],st[2],st[3],st[4],st[5],st[6],st[7],st[8],st[9],st[10])) 
stock_code.close()
cur.close()

Mysql(MariaDB)에서는 CSV파일을 로딩하여 테이블에 적재하는 방법이 존재하지만, 여러가지 수정 사항이 필요하여 추후 진행 할 예정

2. Yahoo Stock 테이블 적재

  - 주식 종목 데이터를 기준으로 yahoo에서 제공하는 주식 일별 시세 데이터를 가져와 테이블에 적재 

import pandas_datareader.data as web
import datetime
import csv
import socket
import pymysql
import sys

# 현재 데이터 일자 지정 
end   = datetime.datetime.now()
# 과거 데이터 일자 지정
start = '1970-01-01'   

IpAddr = socket.gethostbyname(socket.gethostname())

#Database 연결
conn = pymysql.connect(host=IpAddr, port=3306, user='root', passwd='root', db='STOCK',charset='utf8',autocommit=True)

cur = conn.cursor()

#Database 테이블 생성

#종목 코드 데이터 불러오기
stock_code = open('/root/data.csv', 'r')
csvReader = csv.reader(stock_code)

cur.execute("CREATE DATABASE IF NOT EXISTS STOCK")
cur.execute("USE STOCK")

#일별 시세 데이터를 적재하기 위한 테이블 생성
cur.execute("CREATE TABLE IF NOT EXISTS J_DAILY(CURPRI_DT varchar(8) COMMENT '시세일자' ,   ISSUE_CODE varchar(20) COMMENT '종목코드',   CRPC decimal(20,5) COMMENT '시가',   HPRC decimal(20,5) COMMENT '고가',   LPRC decimal(20,5)  COMMENT '저가',  CPRC decimal(20,5) COMMENT '종가',TRDVOL decimal(20,5) COMMENT '거래량',ALT_CPRC decimal(20,5) COMMENT '수정종가');")

#주식 데이터 호출 및 데이터베이스 INSERT
for st in csvReader:
     print(st[0],st[1],st[2])
     EventCode = st[1]
     CompanyNm = st[2]

     #yhaoo
     try: 
        stock_data = web.DataReader("%s.KS" %st[1],'yahoo',start,end)
        stock_data.loc[:,'StockCode'] = EventCode   #종목코드
        stock_data.loc[:,'date'] = stock_data.index.astype('str') #날짜데이터
        
        json = open('/home/python/stockjson/json_data.json', 'w')
        #수정종가(Adj Close)의 컬럼명에 띄어쓰기가 되어 있어 JSON파일에서 제대로 로딩해 오지 못하는 문제는 다음과 같이 replace를 사용하여 해결 
        #날짜데이터의(YYYY-MM-DD)와 같이 들어오는 데이터의 유형을 대쉬(-)를 제외하기 위해서 replace 사용 
        json.write(stock_data.to_json(orient='records').replace('Adj Close','AdjClose').replace('-',''))
        json.close()
         
        cur.execute("DROP TABLE IF EXISTS JSON_STOCK_DATA")
        cur.execute("CREATE TABLE JSON_STOCK_DATA  (Open varchar(100),   High varchar(100),   Low varchar(100),   Close varchar(100),   Volume varchar(100),  AdjClose varchar(100), StockCode varchar(100), date varchar(100)) engine=connect,table_type=json,file_name='/home/python/stockjson/json_data.json'")
        cur.execute("INSERT INTO J_DAILY(CURPRI_DT,ISSUE_CODE,CRPC,HPRC,LPRC,CPRC,TRDVOL,ALT_CPRC)  SELECT date,StockCode,Open,High,Low,Close,Volume,AdjClose FROM JSON_STOCK_DATA")
       
    #에러 발생시 예외처리
     except :
         pass

stock_code.close()

cur.close()
print('데이터 통합작업 완료')

3. 시세데이터 프로그램 수행

  - 770개 종목에 대해서 시세 데이터를 수집하여 테이블에 적재 작업 완료!!

4.데이터 확인 

- 총 2,502,580건의 데이터가 성공적으로 적재 완료!!