[금융데이터분석] 파이썬이용하여 코스피 대장주 찾기 Project-2(섹터 DB저장)

업종 & 섹터 산출

 

1. 업종 분류 현황 크롤링 (코스피 상장사)

 ※ 업종 분류 현황 크롤링하는 자세한 방법에 대해서는 하기 포스팅 참고 하면 좋을것 같다

 

https://wg-cy.tistory.com/54?category=1023254 

[데이터수집] 한국 거래소 업종 분류 현황 및 개별 지표 크롤링 하기

 

코드를 바로 보면 하기 처럼 하면 된다. 

import requests as rq
from io import BytesIO
import pandas as pd

# generate.cmd에서 Request URL과 동일
gen_otp_url = 'http://data.krx.co.kr/comm/fileDn/GenerateOTP/generate.cmd'

# generate.cmd에서 Form Data와 동일
gen_otp_data = {
  'mktId': 'STK',
  'trdDd': '20211212',
  'money': '1',
  'csvxls_isNo': 'false',
  'name': 'fileDown',
  'url': 'dbms/MDC/STAT/standard/MDCSTAT03901'
}

# 헤더 부분에 리퍼러(Referer)를 추가합니다. 
# 리퍼러란 링크를 통해서 각각의 웹사이트로 방문할 때 남는 흔적입니다. (로봇으로 인식을 하지 않게 하기 위함.)
headers = {'Referer': 'http://data.krx.co.kr/contents/MDC/MDI/mdiLoader'}
otp = rq.post(gen_otp_url, gen_otp_data, headers=headers).text

down_url = 'http://data.krx.co.kr/comm/fileDn/download_csv/download.cmd'
down_sector_KS  = rq.post(down_url, {'code':otp}, headers=headers)
sector_KS = pd.read_csv(BytesIO(down_sector_KS.content), encoding='EUC-KR')
sector_KS

결과가 잘 나오는 것을 알 수 있다.

 

종목코드, 종목명, 시장구분까지만 남기고 나머지는 삭제하도록 하겠다.

 

sector_KS = sector_KS[['종목코드', '종목명', '시장구분','업종명']]
sector_KS.rename(columns = {'종목코드' : 'code'}, inplace = True)
sector_KS.rename(columns = {'종목명' : 'company'}, inplace = True)
sector_KS.rename(columns = {'시장구분' : 'market'}, inplace = True)
sector_KS.rename(columns = {'업종명' : 'sector'}, inplace = True)

sector_KS

잘 적용된 것을 볼 수 있다.

 

2. 업종 섹터를 데이터베이스(MySQL)에 저장하기

 

import pymysql, calendar, time, json, urllib
import pandas as pd
from datetime import datetime
import requests
from bs4 import BeautifulSoup
from threading import Timer

# MySQL에서 Investar DB에 접속
conn = pymysql.connect(host='localhost', user='root', passwd='여러분비번', db='INVESTAR', charset='utf8')
with conn.cursor() as curs:
    sql = """
        CREATE TABLE IF NOT EXISTS sector_info(
        code VARCHAR(20),
        company VARCHAR(40),
        market VARCHAR(40),
        sector VARCHAR(40),
        last_update DATE,
        PRIMARY KEY(code))
        """
    curs.execute(sql)

잘 생선된 것을 볼 수 있다.

 

이제 sector_KS의 정보를 MySQL의 sector_info table에 저장을 해보겠다.

 

sql = 'SELECT * FROM sector_info'
sector_table = pd.read_sql(sql, conn) # sector_info Table을 read_sql()함수로 읽는다.
with conn.cursor() as curs:
    sql = "SELECT max(last_update) FROM company_info" # SELECT max() 구문은 DB에서 가장 최근 업데이트 날짜를 가져온다.
    curs.execute(sql)
    rs = curs.fetchone() # fetchone 메서드를 사용하여 데이타를 서버로부터 가져온 후, Fetch 된 데이타를 사용한다.
    today = datetime.today().strftime('%Y-%m-%d')
    for idx in range(len(sector_KS)):
        code = sector_KS.code.values[idx]
        company = sector_KS.company.values[idx]
        market = sector_KS.market.values[idx]
        sector = sector_KS.sector.values[idx]
        sql = f"REPLACE INTO sector_info (code, company, market, sector,last"\
              f"_update) VALUES ('{code}', '{company}', '{market}', '{sector}','{today}');"
        curs.execute(sql) # Replace Info 구문을 이용해서, 코드,회사명, 시장, 업종행을 DB에 저장
        print(f"[{tmnow}] {idx:04d} REPLACE INFO sector_info (code, company, last)"\
                          f"_update) VALUES ('{code}', '{company}', '{today}')")
    conn.commit()

 

해봤더니, 하기와 같이 오류가 발생했다.

구글링을 해보니, 발생 가능한 error를 보니 하기 3가지 case가 있다고 한다.

그래서 내가 만든 sector_info table을 다시 점검 해보기로 했다.

점검 해보니 sector에 대한 field가 없다고 나온다. 내가 처음에 sector_info Table을 만들때 sector를 빼먹었는데, 

다시 코드를 수정해서 실행 시켰더니, 정상 동작을해서 생성된줄 알았는데 그게 아니었나 보다.

 

table을 지우려고 시도해 보았다.

여기서 다음 단계로 넘어가지가 않는다.. 3번시도해도 동일..

그래서 field를 추가해보기로 했다.

지정 컬럼 다음에 추가 하는 방법이다.

ALTER TABLE `테이블명` ADD `새컬럼명` 자료형 AFTER `앞컬럼명`

alter table sector_info add sector varchar(100);

잘 동작 하는 것을 볼  수 있다. 이것도 동일하게 다음 단계로 안넘어갔었는데, SQL을 재실행하니 정상 동작했다.

아무래도 SQL이 잠시 오류가 있었던 것 같다.

 

자 위의 코드를 다시 실행해보자,

 

sql = 'SELECT * FROM sector_info'
sector_table = pd.read_sql(sql, conn) # sector_info Table을 read_sql()함수로 읽는다.

with conn.cursor() as curs:
    sql = "SELECT max(last_update) FROM company_info" # SELECT max() 구문은 DB에서 가장 최근 업데이트 날짜를 가져온다.
    curs.execute(sql)
    rs = curs.fetchone() # fetchone 메서드를 사용하여 데이타를 서버로부터 가져온 후, Fetch 된 데이타를 사용한다.
    today = datetime.today().strftime('%Y-%m-%d')
    for idx in range(len(sector_KS)):
        code = sector_KS.code.values[idx]
        company = sector_KS.company.values[idx]
        market = sector_KS.market.values[idx]
        sector = sector_KS.sector.values[idx]
        sql = f"REPLACE INTO sector_info (code, company, market, sector,last"\
              f"_update) VALUES ('{code}', '{company}', '{market}', '{sector}','{today}');"
        curs.execute(sql) # Replace Info 구문을 이용해서, 코드,회사명, 시장, 업종행을 DB에 저장
        tmnow = datetime.now().strftime('%Y-%m-%d %H:%M')
        print(f"[{tmnow}] {idx:04d} REPLACE INFO sector_info (code, company, last)"\
                          f"_update) VALUES ('{code}', '{company}', '{today}')")
    conn.commit()

잘 동작 하는 것을 볼 수 있다.

 

MySQL Database도 한번더 check 해보자

select * from sector_info;