17.1 SQLite

[KevinFire2030]

17.1 SQLite

• SQLite는 Oracle이나 PostgreSQL과 같은 큰 규모의 DBMS와 달리 '서버 설치와 설정면에서 'Lite'한 DBMS입니다.
  

[KevinFire2030]

17.1.1 sqlite 모듈 기초

• 파이썬 표준 라이브러리 (파이썬이 설치될 때 기본적으로 설치되는 모듈)
• 데이터베이스 생성은 sqlite3 모듈의 connect 함수를 사용
  • 이때 함수의 인자로 생성될 데이터베이스 파일의 경로와 이름을 지정

con = sqlite3.connect("D:\Fire2025\23W23\ch17\kospi.db")
Traceback (most recent call last):
  File "C:\Program Files\JetBrains\PyCharm Community Edition 2022.3.2\plugins\python-ce\helpers\pydev\pydevconsole.py", line 364, in runcode
    coro = func()
  File "<input>", line 1, in <module>
sqlite3.OperationalError: unable to open database file
con = sqlite3.connect("D:/Fire2025/23W23/ch17/kospi.db")

• 데이터를 조작하기 위해서는 SQL 구문을 호출하는 Cursor 객체 필요

  cursor = con.cursor()

• 데이터를 넣기 전에 가장 먼저 해야 하는 일은 테이블을 만드는 것

  
  CREATE TABLE kakao (Date text, Open int, High int, Low int, Close int, Volume int)

cursor.execute("CREATE TABLE kakao (Date text, Open int, High int, Low int, Close int, Volume int)")

- 테이블에 데이터 넣기

INSERT INTO kakao VALUES ("6.06.03", 97000, 98600, 9690, 98000, 321405) cursor.execute("INSERT INTO kakao VALUES ("6.06.03", 97000, 98600, 9690, 98000, 321405)")

주의! SQL 내부에서 큰따옴표 사용시 에러 발생 cursor.execute("INSERT INTO kakao VALUES ("6.06.03", 97000, 98600, 9690, 98000, 321405)") ^ SyntaxError: invalid syntax 예)

- 작업한 내용 데이터베이스에 반영

con.commit() con.close()

[KevinFire2030]

17.1.2 데이터베이스 브라우저

데이터베이스 브라우저는 데이터베이스에 저장된 데이터를 효과적으로 보여주는 프로그램

• 설치하기

https://sqlitebrowser.org/dl/

[KevinFire2030]

7.1.3 데이터베이스로부터 데이터 읽기

con = sqlite3.connect("D:/Fire2025/23W23/ch17/kospi.db")
cursor = con.cursor()
df = cursor.execute("SELECT * FROM kakao")
cursor.fetchone()
('16.06.03', 97000, 98600, 9690, 98000, 321405)

con = sqlite3.connect("D:/Fire2025/23W23/ch17/kospi.db")
cursor = con.cursor()
df = cursor.execute("SELECT * FROM kakao")
kakao = cursor.fetchall()

In [12]: kakao[0][0]
Out[12]: '16.06.03'

In [13]: kakao[0][1]
Out[13]: 97000

In [14]: kakao[0][2]
Out[14]: 98600

[KevinFire2030]

7.2 Pandas와 SQLite

• pandas의 DataFrame 객체를 데이터베이스에 저장하는 방법과 데이터베이스에 저장된 데이터를 DataFrame 객체로 로드하는 방법

[KevinFire2030]

7.2.1 DataFrame 객체를 SQLite DB에 저장하기

import pandas as pd
from pandas import Series, DataFrame
raw_data = {'col0': [1, 2, 3, 4], 'col1': [10, 20, 30, 40], 'col2':[100, 200, 300, 400]}
df = DataFrame(raw_data)
df
   col0  col1  col2
0     1    10   100
1     2    20   200
2     3    30   300
3     4    40   400

