4-1 주식 데이터 저장 (sqlite)

소스파일은 github.com/galaxywiz/StockCrawler_py 에서 확인 가능합니다.

 

sqliteDB.py

import pandas as pd 
import dataframe
import sqlite3
import datetime
import os

class SqliteDB:
    def __init__(self, dbName, tableName):
        self.tableStruct_ = ""
        self.columns_ = []
        self.initDB(dbName, tableName)

    def initDB(self, dbName, tableName):
        dir = "DB/"
        if os.path.exists(dir) == False:
            os.makedirs(dir)
        self.conn_ = sqlite3.connect(dir + dbName)
        self.dbName_ = dbName
        self.tableName_ = tableName

    def _tableName(self, code):
        code = code.replace("=","_")
        name = "%s_%s" % (self.tableName_, code)
        return name

    #----------------------------------------------------------#
    # 테이블이 있는지 확인하고, 있으면 -1, 없으면 0, 생성했으면 1
    def getTable(self, code):
        if self.checkTable(code) == False:
            if self.createTable(code) == False:
                return 0
            else:
                return 1
        return -1

    # 테이블 이름이 있는지 확인
    def checkTable(self, code):
        tableName = self._tableName(code)
        with self.conn_:
            cur = self.conn_.cursor()
            sql = "SELECT count(*) FROM sqlite_master WHERE Name = \'%s\'" % tableName
            cur.execute(sql)
            rows = cur.fetchall()
            for row in rows:          
                if str(row[0]) == "1":
                    return True
            return False
    
    
    # 테이블 생성
    def createTable(self, code):
        tableName = self._tableName(code)
        with self.conn_:
            try:
                cur = self.conn_.cursor()
                sql = "CREATE TABLE %s (%s);" % (tableName, self.tableStruct_)
                cur.execute(sql)
                return True
            except:
                log = "! [%s] table make fail" % tableName 
                print(log)
                return False
    
    # 데이터 저장
    def save(self, code, dataframe):    
        tableName = self._tableName(code)
        with self.conn_:
            try:
                cur = self.conn_.cursor()
                columns = ""
                value = ""
                for col in self.columns_:
                    if len(columns) == 0:
                        columns = col
                    else:
                        columns = "%s, '%s'" % (columns, col)

                    if len(value) == 0:
                        value = "?"
                    else:
                        value = "%s, ?" % (value)

                sql = "INSERT OR REPLACE INTO \'%s\' (%s) VALUES(%s)" % (tableName, columns, value)
                cur.executemany(sql, dataframe.values)    
                self.conn_.commit()
            except:
                return None
            
    # 데이터 로드
    def load(self, code, orderBy = "candleTime ASC"):
        tableName = self._tableName(code)
        with self.conn_:
            try:  
                columns = ""
                for col in self.columns_:
                    if len(columns) == 0:
                        columns = col
                    else:
                        columns = "%s, %s" % (columns, col)              

                sql = "SELECT %s FROM \'%s\' ORDER BY %s" % (columns, tableName, orderBy)
                df = pd.read_sql(sql, self.conn_, index_col=None)
                if len(df) == 0:
                    return False, None
            except:
                return False, None

        return True, df     

Sqlite 기본 기능을 정의한 클래스 입니다.

기본적으로 테이블 생성 / 체크 / 로드 / 저장을 합니다.

원래는 하나의 파일로 만들었으나, 미국 주식과 한국 주식의 데이터들이 한국 주식은 센트 / 전 같은 소수점 자리를 쓰지 않으니까 INT 정수형으로 처리가 가능했는데, 미국 주식은 소수점 문제로 FLOAT 소수점 처리 가능하도록 컬럼 설정하다 보니 분리 되었습니다.

처음에 보면 생성자로, DB파일을 지정합니다.

22번 _tableName 이란 함수가 있습니다.

이는 테이블 이름을 지정하는 매크로인데, 저는 [생성자에서 받은 기본 이름]_[코드] 형태로 가져 가려고 합니다. 즉 하나의 주식 종목 마다 하나의 테이블을 생성해서 가져가는 방식입니다.

 

