파이썬 퀀트투자(4): 한국거래소 데이터 Mysql에 적재

지난 번에 한국거래소의 업종 분류 형황 데이터를 크롤링하는 방법과 한국거래소의 개별종목 지표 데이터를 크롤링하는 방법에 대해서 알아보았습니다. 이제 이 두개의 데이터를 하나로 합친 후, Mysql에 적재할 것입니다.

 

먼저 두 데이터에 공통으로 존재하지 않는 종목, 즉 하나의 데이터에만 존재하는 종목을 살펴보겠습니다.(앞 글에서 사용한 코드를 그대로 이어서 사용했습니다. 전체 코드는 이 글을 참고해주세요.)

 

두 데이터를 set 형태로 변경한 후, symmetric_difference() 메서드를 사용하면, 하나의 데이터에만 존재하는 종목을 찾을 수 있습니다.

 

diff = list(set(krx_sector['종목명']).symmetric_difference(set(krx_ind['종목명'])))
print(diff)

 

결과

['이스트아시아홀딩스', '신한글로벌액티브리츠', 'NH프라임리츠', 'JTC', 'SBI핀테크솔루션즈', 'GRT', '미래산업 [락]', 'SK리츠', 'NH올원리츠', '네오이뮨텍', '미래에셋글로벌리츠', '헝셩그룹', '디앤디플랫폼리츠', '마스턴프리미어리츠', '이리츠코크렙', '케이탑리츠', '코람코더원리츠', '에이리츠', '한화리츠', 'ESR켄달스퀘어리츠', '엑세스바이오', '삼성FN리츠', '씨엑스아이', '스타에스엠리츠', '제이알글로벌리츠', '엘브이엠씨홀딩스', '고스트스튜디오', '맵스리얼티1', '맥쿼리인프라', '코람코라이프인프라리츠', '신한알파리츠', '신한서부티엔디리츠', '애머릿지', '잉글우드랩', '오가닉티코스메틱', '이지스밸류리츠', '미래산업', '윙입푸드', '글로벌에스엠', 'KB스타리츠', '로스웰', '골든센츄리', '코오롱티슈진', '컬러레이', '소마젠', '미래에셋맵스리츠', '한국패러랠', '이지스레지던스리츠', '한국ANKOR유전', '크리스탈신소재', '롯데리츠', '프레스티지바이오파마']

 

 

위 종목들은 선박펀드, 광물펀드, 해외종목 등 일반적이지 않은 종목들입니다. 그럼 이제 두 데이터를 merge()함수를 통해 하나로 합쳐줍니다. intersection() 메서드를 사용하면, 공통으로 존재하는 열을 기준으로 입력해줍니다. 여기서는 '종목코드, 종목명, 종가, 대비, 등락률'이 공통으로 존재하는 열입니다.

 

kor_ticker = pd.merge(krx_sector,
                      krx_ind,
                      on=krx_sector.columns.intersection(
                          krx_ind.columns).tolist(),
                      how='outer')

kor_ticker.head()

 

 

마지막으로 일적인 종목과 스팩(SPAC), 우선주, 리츠, 기타 주식을 구분해줍니다.

1. 스팩 종목은 종목명에 '스팩' 혹은 '제n호' 라는 단어가 들어간다. 따라서 contains() 메서드를 통해 종목명에 '스팩'이 들어가거나 정규 표현식을 이용해 '제n호'라는 문자가 들어간 종목명을 찾습니다.
2. 국내 종목 중 종목코드 끝이 0이 아닌 종목은 우선주에 해당합니다.
3. 리츠 종목은 종목명이 '리츠'로 끝납니다. 따라서 endswith() 메서드를 통해 이러한 종목을 찾습니다. (메리츠화재 등의 종목도 중간에 리츠라는 단어가 들어가므로 contains() 함수를 이용하면 안됩니다.)

numpy 패키지의 where() 함수를 통해 각 조건에 맞는 종목구분을 입력합니다. 종목명에 '스팩' 혹은 '제n호'가 포함된 종목은 스팩으로, 종목코드 끝이 0이 아닌 종목은 '우선주'로, 종목명이 '리츠'로 끝나는 종목은 '리츠'로, 선박펀드, 광물펀드, 해외종목 등은 '기타'로(위에서 diff에 저장해놓은 데이터), 나머지 종목들은 '보통주'로 구분합니다.

