Created
June 3, 2017 16:49
-
-
Save KimMyungSam/bfa3a8595284b7fe77dada8dab1ab38e to your computer and use it in GitHub Desktop.
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": [ | |
"# 종목 마스터 만들기\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", | |
"\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", | |
" 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', 'telephone', 'address']\n", | |
" return df\n" | |
] | |
}, | |
{ | |
"cell_type": "code", | |
"execution_count": 6, | |
"metadata": { | |
"collapsed": false, | |
"scrolled": true | |
}, | |
"outputs": [ | |
{ | |
"name": "stdout", | |
"output_type": "stream", | |
"text": [ | |
" code name sector_code sector telephone \\\n", | |
"0 060310 3S 032602 전자부품 제조업 02-896-9474 \n", | |
"1 095570 AJ네트웍스 126903 산업용 기계 및 장비 임대업 02-6240-0800 \n", | |
"2 068400 AJ렌터카 126901 운송장비 임대업 1544-1600 \n", | |
"3 006840 AK홀딩스 137105 회사본부, 지주회사 및 경영컨설팅 서비스업 02-6923-2921 \n", | |
"4 054620 APS홀딩스 032902 특수 목적용 기계 제조업 031-776-1800 \n", | |
"\n", | |
" address \n", | |
"0 서울특별시 금천구 시흥대로71길 30-1 \n", | |
"1 서울특별시 송파구 정의로8길 9 (문정동,AJ빌딩) \n", | |
"2 서울특별시 구로구 서부샛길 822 \n", | |
"3 서울특별시 구로구 구로중앙로 152(구로동) \n", | |
"4 경기도 화성시 동탄면 동탄산단9길 23-12 (동탄면) \n" | |
] | |
} | |
], | |
"source": [ | |
"if __name__ == \"__main__\":\n", | |
" df = get_krx_stock_master()\n", | |
" df.to_sql(name='stock_list', con=engine, if_exists='replace',index=False)\n", | |
" sql = 'SELECT * from stock_list'\n", | |
" df = pd.read_sql(sql, con=engine)\n", | |
"\n", | |
" print(df.head())" | |
] | |
}, | |
{ | |
"cell_type": "code", | |
"execution_count": 4, | |
"metadata": { | |
"collapsed": false | |
}, | |
"outputs": [ | |
{ | |
"data": { | |
"text/plain": [ | |
"1268" | |
] | |
}, | |
"execution_count": 4, | |
"metadata": {}, | |
"output_type": "execute_result" | |
} | |
], | |
"source": [ | |
"sql = 'SELECT code FROM stock_list WHERE sector LIKE \"%제조업%\"'\n", | |
"df = pd.read_sql(sql, con=engine)\n", | |
"len(df)" | |
] | |
} | |
], | |
"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