import pandas as pd
import numpy as np
import requests

from bs4 import BeautifulSoup

from io import BytesIO

def stock_master():
    url = 'http://kind.krx.co.kr/corpgeneral/corpList.do'
    data = {
        'method':'download',
        'orderMode':'1',           # 정렬컬럼
        'orderStat':'D',           # 정렬 내림차순
        'searchType':'13',         # 검색유형: 상장법인
        'fiscalYearEnd':'all',     # 결산월: 전체
        'location':'all',          # 지역: 전체
    }

    r = requests.post(url, data=data)
    f = BytesIO(r.content)
    dfs = pd.read_html(f, header=0, parse_dates=['상장일'])
    df = dfs[0].copy()

    # 숫자를 앞자리가 0인 6자리 문자열로 변환
    df['종목코드'] = df['종목코드'].astype(np.str)   
    df['종목코드'] = df['종목코드'].str.zfill(6)
    return df

df_master = stock_master()
df_master.head()

df = df_master.loc[:, ['회사명', '종목코드', '업종', '상장일', '결산월']]
df.head()

import pandas as pd
import numpy as np
import requests
from io import BytesIO
from datetime import datetime

def stock_master_price(date=None):
    if date == None:
        date = datetime.today().strftime('%Y%m%d')   # 오늘 날짜

    # STEP 01: Generate OTP
    gen_otp_url = 'http://marketdata.krx.co.kr/contents/COM/GenerateOTP.jspx'
    gen_otp_data = {
        'name':'fileDown',
        'filetype':'xls',
        'url':'MKD/04/0404/04040200/mkd04040200_01',
        'market_gubun':'ALL',                          #시장구분: ALL=전체
        'indx_ind_cd':'',
        'sect_tp_cd':'',
        'schdate': date,
        'pagePath':'/contents/MKD/04/0404/04040200/MKD04040200.jsp',
    }
    
    r = requests.post(gen_otp_url, gen_otp_data)
    code = r.content  # 리턴받은 값을 아래 요청의 입력으로 사용.
    
    # STEP 02: download
    down_url = 'http://file.krx.co.kr/download.jspx'
    down_data = {
        'code': code,
    }
    
    r = requests.post(down_url, down_data)
    df = pd.read_excel(BytesIO(r.content), header=0, thousands=',')
    return df

df = stock_master_price()
df.head()

date = datetime(2017, 3, 3).strftime('%Y%m%d')
df = stock_master_price(date)
df.head()

len(df)

import pandas as pd
import numpy as np
import requests
import io

def get_krx_stock_master():
    # STEP 01: Generate OTP
    gen_otp_url = 'http://marketdata.krx.co.kr/contents/COM/GenerateOTP.jspx'
    gen_otp_data = {
        'name':'fileDown',
        'filetype':'xls',
        'url':'MKD/04/0406/04060100/mkd04060100_01',
        'market_gubun':'ALL',                          # ALL:전체
        'isu_cdnm':'전체',
        'sort_type':'A',                               # 정렬 : A 기업명
        'std_ind_cd':'01',
        'cpt':'1',
        'in_cpt':'',
        'in_cpt2':'',
        'pagePath':'/contents/MKD/04/0406/04060100/MKD04060100.jsp',
    }  # Query String Parameters

    r = requests.post(gen_otp_url, gen_otp_data)
    code = r.content

    # STEP 02: download
    down_url = 'http://file.krx.co.kr/download.jspx'
    down_data = {
        'code': code,
    }

    r = requests.post(down_url, down_data)
    f = io.BytesIO(r.content)
    
    usecols = ['종목코드', '기업명', '업종코드', '업종']
    df = pd.read_excel(f, converters={'종목코드': str, '업종코드': str}, usecols=usecols)
    df.columns = ['code', 'name', 'sector_code', 'sector']
    return df

df_master = get_krx_stock_master()
df_master.head()

import mysql.connector
from sqlalchemy import create_engine

con_str = 'mysql+mysqlconnector://woosa7:finda***@localhost/findb'
engine = create_engine(con_str, echo=False)

insert_update_sql = """
    insert into stock_master (code, name, sector_code, sector) 
    values (%s,%s,%s,%s)
    on duplicate key update
        code=values(code),
        name=values(name),
        sector_code=values(sector_code),
        sector=values(sector)
"""

for ix, r in df_master.iterrows():
    engine.execute(insert_update_sql, (r['code'], r['name'], r['sector_code'], r['sector']))
    print(r['code'], r['name'])