豆腐腦: 4月 2019

2019年4月30日星期二

python 抓取上市及上櫃公司清單, 並寫入 MySQL 資料庫

不囉嗦，直接看程式碼：

# 如何獲得上市上櫃股票清單
import requests
import time
import pandas as pd
from sqlalchemy import create_engine
from sqlalchemy.types import NVARCHAR, Date

def getTWSE(str_mode):
     # 設定爬蟲程式的 User_Agent
    headers = {'user-agent': 'Mozilla/5.0 (Macintosh Intel Mac OS X 10_13_4) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/66.0.3359.181 Safari/537.36'}
    # 上市: http://isin.twse.com.tw/isin/C_public.jsp?strMode=2
    # 上櫃: http://isin.twse.com.tw/isin/C_public.jsp?strMode=4
    req_url = "http://isin.twse.com.tw/isin/C_public.jsp?strMode=%s" % (str_mode)
    res = requests.get(req_url, headers=headers)
    df = pd.read_html(res.text)[0]

    # 設定column名稱
    df.columns = df.iloc[0]
    # 刪除第一行
    df = df.iloc[1:]
    # 先移除row，再移除column，超過三個NaN則移除
    df = df.dropna(thresh=3, axis=0).dropna(thresh=3, axis=1)
    df[['有價證券代號','名稱']] = df['有價證券代號及名稱'].str.split(n=1, expand=True)
    del df['有價證券代號及名稱']
    df = df.set_index('有價證券代號')
    return df

engine = create_engine("mysql+pymysql://stockuser:password@127.0.0.1:3306/stockdb")

dtypedict = {
    '有價證券代號':NVARCHAR(length=32),
    '上市日': Date()
}

# 抓取上市公司股票清單, 寫入MySQL > stockdb > listed_code (listed_code table存在時整個取代)
listed_companies = "2"  #上市公司
mydf1 = getTWSE(listed_companies)
mydf1.to_sql(name="listed_code", con=engine, if_exists = 'replace', index=True, dtype=dtypedict)

# 先睡個10秒鐘吧
time.sleep(10)

# 抓取上櫃公司股票清單, 寫入MySQL > stockdb > listed_code (append 到 listed_code table)
listed_companies = "4" #上櫃公司
mydf2 = getTWSE(listed_companies)
mydf2.to_sql(name="listed_code", con=engine, if_exists = 'append', index=True, dtype=dtypedict)

2019年4月29日星期一

python 讀取 MySQL 資料

sqlalchemy連結MySQL時，必須先安裝另一模組 pymysql

pip install pymysql

接下來利用 python 來讀取先前我們用R寫入的股價資料庫吧

%matplotlib inline

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine("mysql+pymysql://stockuser:password@127.0.0.1:3306/stockdb")

sql ="select * from tw1101;"

df = pd.read_sql_query(sql, engine)

print(df)

df.close.plot()

執行結果：

  row_names        date       open       high        low      close  \
0            1  2009-01-02  24.193001  24.193001  24.193001  24.193001   
1            2  2009-01-05  25.135599  25.225300  24.597000  24.821400   
2            3  2009-01-06  24.955999  25.135599  24.372499  24.641800   
3            4  2009-01-07  25.090700  25.449800  24.417400  25.225300   
4            5  2009-01-08  24.372499  24.731600  23.789000  24.417400   
5            6  2009-01-09  24.552099  25.090700  23.789000  23.789000   
...
          volume   adjusted  
0            0.0  14.254436  
1     22712510.0  14.624688  
2     20298561.0  14.518868  
3     26370751.0  14.862664  
4     23748492.0  14.386651
...
2536  30751985.0  41.000000  
2537  23165336.0  41.599998  
2538  16390191.0  41.549999  
2539  13459252.0  41.799999  
2540  11300709.0  41.750000  

[2541 rows x 8 columns]

2019年4月28日星期日

R tidyquant 抓取股價資料寫入 MySQL 資料庫

MySQL上先建立一個新使用者 stockuser ：

mysql -u root -p

出現 mysql > 後，開始建立新用者資料並設定密碼：

mysql >
mysql > GRANT ALL PRIVILEGES ON *.* TO 'stockuser'@'%' IDENTIFIED BY 'password';
mysql > FLUSH PRIVILEGES;
mysql > quit;

若MySQL伺服器要開放遠端連結，記得防火牆允入3306埠，另外 MySQL的設定檔也要檢查：

vi /etc/mysql/my.cnf

記得 bind-address = 127.0.0.1 這行要註解

#bind-address           = 127.0.0.1

也記得 MySQL 要重新啟動

service mysql restart

資料庫部分沒問題的話，就可以準備來使用R抓取股價資料~R 如果還沒有安裝過 RMySQL package, 也記得先安裝：

install.packages("RMySQL")

確認安裝完成後，就開始吧...

library(RMySQL)

library(DBI)
library(tidyquant)
library(dplyr)

cn <- dbConnect(
 drv = RMySQL::MySQL(),
 username='stockuser',
 password='password',
 host='127.0.0.1',
 port = 3306,
 dbname = "stockdb"
 )

#  利用 tidyquant 取得 Yahoo 上的台灣五十股價資料, 寫入 MySQL stockdb > tw{股票代碼}
tw50 <- c("1101","1102","1216") #只有示範三檔股票
for (i in tw50) {
 stockid <- paste(i,"TW",sep=".")
 tblname <- paste("tw",i,sep="")
 print(stockid)
 sdata <- tq_get(stockid)
 dbWriteTable(cn, name=tblname, value=sdata, overwrite=TRUE)
 Sys.sleep(10) # 每次 Loop 先休息 10 秒
}

# 利用 tidyquant 取得 Yahoo 上的IBM股價資料, 寫入 MySQL stockdb > ibm
ibm <- tq_get("IBM")
dbWriteTable(cn, name="ibm", value=ibm, overwrite=TRUE)

dbDisconnect(cn) #記得斷開資料庫連線

資料都抓取完成後，MySQL資料庫裡面 stockdb > 就會多個股 table ：

mysql -u stockuser -p
輸入密碼，登入後...
mysql> use stockdb;
Database changed
mysql> show tables;
+-------------------+
| Tables_in_stockdb |
+-------------------+
| ibm               |
| tw1101            |
| tw1102            |
| tw1216            |
+-------------------+
4 rows in set (0.00 sec)

以後就可以直接從 MySQL 中讀取股價資料來運用：

# 從 MySQL 取出 IBM 股價資料
cn <- src_mysql(
 user='stockuser',
 password='password',
 host='127.0.0.1',
 port = 3306,
 dbname = "stockdb"
 )

ibm_table <- tbl(cn, "ibm")
ibm_table

dbDisconnect(cn)

延伸閱讀資料：

Amazon RDS + R (其實我是租不起, 所以自建 MySQL...)

R學習筆記 - 資料工程篇(四) Database

2019年4月30日 星期二