Created
June 4, 2017 11:36
-
-
Save KimMyungSam/2ad8a2a97975a111263c0c9d4c258c1e to your computer and use it in GitHub Desktop.
KRX 종목 마스터 만들기
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
{ | |
"cells": [ | |
{ | |
"cell_type": "markdown", | |
"metadata": {}, | |
"source": [ | |
"# KRX 종목 마스터 만들기\n", | |
"거래소 상장회사검색 http://marketdata.krx.co.kr/contents/MKD/04/0406/04060100/MKD04060100.jsp" | |
] | |
}, | |
{ | |
"cell_type": "code", | |
"execution_count": 1, | |
"metadata": { | |
"collapsed": true | |
}, | |
"outputs": [], | |
"source": [ | |
"import requests\n", | |
"import pandas as pd\n", | |
"import io\n", | |
"import mysql.connector\n", | |
"import sqlalchemy\n", | |
"from sqlalchemy import create_engine" | |
] | |
}, | |
{ | |
"cell_type": "code", | |
"execution_count": 2, | |
"metadata": { | |
"collapsed": true | |
}, | |
"outputs": [], | |
"source": [ | |
"# pwd=input('Enter Password for server:')\n", | |
"pwd = 'rlaehgus1'\n", | |
"engine = create_engine('mysql+mysqlconnector://root:'+pwd+'@localhost/findb', echo=False)\n", | |
"\n", | |
"def get_krx_stock_master():\n", | |
" # STEP 01: Generate OTP\n", | |
" gen_otp_url = 'http://marketdata.krx.co.kr/contents/COM/GenerateOTP.jspx'\n", | |
" gen_otp_data = {\n", | |
" 'name':'fileDown',\n", | |
" 'filetype':'xls',\n", | |
" 'url':'MKD/04/0406/04060100/mkd04060100_01',\n", | |
" 'market_gubun':'ALL', # ''ALL':전체, STK': 코스피\n", | |
" 'isu_cdnm':'전체',\n", | |
" 'sort_type':'A',\n", | |
" 'std_ind_cd':'01',\n", | |
" 'lst_stk_vl':'1',\n", | |
" 'in_lst_stk_vl':'',\n", | |
" 'in_lst_stk_vl2':'',\n", | |
" 'pagePath':'/contents/MKD/04/0406/04060100/MKD04060100.jsp',\n", | |
" }\n", | |
"\n", | |
" r = requests.post(gen_otp_url, gen_otp_data)\n", | |
" code = r.content\n", | |
"\n", | |
" # STEP 02: download\n", | |
" down_url = 'http://file.krx.co.kr/download.jspx'\n", | |
" down_data = {\n", | |
" 'code': code,\n", | |
" }\n", | |
" \n", | |
" #io를 사용하여 다운로드 파일을 받지않고 file type으로 만들어준다\n", | |
" r = requests.post(down_url, down_data)\n", | |
" f = io.BytesIO(r.content)\n", | |
" \n", | |
" usecols = ['종목코드', '기업명', '업종코드','업종','상장주식수(주)','자본금(원)','액면가(원)', '대표전화', '주소']\n", | |
" df = pd.read_excel(f, converters={'종목코드': str, '업종코드': str}, usecols=usecols)\n", | |
" df.columns = ['code', 'name', 'sector_code', 'sector','lilsted_stock','capital','par_value','telephone', 'address']\n", | |
" return df\n" | |
] | |
}, | |
{ | |
"cell_type": "code", | |
"execution_count": 5, | |
"metadata": { | |
"collapsed": false | |
}, | |
"outputs": [], | |
"source": [ | |
"# 상장회사 목록을 listed_company table명으로 만든다.\n", | |
"\n", | |
"if __name__ == \"__main__\":\n", | |
" \n", | |
" df = get_krx_stock_master()\n", | |
" df.to_sql(name='listed_company', con=engine, if_exists='replace',index=False)" | |
] | |
}, | |
{ | |
"cell_type": "markdown", | |
"metadata": { | |
"collapsed": false, | |
"scrolled": true | |
}, | |
"source": [ | |
"### database table에서 data 불러오기...\n", | |
"### sql = 'SELECT * from 테이블이름' , 모두불러오기\n", | |
"### df = pd.read_sql(sql, con=engine) , sql문 전달하기\n", | |
"### sql = 'SELECT code FROM company WHERE sector LIKE \"%제조업%\"' , company테이블에서 sector필드중 제조업 단어가 들어있는 모든 row 추출.." | |
] | |
} | |
], | |
"metadata": { | |
"kernelspec": { | |
"display_name": "Python 3", | |
"language": "python", | |
"name": "python3" | |
}, | |
"language_info": { | |
"codemirror_mode": { | |
"name": "ipython", | |
"version": 3 | |
}, | |
"file_extension": ".py", | |
"mimetype": "text/x-python", | |
"name": "python", | |
"nbconvert_exporter": "python", | |
"pygments_lexer": "ipython3", | |
"version": "3.6.0" | |
} | |
}, | |
"nbformat": 4, | |
"nbformat_minor": 2 | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment