python 증권데이터 분석 - DB Updata 모듈만들기#1, krx 종목주식시세 DB로 업데이트하기

주식시세 매일 DB에 업데이트 하기

네이버 금융의 주식 시세와 BeautifulSouf, Pandas를 사용해서

Maria DB에 매일 자동으로 업데이트하는 DB Updater 모듈을 만들어보자.

 

 

DB Updater 클래스 구조

C 드라이버 하부에 새로운 폴더(디렉터리)를 만들고 investar 패키지를 생성할 [DB구축] 디렉터리를 만들어줍니다.

1. DBUpdater 스텁(stub) code 작성하기

스텁코드란 인터페이스는 정의되어 있으나 실제 코드가 구현되어 있지 않은 상태의 코드

# DB STUB 코드

class DBUpdater:
    # 생성자 = MariaDB 연결 및 종목코드 딕셔너리 생성
    def __init__(self):
    
    # 소멸자 : MariaDB 연결 해제
    def __del__(self):

    # KRX로부터  상장법인 목록 파일을 읽어와 데이터 프레임 변환
    def read_krx_code(self):

    # 종목코드를 conpany_info 테이블에 업데이트 한 후 딕셔너리에 저장
    def update_comp_info(self):

    # 네이버금융에서 주식 시세를 읽어서 데이터프레임으로 변환
    def read_naver(self, code, company, pages_to_fetch):

    # 네이버 금융에서 읽어온 주식 시세를 DB에 REPLACE
    def replace_into_db(self, df, num, code, company):
    
    # KRX 상장 법인의 주식 시세를 네이버로 부터 읽어 DB에 업데이트
    def update_daily_price(self, pages_to_fetch):

    # 실행 즉시 매일 오후 5시에 daily_price 테이블 업데이트
    def execute_daily(self):

if __name == '__main__':
    dbu = DBUpdater()
    dbu.execute_daily()

 

2. HeldSQL을 이용하여 데이터베이스 테이블 생성하기

HEldSQL에 접속하여 세션관리자 창에 새로운 세션을 만들어주고 설정한 암호와 포트를 입력해준다.

 

나의 로컬에는 3306 포트가 MYSQL로 사용되고 있기때문에, 3307포트를 사용했다.

 

CREATE TABLE IF NOT EXISTS company_info (
	code VARCHAR(20),
	company VARCHAR(40),
	last_update DATE,
	PRIMARY KEY(CODE)

)DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS daily_price (
	code VARCHAR(20),
	date DATE,
	open BIGINT(20),
	high BIGINT(20),
	low BIGINT(20),
	close BIGINT(20),
	diff BIGINT(20),
	volume BIGINT(20),
	PRIMARY KEY (code, date)
)DEFAULT CHARSET=utf8;

쿼리에 company_info 테이블과 daily_price 테이블을 생성하는 쿼리를 실행하면

다음과 같이 company_info 테이블과 daily_price 테이블 생성되어 있음을 확인할 수 있다.

 

company_info 테이블과 daily_price 테이블을 더블클릭하면 테이블에 설정된 테이블 정보를 확인할 수 있다.

 

 

python에서 pymysql로 테이블 생성하기

pymysql 라이브러리는 파이썬 프로그램에서 직접 SQL 구문을 실행할 수 있게 해주는 라이브러리이다.

 

pymysql 설치하기

# pip install
pip install pymysql

# conda install
conda install -c anaconda pymysql

 

DBUpdater 클래스의 생성자, 소멸자 함수 채우기

 

    # 생성자 = MariaDB 연결 및 종목코드 딕셔너리 생성
    def __init__(self):
        self.conn = pymysql.connect(host='localhost', user='root',
                                    password='mariadb', db='INVESTAR', charset='utf8')
        
        with self.conn.cursor() as cur:
            sql = """
            CREATE TABLE IF NOT EXISTS company_info (
                code VARCHAR(20),
                company VARCHAR(40),
                last_update DATE,
                PRIMARY KEY(CODE))

            CREATE TABLE IF NOT EXISTS daily_price (
                code VARCHAR(20),
                date DATE,
                open BIGINT(20),
                high BIGINT(20),
                low BIGINT(20),
                close BIGINT(20),
                diff BIGINT(20),
                volume BIGINT(20),
                PRIMARY KEY (code, date))
            """
            cur.execute(sql)
        self.conn.commit()

        self.codes = dict()
        self.update_comp_info()
    
    # 소멸자 : MariaDB 연결 해제
    def __del__(self):
        self.conn.close()

 

