2. 주가 일봉 데이터 저장 프로그램 개발(DB 설계 및 구현)

< 주가 일봉 데이터 저장 프로그램 개발 - DB 설계 및 구현>

1. 주가 일봉 데이터 저장 프로그램 개발(개요)

해당 포스트를 읽기 전에 이전 포스트를 읽기 바란다.

이번 포스트에서는 DB를 설계하고 DB 관련 코드를 파이썬으로 구현할 것이다. 우리는 MySQL을 사용한다. 주가 일봉 데이터 저장 프로그램에서 DB에 저장하는 데이터는 '일자', '시가', '고가', '저가', '종가',' 거래량'으로 구성된다.  테이블은 각 종목으로 한다. 따라서 DB는 종목 테이블로 구성되고 테이블 내에 '일자', '시가', '고가', '저가', '종가',' 거래량' 칼럼이 있다. 코스피와 코스닥 지수의 경우 테이블 명을 'kospi', 'kosdaq'으로 일반 종목들과 etf는 'a+종목코드'로 한다. 테이블의 구조는 아래와 같다.

따라서 테이블을 생성 sql은 다음과 같다. 

sql = 'create table ' + code + '(Date date primary key,Open Decimal,High Decimal,Low Decimal,Close Decimal, Volume Decimal);'

DB를 설계해봤다. 다음으로 DB 관련 코드를 구현해보자. DB 코드에 필요한 기능은 아래와 같다.

1. 데이터 베이스 생성(이미 생성되어 있는 지 확인 필요)

2. 테이블 생성(이미 생성되어 있는 지 확인 필요)

3. 종목의 주가 일봉 데이터 INSERT

4. 테이블 내 특정 종목 데이터의 가장 최근 INSERT 날짜 반환 

'4'번 기능을 구현하는 이유는 효율성 때문이다. 예로 한 달 전에 코스피 지수 데이터를 DB에 저장하고 오늘 코스피 지수 데이터를 사용한다고 하자. 최근 한 달동안 코스피 지수 데이터가 없기 때문에 DB에 코스피 지수 데이터를 INSERT를 해야 한다. 하지만 그냥 INSERT 하면 우리나라 주식 시장 최초 개장일부터의 데이터가 다시 INSERT 된다. 테이블의 'Date' 필드를 primary key로 했기 때문에 에러가 난다. primary key로 하지 않았더라도 어마어마한 데이터 중복이 나타난다. 코스피 개장일 1985년부터 2017년까지 데이터 약 8700개 행이 중복된다. 

또한 코스피 테이블 전체 데이터를 지우고 다시 INSERT 하는 방법도 있다. 하지만 많은 양의 데이터를 다시 INSERT 하기는 시간 효율성이 떨어진다. 그래서 '4'번 기능을 구현했다. 가장 최근에 INSERT 된 데이터의 Date(날짜)를 알면 그 이후 데이터만 INSERT 하면 된다. 

그러면 위에서 제시한 4가지 기능의 코드를 보자. 파일명은 StockDB.py 이다.  전체 코드는 아래와 같고 순차적으로 기능 하나씩 알아보자. 필자는 파이썬에서 MySQL에 접근하는 API로 pymysql을 사용했다.

from sqlalchemy import create_engine
import pymysql
pymysql.install_as_MySQLdb()
import pandas as pd

class StockDB():
    def init(self, password):
        if self._create_database(password) is False:
            return False
        self.engine = create_engine("mysql+mysqldb://root:"+password+"@localhost/stock", encoding='utf-8')
        self.conn = pymysql.connect(host='localhost', user='root', password=password, db='stock', charset='utf8')
        self.cursor = self.conn.cursor()
        return True

    def _create_database(self,password):
        try:
            conn = pymysql.connect(host='localhost', user='root', password=password, charset='utf8')
            cursor = conn.cursor()
            sql = 'SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = \'stock\''
            result = cursor.execute(sql)

            if result == 0:
                sql = 'CREATE DATABASE stock'
                cursor.execute(sql)
                conn.commit()
        except:
            return False
        return True


    def close(self):
        self.conn.close()

    def select_max_date(self,table_name):
        sql = 'select max(Date) from ' + table_name
        self.cursor.execute(sql)
        result = self.cursor.fetchone()
        return result[0]

    def insert_chart(self,data, table_name):
        data.to_sql(name=table_name, con=self.engine, if_exists='append')
        self.conn.commit()

    def create_table(self,table_name):
        sql = 'SHOW TABLES LIKE \'' + table_name + '\''
        result = self.cursor.execute(sql)
        if result == 0:
            sql = 'create table ' + table_name + '(Date date primary key,Open Decimal,High Decimal,Low Decimal,Close Decimal, Volume Decimal);'
            self.cursor.execute(sql)
            self.conn.commit()