import numpy as np

kor_ticker['종목구분'] = np.where(kor_ticker['종목명'].str.contains('스팩|제[0-9]+호'), '스팩',
                              np.where(kor_ticker['종목코드'].str[-1:] != '0', '우선주',
                                       np.where(kor_ticker['종목명'].str.endswith('리츠'), '리츠',
                                                np.where(kor_ticker['종목명'].isin(diff),  '기타',
                                                '보통주'))))

 

그 후, replace() 메서드로 열 이름의 공백을 제거하고, 필요한 열만 선택해줍니다. SQL에는 NaN이 입력되지 않으므로 replace() 메서드로 None으로 변경해줍니다. 기준일은 to_datetime() 메서드를 이용해 yyyymmdd에서 yyyy-mm-dd 형태로 변경해줍니다.

 

kor_ticker = kor_ticker.reset_index(drop=True)
kor_ticker.columns = kor_ticker.columns.str.replace(' ', '')
kor_ticker = kor_ticker[['종목코드', '종목명', '시장구분', '종가',
                         '시가총액', '기준일', 'EPS', '선행EPS', 'BPS', '주당배당금', '종목구분']]
kor_ticker = kor_ticker.replace({np.nan: None})
kor_ticker['기준일'] = pd.to_datetime(kor_ticker['기준일'])

kor_ticker.head()

 

 

이제 해당 정보를 DB에 저장해야합니다. 먼저 MySQL에서 아래의 쿼리를 입력해 데이터베이스(stock_db)를 만든 후, 국내 티커정보가 들어갈 테이블(kor_ticker)을 만들어줍니다.

 

create database stock_db;

use stock_db;

create table kor_ticker
(
    종목코드 varchar(6) not null,
    종목명 varchar(20),
    시장구분 varchar(6),
    종가 float,
    시가총액 float,
    기준일 date,
    EPS float,
    선행EPS float,
    BPS float,
    주당배당금 float,
    종목구분 varchar(5),
    primary key(종목코드, 기준일)
);

 

마지막으로, 파이썬에서 아래 코드를 실행하면 다운로드 받은 정보가 kor_ticker 테이블에 upsert 형태로 저장됩니다. 

 

import pymysql

con = pymysql.connect(user='root',
                      passwd='비밀번호',
                      host='127.0.0.1',
                      db='stock_db',
                      charset='utf8')

mycursor = con.cursor()
query = f"""
    insert into kor_ticker (종목코드,종목명,시장구분,종가,시가총액,기준일,EPS,선행EPS,BPS,주당배당금,종목구분)
    values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) as new
    on duplicate key update
    종목명=new.종목명,시장구분=new.시장구분,종가=new.종가,시가총액=new.시가총액,EPS=new.EPS,선행EPS=new.선행EPS,
    BPS=new.BPS,주당배당금=new.주당배당금,종목구분 = new.종목구분;
"""

args = kor_ticker.values.tolist()

mycursor.executemany(query, args)
con.commit()

con.close()

 

 

코드를 하나하나 뜯어 보겠습니다. 데이터베이스에 쿼리를 실행하려면 커서를 생성해야 합니다.

mycursor = con.cursor()

 

cursor() 메서드는 쿼리를 실행하고 결과를 가져오는 역할을 합니다.

 

아래 쿼리는 주식 관련 데이터를 데이터베이스의 kor_ticker 테이블에 삽입하거나, 이미 해당 데이터가 있을 경우 업데이트하는 역할을 합니다. 이를 위해 INSERT INTO와 ON DUPLICATE KEY UPDATE 구문을 사용합니다. 

query = f"""
    insert into kor_ticker (종목코드, 종목명, 시장구분, 종가, 시가총액, 기준일, EPS, 선행EPS, BPS, 주당배당금, 종목구분)
    values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) as new
    on duplicate key update
    종목명=new.종목명, 시장구분=new.시장구분, 종가=new.종가, 시가총액=new.시가총액, EPS=new.EPS, 선행EPS=new.선행EPS,
    BPS=new.BPS, 주당배당금=new.주당배당금, 종목구분=new.종목구분;
"""

 