DBUpdater 클래스에서 생성자로 객체가 생성될 때, mariaDB에 접속하고, 소멸될 때 mariaDB 접속을 해제한다.

 

INVESTAR 데이터베이스의 campany_info 테이블은 회사명 칼럼에서 한글 회사명을 불러오기 때문에, 인코딩 오류를 방지하기 위해 charset = 'utf8'로 인코딩 형식을 지정해주어 방지할 수 있다.

 

실제로 인코딩 오류가 발생하면 원인 찾기가 힘드니 반드시 지정해주어야 한다.

 

 

CREATE TABLE구문으로 TABLE을 생성하면 이미 존재하는 테이블이라는 오류가 뜨기 때문에 

IF NOT EXISTS 구문을 추가하여 경고 메세지만 표시하고 프로그램은 실행되도록 처리해주었다.

 

생성자의 update_comp_info() 메서드는 KRX의 주식 코드를 읽어와 company_info 테이블을 업데이트 해주는 기능을 가지고 있다.

 

 

한국거래소 KRX 의 종목 코드 가져오기

한국거래소 사이트에서 제공하는 '상장법인목록.xls' 파일을 다운로드 하여 문자열로 변경하는 코드이다.

 

    # KRX로부터  상장법인 목록 파일을 읽어와 데이터 프레임 변환
    def read_krx_code(self):
        # 1.krx 상장법인목록 url 가져와서 read_html로 읽기
        url = 'https://kind.krx.co.kr/corpgeneral/corpList.do?method=download&searchType=13'
        krx = pd.read_html(url, header = 0)[0]

        # 2.krx 상장법인목록 columns 중 종목코드, 회사명만 가져오기
        krx = krx[['종목코드', '회사명']]
        
        # 3.krx 칼럼을 종목코드 -> code로, 회사명을 company로 변경
        krx = krx.rename(columns={'종목코드':'code', '회사명':'company'})

        # 4.krx 종목코드 6자리에 빠진 0을 추가해준다.
        krx.code = krx.code.map('{06d}'.format)

        return krx # 5.krx를 반환

 

한국거래소 KRX 의 종목 코드 가져오는 순서

1.krx 상장법인목록 url 가져와서 read_html로 읽기

2.krx 상장법인목록 columns 중 종목코드, 회사명만 가져오기

3.krx 칼럼을 종목코드 -> code로, 회사명을 company로 변경

4.krx 종목코드 6자리에 빠진 0을 추가해준다. ex) 삼성전자 종목코드 불러올때 593 -> 005930(실제코드 6자리로 수정) 

5.krx를 반환

 

 

한국거래소 KRX 의 종목 코드 DB에 업데이트 하기

웹 스크래핑으로 읽어온 한국거래소 KRX 종목코드를 mariaDB의 Investar database에 company_info 테이블에 업데이트시켜주도록 하자

 

REPLCE INTO 구문은 Maria DB에서 제공하는 구문으로

동일한 데이터 행이 존재하더라도 오류가 발생하지 않고 UPDATE를 수행한다.

 

REPLCE INTO 구문처럼 INSERT와 UPDATE를 합쳐놓은 기능을 UPSERT라고 부르기도 한다.

 

   # 종목코드를 conpany_info 테이블에 업데이트 한 후 딕셔너리에 저장
    # 오늘 날짜로 업데이트한 기록이 있다면 더이상 업데이트 하지 않음
    def update_comp_info(self):
        sql = "SELECT * FROM company_info" # conpany_info table 의 모든걸 불러오기
        df = pd.read_sql(sql, self.conn)
        for idx in range(len(df)):
            self.codes[df['code'].values[idx]] = df['company'].value[idx]
        
        with self.conn.cursor() as curs:
            sql = "SELECT max(last_update) FROM company_info"
            curs.execute(sql)
            rs = curs.fetchone()
            today = datetime.today().strftime('%Y-%m-%d')

            if rs[0] == None or rs[0].strftime('%Y-%m-%d') < today:
                krx = self.read_krx_code()
                for idx in range(len(krx)):
                    code = krx.code.values[idx]
                    company = krx.company.values[idx]
                    sql = f"REPLACE INTO company_info (code, company, last_update)"\
                        f"VALUES ('{code}, '{company}", "{today}')"
                    curs.execute(sql)
                    self.codes[code] = company
                    tmnow = datetime.now().strftime('%Y-%m-%d %H:%M')
                    print(f"[{tmnow}] {idx:04d} REPLACE INTO company_info "\
                        f"VALUES ({code}, {company}, {today})")
                self.conn.commit()
                print('')