'1'번, 데이터 베이스 생성(이미 생성되 있는 지 확인) 기능 코드를 보자. DB를 사용하려면 당연히 데이터 베이스를 생성해야 한다. 사용자의 DB 패스워드를 인자로 받으면 'pymysql.connect'를 통해 DB에 접속한다. 패스워드가 틀리면 예외가 발생해 except문으로 넘어가 False를 리턴한다. 다음 행의 sql 문으로 데이터 베이스가 현재 있는 지 확인한다. 이미 생성되었으면 데이터 베이스 생성을 취소해야 하기 때문이다. 

'SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = \'stock\''

위 sql을 사용해 'stock' 데이터베이스가 있는 지 확인한다. 'cursor.execute(sql)'을 호출하면 해당 sql 문이 접속된 DB에 실행된다. 반환 값이 '0'이면 'stock' 데이터베이스가 DB 상에 없다는 의미이기 때문에 'CREATE DATABASE stock'으로 데이터 베이스를 생성한다.  

    def _create_database(self,password):
        try:
            conn = pymysql.connect(host='localhost', user='root', password=password, charset='utf8')
            cursor = conn.cursor()
            sql = 'SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = \'stock\''
            result = cursor.execute(sql)

            if result == 0:
                sql = 'CREATE DATABASE stock'
                cursor.execute(sql)
                conn.commit()
        except:
            return False
        return True

'2' 번 기능은 테이블을 생성한다. 테이블이 이미 있는 지도 확인한다. '1'번 기능과 로직은 같다. ( SHOW TABLE LIKE \''+table_name+ '\'' ) sql 문을 이용해 인자로 받은 테이블 네임과 동일한 테이블이 있는 지 확인하다. 없다면 위에서 설계한 테이블 스펙대로 테이블을 생성한다. 

    def create_table(self,table_name):
        sql = 'SHOW TABLES LIKE \'' + table_name + '\''
        result = self.cursor.execute(sql)
        if result == 0:
            sql = 'create table ' + table_name + '(Date date primary key,Open Decimal,High Decimal,Low Decimal,Close Decimal, Volume Decimal);'
            self.cursor.execute(sql)
            self.conn.commit()

'3' 번 기능은 인자로 받은 주가 일봉 데이터를 테이블에 INSERT 한다. 인자로 받은 'data'가 주가 일봉 데이터다. 'data' 변수 타입은 'DataFrame'이다. DataFrame 변수는 'to_sql' 메서드로 한 번에 DataFrame 내부 데이터를 INSERT 할 수 있다. 

    def insert_chart(self,data, table_name):
        data.to_sql(name=table_name, con=self.engine, if_exists='append')
        self.conn.commit()

'4'번 기능은 특정 종목(테이블) 데이터의 가장 최근 INSERT된 데이터의 Date(날짜) 칼럼를 반환한다. 이 기능을 구현하는 이유는 위에서 설명했다.  ( 'select max(Date) from '+ table_name ) sql 문을 이용해서 테이블 내 데이터의 Date 칼럼 최댓값을 리턴한다. 'self.cursor.execute(sql)'을 통해 DB에서 sql문을 실행하고 'self.cursor.fetchone()'을 이용해 입력한 SELECT 문의 리턴 값을 전달 받는다. 리턴 값의 타입은 튜플이기 때문에 'result[0]' 을 이용해 실제 max(Date) 값에 접근한다. 

    def select_max_date(self,table_name):
        sql = 'select max(Date) from ' + table_name
        self.cursor.execute(sql)
        result = self.cursor.fetchone()
        return result[0]

이 것으로 DB 설계 및 구현을 마쳤다. 사실상 주가 일봉 데이터 저장 프로그램에서는 단순히 종목의 일봉 데이터만 저장하기에 매우 간단하다. DB에 대한 전체 소스파일은 첨부파일에 있다.

StockDB.py