• INSERT INTO kor_ticker (...) VALUES (%s, %s, ...): kor_ticker 테이블에 값을 삽입합니다. %s는 각 값이 들어갈 자리입니다.
• ON DUPLICATE KEY UPDATE: 기본 키(종목코드 등)가 중복되면, 새로운 데이터로 기존 데이터를 업데이트합니다. 이를 통해 기존 데이터가 존재할 경우, 덮어쓰기가 가능합니다.
• 위에서 테이블을 만들 때, PK로 지정해놓은 종목코드, 기준일은 ON DUPLICATE KEY UPDATE 구분에 넣지 않아야 정상적으로 upsert 방식이 적용됩니다.

as new를 사용하여 새로운 값을 new라는 별칭으로 부여한 후, UPDATE 부분에서 이를 활용해 기존 필드를 업데이트합니다.

 

kor_ticker라는 데이터 프레임에서 리스트 형태로 데이터를 가져와 executemany() 메서드를 사용해 다수의 데이터를 한 번에 삽입합니다.

 

args = kor_ticker.values.tolist()
mycursor.executemany(query, args)

 

 

• kor_ticker.values.tolist(): pandas의 데이터프레임 kor_ticker에서 값들을 리스트로 변환하여 SQL 쿼리에 전달할 수 있는 형식으로 만듭니다.
• executemany(): 여러 개의 데이터(args)를 한 번에 삽입할 수 있게 도와줍니다. 이 방법은 다수의 레코드를 효율적으로 처리할 수 있습니다.

모든 쿼리가 정상적으로 실행된 후, commit() 메서드를 호출해 데이터베이스에 변경 사항을 저장하고, 마지막으로 연결을 종료합니다.

 

con.commit()
con.close()

 

 

commit()은 트랜잭션을 완료하고 변경 사항을 데이터베이스에 반영합니다. con.close()는 데이터베이스 연결을 종료합니다.

 

 

전체 코드

import numpy as np
import pymysql

diff = list(set(krx_sector['종목명']).symmetric_difference(set(krx_ind['종목명'])))
print(diff)

kor_ticker = pd.merge(krx_sector,
                      krx_ind,
                      on=krx_sector.columns.intersection(
                          krx_ind.columns).tolist(),
                      how='outer')

kor_ticker['종목구분'] = np.where(kor_ticker['종목명'].str.contains('스팩|제[0-9]+호'), '스팩',
                              np.where(kor_ticker['종목코드'].str[-1:] != '0', '우선주',
                                       np.where(kor_ticker['종목명'].str.endswith('리츠'), '리츠',
                                                np.where(kor_ticker['종목명'].isin(diff),  '기타',
                                                '보통주'))))
kor_ticker = kor_ticker.reset_index(drop=True)
kor_ticker.columns = kor_ticker.columns.str.replace(' ', '')
kor_ticker = kor_ticker[['종목코드', '종목명', '시장구분', '종가',
                         '시가총액', '기준일', 'EPS', '선행EPS', 'BPS', '주당배당금', '종목구분']]
kor_ticker = kor_ticker.replace({np.nan: None})
kor_ticker['기준일'] = pd.to_datetime(kor_ticker['기준일'])

con = pymysql.connect(user='root',
                      passwd='1234',
                      host='127.0.0.1',
                      db='stock_db',
                      charset='utf8')

mycursor = con.cursor()
query = f"""
    insert into kor_ticker (종목코드,종목명,시장구분,종가,시가총액,기준일,EPS,선행EPS,BPS,주당배당금,종목구분)
    values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) as new
    on duplicate key update
    종목명=new.종목명,시장구분=new.시장구분,종가=new.종가,시가총액=new.시가총액,EPS=new.EPS,선행EPS=new.선행EPS,
    BPS=new.BPS,주당배당금=new.주당배당금,종목구분 = new.종목구분;
"""

args = kor_ticker.values.tolist()

mycursor.executemany(query, args)
con.commit()

con.close()

 

 

참고자료

https://github.com/hyunyulhenry/quant_py/blob/main/data_korea.ipynb

 

다음편

파이썬 퀀트투자(5): WICS 기준 섹터정보 크롤링, 적재