pandas 웹 페이지(http://pandas.pydata.org/)에서 to_sql 메서드를 살펴보면 다음과 같이 정의돼 있습니다.

df.to_sql('test', con)

DataFrame.to_sql(name, con, flavor='sqlite', schema=None, if_exists='fail', index=True, index_label=None, chunksize=None, dtype=None)

표 17.1 DataFrame.to_sql 메서드 파라미터

파라미터 | 설명 -- | -- name | SQL 테이블 이름으로 파이썬 문자열로 형태로 나타낸다. con | Cursor 객체 flavor | 사용한 DBMS를 지정할 수 있는데 'sqlite' 또는 'mysql'을 사용할 수 있다. 기본값은 'sqlite'이다. schema | Schema를 지정할 수 있는데 기본값은 None이다. if_exists | 데이터베이스에 테이블이 존재할 때 수행 동작을 지정한다. 'fail', 'replace', 'append' 중 하나를 사용할 수 있는데 기본값은 'fail'이다. 'fail'은 데이터베이스에 테이블이 있다면 아무 동작도 수행하지 않는다. 'replace'는 테이블이 존재하면 기존 테이블을 삭제하고 새로 테이블을 생성한 후 데이터를 삽입한다. 'append'는 테이블이 존재하면 데이터만을 추가한다. index | DataFrame의 index를 데이터베이스에 칼럼으로 추가할지에 대한 여부를 지정한다. 기본값은 True이다. index_label | 인덱스 칼럼에 대한 라벨을 지정할 수 있다. 기본값은 None이다. chunksize | 한 번에 써지는 로우의 크기를 정숫값으로 지정할 수 있다. 기본값은 None으로 DataFrame 내의 모든 로우가 한 번에 써진다. dtype | 칼럼에 대한 SQL 타입을 파이썬 딕셔너리로 넘겨줄 수 있다.

to_sql 메서드에는 여러 파라미터가 있지만 대부분 기본값을 가지고 있어 따로 지정할 필요가 없는 경우가 많습니다. 다만

[KevinFire2030]

7.2.2 SQLite DB에서 테이블 로드하기

• 데이터베이스의 테이블을 DataFrame 객체로 읽어오기 위해 read_sql 함수를 제공합니다.

df2 = pd.read_sql("SELECT * FROM test", con, index_col=None)

df2
   index  col0  col1  col2
0      0     1    10   100
1      1     2    20   200
2      2     3    30   300
3      3     4    40   400

df = pd.read_sql("SELECT * FROM test", con, index_col='index')

       col0  col1  col2
index                  
0         1    10   100
1         2    20   200
2         3    30   300
3         4    40   400

[KevinFire2030]

7.2.3 Pandas를 이용한 주가 데이터 저장

import pandas as pd
import pandas_datareader.data as web
import sqlite3

df = web.DataReader('005930', 'naver', '2023-01-01', '2023-06-09')

con = sqlite3.connect("kospi.db")
df.to_sql('005930', con, if_exists='replace')

readed_df = pd.read_sql("SELECT * FROM '005930'", con, index_col = 'Date')
print(readed_df)

[KevinFire2030]

7.3 증권사 API를 이용한 주가 데이터 저장

• 증권사의 API를 이용해 주가 데이터를 다운로드하고 이를 데이터베이스로 저장

[KevinFire2030]

7.3.1 종목 코드 리스트 얻어 오기

• 종목 코드는 KRX 홈페이지와 같은 웹을 통해 내려받을 수도 있고 증권사의 API를 사용해도 내려받을 수 있습니다.

• 주식

• 선물 한국

미국

import sys
from PyQt5.QtWidgets import *
from PyQt5.QAxContainer import *
from PyQt5.QtCore import *

class Kiwoom(QAxWidget):
    def __init__(self):
        super().__init__()
        self._create_kiwoom_instance()
        self._set_signal_slots()

    def _create_kiwoom_instance(self):
        self.setControl("KHOPENAPI.KHOpenAPICtrl.1")

    def comm_connect(self):
        self.dynamicCall("CommConnect()")
        self.login_event_loop = QEventLoop()
        self.login_event_loop.exec_()

    def _set_signal_slots(self):
        self.OnEventConnect.connect(self._event_connect)

    def get_code_list_by_market(self, market):
        code_list = self.dynamicCall("GetCodeListByMarket(QString)", market)
        code_list = code_list.split(';')
        return code_list[:-1]

    def _event_connect(self, err_code):
        if err_code == 0:
            print("connected")
        else:
            print("disconnected")

        self.login_event_loop.exit()

if __name__ == "__main__":
    app = QApplication(sys.argv)
    kiwoom = Kiwoom()
    kiwoom.comm_connect()
    code_list = kiwoom.get_code_list_by_market('10')
    for code in code_list:
        print(code, end=" ")

class Kiwoom(QAxWidget):
    def __init__(self):
        super().__init__()
        self._create_kiwoom_instance()

    def _create_kiwoom_instance(self):
        self.setControl("KHOPENAPI.KHOpenAPICtrl.1")

• 키움증권의 OpenAPI+를 사용하려면 먼저 COM 오브젝트를 생성해야 합니다. COM 오브젝트를 생성하려면 "KHOPENAPI.KHOpenAPICtrl.1"이라는 ProgID를 QAxWidget 클래스의 생성자로 전달하거나 setControl 메서드를 사용하면 됩니다.
• 메서드의 이름에 언더스코어(_)를 붙여준 것은 _create_kiwoom_instance라는 메서드가 주로 Kiwoom 클래스의 메서드에서 호출되기 때문

[KevinFire2030]

7.3.2 일봉 데이터 연속 조회

import sys
from PyQt5.QtWidgets import *
from PyQt5.QAxContainer import *
from PyQt5.QtCore import *
import time

TR_REQ_TIME_INTERVAL = 0.2

class Kiwoom(QAxWidget):
    def __init__(self):
        super().__init__()
        self._create_kiwoom_instance()
        self._set_signal_slots()

    def _create_kiwoom_instance(self):
        self.setControl("KHOPENAPI.KHOpenAPICtrl.1")

    def _set_signal_slots(self):
        self.OnEventConnect.connect(self._event_connect)
        self.OnReceiveTrData.connect(self._receive_tr_data)

    def comm_connect(self):
        self.dynamicCall("CommConnect()")
        self.login_event_loop = QEventLoop()
        self.login_event_loop.exec_()

    def _event_connect(self, err_code):
        if err_code == 0:
            print("connected")
        else:
            print("disconnected")

        self.login_event_loop.exit()

    def get_code_list_by_market(self, market):
        code_list = self.dynamicCall("GetCodeListByMarket(QString)", market)
        code_list = code_list.split(';')
        return code_list[:-1]

    def get_master_code_name(self, code):
        code_name = self.dynamicCall("GetMasterCodeName(QString)", code)
        return code_name

    def set_input_value(self, id, value):
        self.dynamicCall("SetInputValue(QString, QString)", id, value)

    def comm_rq_data(self, rqname, trcode, next, screen_no):
        self.dynamicCall("CommRqData(QString, QString, int, QString", rqname, trcode, next, screen_no)
        self.tr_event_loop = QEventLoop()
        self.tr_event_loop.exec_()

    def _comm_get_data(self, code, real_type, field_name, index, item_name):
        ret = self.dynamicCall("CommGetData(QString, QString, QString, int, QString", code,
                               real_type, field_name, index, item_name)
        return ret.strip()

    def _get_repeat_cnt(self, trcode, rqname):
        ret = self.dynamicCall("GetRepeatCnt(QString, QString)", trcode, rqname)
        return ret

    def _receive_tr_data(self, screen_no, rqname, trcode, record_name, next, unused1, unused2, unused3, unused4):
        if next == '2':
            self.remained_data = True
        else:
            self.remained_data = False

        if rqname == "opt10081_req":
            self._opt10081(rqname, trcode)

        try:
            self.tr_event_loop.exit()
        except AttributeError:
            pass

    def _opt10081(self, rqname, trcode):
        data_cnt = self._get_repeat_cnt(trcode, rqname)

        for i in range(data_cnt):
            date = self._comm_get_data(trcode, "", rqname, i, "일자")
            open = self._comm_get_data(trcode, "", rqname, i, "시가")
            high = self._comm_get_data(trcode, "", rqname, i, "고가")
            low = self._comm_get_data(trcode, "", rqname, i, "저가")
            close = self._comm_get_data(trcode, "", rqname, i, "현재가")
            volume = self._comm_get_data(trcode, "", rqname, i, "거래량")
            print(date, open, high, low, close, volume)

if __name__ == "__main__":
    app = QApplication(sys.argv)
    kiwoom = Kiwoom()
    kiwoom.comm_connect()

    # opt10081 TR 요청
    kiwoom.set_input_value("종목코드", "039490")
    kiwoom.set_input_value("기준일자", "20170224")
    kiwoom.set_input_value("수정주가구분", 1)
    kiwoom.comm_rq_data("opt10081_req", "opt10081", 0, "0101")

    while kiwoom.remained_data == True:
        time.sleep(TR_REQ_TIME_INTERVAL)
        kiwoom.set_input_value("종목코드", "039490")
        kiwoom.set_input_value("기준일자", "20170224")
        kiwoom.set_input_value("수정주가구분", 1)
        kiwoom.comm_rq_data("opt10081_req", "opt10081", 2, "0101")

connected
**600**
20170224 76900 77900 76000 76300 57565
20170223 78100 78100 76000 77000 60117
20170222 76600 78100 76300 77300 49600
20170221 73500 76900 73300 76500 46682
20170220 74600 75000 73600 73800 30275
20170217 74600 75900 73900 75600 30195
20170216 72100 75900 72100 75800 67704
20170215 71700 73600 71400 71800 72560
...

20040503 4774 4987 4683 4683 144569
20040430 4911 5200 4911 4949 107002
20040429 5117 5436 5025 5101 109911
20040428 5071 5452 4995 5444 182604
20040427 5452 5482 5033 5063 299489
20040426 5436 5741 5261 5398 343283
20040423 6076 6799 5353 5353 1415109

def comm_rq_data(self, rqname, trcode, next, screen_no):
    self.dynamicCall("CommRqData(QString, QString, int, QString", rqname, trcode, next, screen_no)
    self.tr_event_loop = QEventLoop()
    self.tr_event_loop.exec_()

• CommRqData를 통해 키움증권 서버에 TR을 요청하면 데이터가 바로 반환되는 것이 아닙니다. 키움증권 서버는 TR을 처리한 후 이벤트를 통해 알려주기 때문에 키움증권이 이벤트를 줄 때까지 대기하고 있어야 합니다. 따라서 CommRqData를 호출한 후에 이벤트 루프를 만들어주는 코드가 반드시 있어야 합니다. 이 부분은 앞서 로그인을 수행할 때 이벤트 루프를 만든 것과 동일합니다.

• 600개의 데이터(거래일 기준)가 반환됩니다.

• OpenAPI+의 CommGetData 메서드의 반환값은 문자열 타입 로 양쪽에 공백이 있습니다. 따라서 공백을 제거하기 위해 반환받은 문자열에 strip 메서드를 사용했습니다.

• 키움증권 서버는 OnReceiveTrData 이벤트가 발생할 때 PrevNext라는 인자 값을 통해 연속조회가 필요한 경우 PrevNext 값을 2로 리턴합니다.

• 세 번째 고려사항은 이벤트 루프에 대한 처리입니다. 앞서 CommRqData를 호출할 때 이벤트 루프를 생성했습니다. 이벤트 루프 덕분에 키움증권 서버가 'OnReceiveTrData' 이벤트를 보낼 때까지 대기할 수 있었습니다.

• 움증권은 1초에 최대 5번의 TR 요청만 허용하고 있으므로 time 모듈의 sleep 함수를 통해 0.2초를 대기한 후 다음 TR을 요청하도록 구현했습니다.