38번째 줄 보면, checkTableAndMake 함수가 보이실 겁니다.
이건 새로운 주식종목이 생성되면, DB파일에 테이블이 있는지 체크해 보고 없으면 생성해주는 함수입니다.

42번 라인보시면 "SELECT count(*) FROM sqlite_master WHERE Name = \'%s\'" 같이 되어있는데, 이는 sqlite_master 라는 관리 테이블에서 사용자가 생성한 테이블 이름을 검색하는 명령어입니다. 만약 검색한 테이블 있다면, 46라인처럼 결과값을 받아서 True리턴하고, 아니면 False 리턴 합니다.

 

57 라인에서는 테이블 생성 명령어 "CREATE TABLE %s (%s);" % (self.tableName_, self.tableStruct_) 와 같이 테이블을 생성합니다. 보시면, tableName_과 tableStruct_ 를 받아와서 문장으로 만든 뒤 쿼리를 실행하는데, 저 tableName과 struct 는 이 밑의 sqliteStockDB에서 조절 가능하도록 만들어주는 변수 입니다.

 

84 라인에서는 "INSERT OR REPLACE INTO \'%s\' (%s) VALUES(%s)" 와 같이 있는데, 이는 레코드(엑셀 시트로 생각하면 한 행 데이터)를 넣어 주는 명령어입니다. 만약 같은 값이 있으면 이를 새로 덮어쓰라는 의미로 INSERT OR REPLACE 명령어를 썼습니다.

이 문법은 MSSQL에서는 MERGE 라던가, MYSQL 에서는 REPLACE 라던가 다르기 때문에 혹시 다른 SQL제품을 사용한다면 적절히 수정하시는 게 좋습니다.

 

102라인에 로드 함수에 보면 "SELECT %s FROM \'%s\' ORDER BY %s" 와 같이 테이블 데이터를 가지고 오는 명령어가 있습니다.

뒤에 ORDER BY 하고 컬럼 이름을 입력하는데, 그 컬럼을 중심으로 정령해서 데이터를 달라는 뜻입니다. 기본은 오름차순이고, 내림차순 하려면 ORDER BY [컬럼명] DESC를 붙여줘야 합니다.

이제 sqlite에 접속해서 로딩 / 저장 등 최소한 기능을 구현 하였으니, 우리 주식 프로그램에 사용할 db 클래스를 생각해 봅시다.

 

웹에서 데이터를 가지고 오는 것이니 1일봉 데이터로 시가, 고가, 저가, 종가, 거래량만 저장하도록 합시다, 어차피 기술지표 값들은 저것들 가지고 그때 그때 계산할 수 있으니 굳이 다 저장할 필요 없습니다. (계산하는데 시간이 많이 걸리지도 않고요)

이를 구현해 보면 아래처럼 작성할 수 있습니다.

 

sqliteStockDB.py

import pandas as pd 
import dataframe
import sqlite3
from datetime import datetime
from datetime import timedelta
import time
import os

from stockData import StockData
from sqliteDB import SqliteDB

class DayPriceDB(SqliteDB):
    def initDB(self, dbName, tableName):
        super().initDB(dbName, tableName)

        self.tableStruct_ = "candleTime DATETIME PRIMARY KEY, start INT, high INT, low INT, close INT, vol INT"
        self.columns_ = ['candleTime', 'start', 'high', 'low', 'close', 'vol']

class DayPriceFloatDB(SqliteDB):
    def initDB(self, dbName, tableName):
        super().initDB(dbName, tableName)

        self.tableStruct_ = "candleTime DATETIME PRIMARY KEY, start Float, high Float, low Float, close Float, vol INT"
        self.columns_ = ['candleTime', 'start', 'high', 'low', 'close', 'vol']

 

보시면 tableStruct_.의 컬럼 타입의 start(시작가), high(고가), low(저가), close(종가)가 DayPriceDB는 INT, 정수만 입력 받고, DayPriceFloatDB 를 보시면 Float 소수점 입력을 받도록 커스터마이징을 해주었습니다.