종목코드를 maria DB의 investar DATABASE에 company_info table에 데이터를 밀어 넣었다.

 

실제로 2568개의 행이 저장되어 있음을 확인할 수 있다.

 

krx 상장법인 목록 파일 mariadb에 밀어넣는 과정까지의 코드

# 라이브러리 설정
import pymysql
import pandas as pd
from datetime import datetime

class DBUpdater:
    # 생성자 = MariaDB 연결 및 종목코드 딕셔너리 생성
    def __init__(self):
        self.conn = pymysql.connect(host='localhost', port=3307, user='root',
                                    password='mariadb', db='INVESTAR', charset='utf8')
        
        with self.conn.cursor() as curs:
            sql = """
            CREATE TABLE IF NOT EXISTS company_info (
                code VARCHAR(20),
                company VARCHAR(40),
                last_update DATE,
                PRIMARY KEY(CODE))
            """
            curs.execute(sql)

            sql = """
            CREATE TABLE IF NOT EXISTS daily_price (
                code VARCHAR(20),
                date DATE,
                open BIGINT(20),
                high BIGINT(20),
                low BIGINT(20),
                close BIGINT(20),
                diff BIGINT(20),
                volume BIGINT(20),
                PRIMARY KEY (code, date))
            """
            curs.execute(sql)
        self.conn.commit()

        self.codes = dict()
        self.update_comp_info()
    
    # 소멸자 : MariaDB 연결 해제
    def __del__(self):
        self.conn.close()

    # KRX로부터  상장법인 목록 파일을 읽어와 데이터 프레임 변환
    def read_krx_code(self):
        # krx 상장법인목록 url 가져와서 read_html로 읽기
        url = 'https://kind.krx.co.kr/corpgeneral/corpList.do?method=download&searchType=13'
        krx = pd.read_html(url, header = 0)[0]

        # krx 상장법인목록 columns 중 종목코드, 회사명만 가져오기
        krx = krx[['종목코드', '회사명']]
        
        # krx 칼럼을 종목코드 -> code로, 회사명을 company로 변경
        krx = krx.rename(columns={'종목코드':'code', '회사명':'company'})

        # krx 종목코드 6자리에 빠진 0을 추가해준다.
        krx.code = krx.code.map('{:06d}'.format)

        return krx # krx를 반환

    # 종목코드를 conpany_info 테이블에 업데이트 한 후 딕셔너리에 저장
    # 오늘 날짜로 업데이트한 기록이 있다면 더이상 업데이트 하지 않음
    def update_comp_info(self):
        """종목코드를 company_info 테이블에 업데이트 한 후 딕셔너리에 저장"""
        sql = "SELECT * FROM company_info"
        df = pd.read_sql(sql, self.conn)
        for idx in range(len(df)):
            self.codes[df['code'].values[idx]] = df['company'].values[idx]
                    
        with self.conn.cursor() as curs:
            sql = "SELECT max(last_update) FROM company_info"
            curs.execute(sql)
            rs = curs.fetchone()
            today = datetime.today().strftime('%Y-%m-%d')
            
            if rs[0] == None or rs[0].strftime('%Y-%m-%d') < today:
                krx = self.read_krx_code()
                for idx in range(len(krx)):
                    code = krx.code.values[idx]
                    company = krx.company.values[idx]
                    with self.conn.cursor() as curs:                
                        sql = f"REPLACE INTO company_info (code, company, last"\
                        f"_update) VALUES ('{code}', '{company}', '{today}')"
                        curs.execute(sql)
                        self.codes[code] = company
                        tmnow = datetime.now().strftime('%Y-%m-%d %H:%M')
                        print(f"[{tmnow}] #{idx+1:04d} REPLACE INTO company_info "\
                            f"VALUES ({code}, {company}, {today})")
                    self.conn.commit()
                    print('')              

if __name__ == '__main__':
    dbu = DBUpdater()
    dbu.update_comp_info()

 

stub 코드의 내용을 다 채우기에는 내용이 길어진다.

 

krx 주식정보는 DATABASE 에 밀어넣었고 하루에 1번씩 업데이트 되는 기능까지 구현하였다.

 

다음 포스팅에는 네이버 증권에서 시세를 가지고 오는 나머지 DB구축을 마무리해보자.

 

 

참고도서

http://www.yes24.com/Product/Goods/90578506

파이썬 증권 데이터 분석 - YES24

https://github.com/INVESTAR/StockAnalysisInPython

GitHub - INVESTAR/StockAnalysisInPython