2024年5月17日 星期五

Python 學習筆記 : 網頁爬蟲實戰 (八) 台股每日盤後資訊網頁

今天繼續來玩 Python 爬蟲, 本篇要爬的對象是證交所台股每日盤後資訊網頁, 十幾年前我用 PHP 爬這張網頁時覺得處理起來很麻煩 (那時的網頁是將全部上市股票擠在一張網頁, 用字串處理技巧硬剖), 現在改用 Python 相對簡單多了. 

本系列之前的筆記參考 : 


本篇測試部分作法參考了下面這本書中的範例加以改寫 : 



一. 檢視目標網頁 :  

證交所台股每日盤後資訊網頁之網址如下 :





不過這個網頁只是大盤統計資料而已, 我們要的標的網頁必須勾選上面的 "分類" 選項, 勾選 "全部 (不含權證, 牛熊證, 可展延牛熊證)" 後按查詢才會看到 : 




但得到的網頁包含了 8 張表格, 我們的目標資訊是最底下的那張表格 :




由於查詢後網址不變, 可見網頁中的這 8 張表格是透過 Ajax/XHR 產生的, 這可以用 Chrome 擴充功能 Quick Javascript Switcher 關掉 Javascript 功能印證 (關掉就看不到這些表格), 所以可以利用 Chrome 開發者功能的 Networking/XHR 功能來觀察後端提供表格內容的網址, 可取得證交所不公開的 API 來直接擷取資料 (通常是 JSON 檔). 

首先在瀏覽器上按 F12 打開開發者視窗, 然後重新整理上面台股每日盤後資訊網頁, 開發者工具視窗點選 "Networking" 與 "Fetch/XHR", 點擊左邊的回應網址, 看看右邊 Response 頁籤中是否有我們要的目標資料 (通常是檔案較大的那個) :




找到目標後在左邊回應網址上按滑鼠右鍵選 "Copy/Copy URL" 將網址複製下來 :

https://www.twse.com.tw/rwd/zh/afterTrading/MI_INDEX?date=20240513&type=ALLBUT0999&response=json&_=1715650102878

這就是證交所不公開的 API, 透過此 URL 即可直接取得我們想要的資料了 (完全用不到 BeautifulSoup). 網址最後面的參數 _ 可有可無, 即使去掉也可以順利取得資料 (JSON) :


把此網址貼到 Chrome 就會看到傳回的 JSON 字串 :




勾選左上角的 "美化排版" 即可將 JSON 排列整齊以利閱讀 :



{
    "tables": [
        {
            "title": "113年05月13日 價格指數(臺灣證券交易所)",
            "fields": [
                "指數",
                "收盤指數",
                "漲跌(+/-)",
                "漲跌點數",
                "漲跌百分比(%)",
                "特殊處理註記"
            ],
            "data": [
                [
                    "寶島股價指數",
                    "23,695.45",
                    "<p style ='color:red'>+</p>",
                    "150.48",
                    "0.64",
                    ""
                ],
                [
                    "發行量加權股價指數",
                    "20,857.71",
                    "<p style ='color:red'>+</p>",
                    "148.87",
                    "0.72",
                    ""
                ],
                [
                    "臺灣公司治理100指數",
                    "12,043.95",
                    "<p style ='color:red'>+</p>",
                    "122.85",
                    "1.03",
                    ""
                ],
                ... (略) ...
            ]
        },
        {
            "title": "113年05月13日 每日收盤行情(全部(不含權證、牛熊證))",
            "fields": [
                "證券代號",
                "證券名稱",
                "成交股數",
                "成交筆數",
                "成交金額",
                "開盤價",
                "最高價",
                "最低價",
                "收盤價",
                "漲跌(+/-)",
                "漲跌價差",
                "最後揭示買價",
                "最後揭示買量",
                "最後揭示賣價",
                "最後揭示賣量",
                "本益比"
            ],
            "data": [
                [
                    "0050",
                    "元大台灣50",
                    "9,642,900",
                    "12,460",
                    "1,576,521,176",
                    "163.35",
                    "163.90",
                    "162.95",
                    "163.30",
                    "<p style= color:red>+</p>",
                    "1.65",
                    "163.25",
                    "58",
                    "163.30",
                    "14",
                    "0.00"
                ],
                ... (略) ...


API 網址可以用 f 字串來表示 :

url=f'https://www.twse.com.tw/rwd/zh/afterTrading/MI_INDEX?date={data}&type=ALLBUT0999&response=json'

只要傳入日期 date (格式 YYYYMMDD) 即可取得指定日期之盤後資訊. 


二. 擷取目標網頁內容 :  

找到資料來源 URL 後就可以用 requests 套件來抓資料, 先匯入要用到的模組 : 

>>> import requests   
>>> from fake_useragent import UserAgent    
>>> import csv  
>>> import json   

建立偽裝用的 User Agent 標頭字典 :

>>> ua=UserAgent()  
>>> headers={'User-Agent': ua.random}    

關於 fake_useragent 套件用法參考 :


將網址與標頭傳入 requests.get() :

>>> url='https://www.twse.com.tw/rwd/zh/afterTrading/MI_INDEX?date=20240513&type=ALLBUT0999&response=json&_=1715650102878'    
>>> res=requests.get(url, headers=headers)   

將回應內文 res.text 傳給 json.loads() 把 JSON 字串轉為字典 :

>>> data=json.loads(res.text)    
>>> type(data)   
<class 'dict'>   

呼叫 keys() 查詢字典有哪些鍵 : 

>>> data.keys()  
dict_keys(['tables', 'params', 'stat', 'date'])

對照上面瀏覽器顯示的 JSON 資料, 可知表格內容放在 tables 屬性中, 查詢共有 10 個 table :

>>> len(data['tables'])  
10

不過最後一個為空表格, 只有前 9 個有資料 : 

>>> data['tables'][0]['title']   
'113年05月13日 價格指數(臺灣證券交易所)'
>>> data['tables'][1]['title']   
'價格指數(跨市場)'
>>> data['tables'][2]['title']   
'價格指數(臺灣指數公司)'
>>> data['tables'][3]['title']       
'報酬指數(臺灣證券交易所)'
>>> data['tables'][4]['title']    
'報酬指數(跨市場)'
>>> data['tables'][6]['title']     
'113年05月13日 大盤統計資訊'
>>> data['tables'][7]['title']     
'漲跌證券數合計'
>>> data['tables'][8]['title']     
'113年05月13日 每日收盤行情(全部(不含權證、牛熊證))'
>>> data['tables'][9]     
{}

可見目標資料放在第 9 個表格 ( tables 屬性值為串列, 目標資料放在其索引 8 的一個字典的 data 屬性值裡) :

        {
            "title": "113年05月13日 每日收盤行情(全部(不含權證、牛熊證))",
            "fields": [
                "證券代號",
                "證券名稱",
                "成交股數",
                "成交筆數",
                "成交金額",
                "開盤價",
                "最高價",
                "最低價",
                "收盤價",
                "漲跌(+/-)",
                "漲跌價差",
                "最後揭示買價",
                "最後揭示買量",
                "最後揭示賣價",
                "最後揭示賣量",
                "本益比"
            ],
            "data": [
                [
                    "0050",
                    "元大台灣50",
                    "9,642,900",
                    "12,460",
                    "1,576,521,176",
                    "163.35",
                    "163.90",
                    "162.95",
                    "163.30",
                    "<p style= color:red>+</p>",
                    "1.65",
                    "163.25",
                    "58",
                    "163.30",
                    "14",
                    "0.00"
                ],
                [
                    "0051",
                    "元大中型100",


因此用 data['tables'][8]['data'] 便能取得全部上市股票盤後資訊, 先用 len() 看看有幾筆 : 

>>> len(data['tables'][8]['data'])    
1236

可見共有 1236 支股票, 檢視其中幾筆 : 

>>> data['tables'][8]['data'][0]    
['0050', '元大台灣50', '9,642,900', '12,460', '1,576,521,176', '163.35', '163.90', '162.95', '163.30', '<p style= color:red>+</p>', '1.65', '163.25', '58', '163.30', '14', '0.00']
>>> data['tables'][8]['data'][1]   
['0051', '元大中型100', '53,991', '230', '4,253,963', '79.00', '79.00', '78.55', '78.95', '<p style= color:red>+</p>', '0.20', '78.80', '1', '79.00', '6', '0.00'] 
>>> data['tables'][8]['data'][2]   
['0052', '富邦科技', '476,629', '683', '75,336,734', '158.00', '158.75', '157.50', '157.90', '<p style= color:red>+</p>', '1.20', '157.90', '17', '158.00', '5', '0.00']

前面都是 ETF 基金, 到索引 191 才是個股 : 

>>> data['tables'][8]['data'][191]    
['1101', '台泥', '30,344,896', '14,455', '1,021,662,057', '33.60', '33.85', '33.45', '33.70', '<p style= color:red>+</p>', '0.40', '33.70', '158', '33.75', '183', '32.72']
>>> data['tables'][8]['data'][200]    
['1203', '味王', '1,071', '77', '50,950', '--', '--', '--', '--', '<p> </p>', '0.00', '48.95', '1', '49.55', '1', '23.48']

可以用迴圈走訪 data['tables'][8]['data'] 串列來取得全部上市證券盤後資料並使用 csv 套件將其存入 .csv 檔或存入資料庫. 但在此之前須先進行資料清洗, 處理表格各欄位之資料型態 : 


1. 去除漲跌價差欄位 :

第 9 欄位的漲跌價差是一個 HTML 字串, 此欄位可透過前後日收盤價計算而得故可移除 :

>>> data['tables'][8]['data'][191][9]    
'<p style= color:red>+</p>'

這可以透過串列切片來處理.


2. 添加日期欄位 :

如果要將每日擷取到的盤後資料存入資料庫, 則必須添加一個日期欄位來區別, 我們可以從 data['title'] 屬性值中取得日期 :

"title": "113年05月13日 每日收盤行情(全部(不含權證、牛熊證))"

注意, 日期資訊是民國年月日, 與後面的 "每日收盤行情... " 之間有個空格, 先用 split() 將日期資訊萃取出來 : 

>>> date_str=data['tables'][8]['title'].split(' ')[0]      
>>> date_str     
'113年05月13日'

在利用正規表示法把年月日都替換成 '-' :

>>> import re   
>>> date_str=re.sub(r"\D", "-", date_str)     
>>> date_str      
'113-05-13-'   

用字串切片去除結尾的 '-' 字元 :

>>> date_str=date_str[:-1]   
>>> date_str    
'113-05-13'  

接下來將日期字串用 split() 拆開後把民國年加上 1911 再組回去即可 :

>>> y, m, d=date_str.split('-')   
>>> date_str=str(int(y) + 1911) + '-' + m + '-' + d   
>>> date_str    
'2024-05-13'


3. 將數字字串轉成數值型態 (int/float) :

由上可知, data['tables'][8]['data'] 串列中各欄位之值都是字串, 為了爾後進行計算, 除了第二欄位 "證券名稱" 外都要轉為數值, 浮點數可傳給 float() 轉換, 整數可傳給 int() 轉換. 

例如大立光的價格資訊中有小數部分, 所以需要用 float() 轉成浮點數, 但整數部分有逗點必須先去除 : 

>>> data['tables'][8]['data'][725]    
['3008', '大立光', '426,653', '1,576', '951,013,700', '2,235.00', '2,250.00', '2,215.00', '2,240.00', '<p style= color:red>+</p>', '10.00', '2,235.00', '1', '2,240.00', '6', '14.43']   

不過在進行數字字串轉數值時會遇到一個問題, 若股票當日成交股數 (第三欄位) 為 0, 則欄位 6, 7, 8, 9 的開高低收欄位會是 '--' 而非數值字串 :  

>>> data['tables'][8]['data'][165]  
['01002T', '土銀國泰R1', '0', '0', '0', '--', '--', '--', '--', '<p> </p>', '0.00', '17.15', '1', '17.19', '1', '0.00']   

因此對於成交股數為 0 的股票就跳過去不予處理

這樣就可以走訪 data['tables'][8]['data'] 串列之元素來進行轉換了, 以下為了縮短指涉長度, 用一個新變數 stocks 來指向二維串列 data['tables'][8]['data'] :

>>> stocks=data['tables'][8]['data']     
>>> stocks[0]    
['0050', '元大台灣50', '9,642,900', '12,460', '1,576,521,176', '163.35', '163.90', '162.95', '163.30', '<p style= color:red>+</p>', '1.65', '163.25', '58', '163.30', '14', '0.00']

原始欄位共 16 個, 如果刪除第 9 欄位 (索引 8) "漲跌(+/-)", 然後在最前面插入日期欄位, 則一刪一增後仍然是 16 欄, 結果會變成如下 : 

>>> del stocks[0][9]  
>>> stocks[0].insert(0, date_str)   
>>> stocks[0]    
['2024-05-13', '0050', '元大台灣50', '9,642,900', '12,460', '1,576,521,176', '163.35', '163.90', '162.95', '163.30', '1.65', '163.25', '58', '163.30', '14', '0.00']

為了能在做欄位型態轉換時能知道此欄應該轉成甚麼型態, 定義一個變數 col_type 來做對映一刪一增後的欄位型態分布 : 

>>> col_types=['date', 'text', 'text', 'int', 'int', 'int', 'float', 'float', 'float', 'float', 'float', 'float', 'int', 'float', 'int', 'float']  
>>> col_types   
['date', 'text', 'text', 'int', 'int', 'int', 'float', 'float', 'float', 'float', 'float', 'float', 'int', 'float', 'int', 'float']

然後定義一個函式來處理欄位型態轉換 : 
 
>>> def col_trans(col, col_type):     
    if col_type in ['int', 'float']:         # 欄位型態是 int/float 才轉換
        col=col.strip().replace(',', '')    # 先將左右空格與千位逗號去除
        try :                                         # int() 與 float() 轉型失敗會丟出例外
            if col_type == 'int':             # 整數用 int() 轉換
                return int(col)
            else:
                return float(col)              # 浮點數用 float() 轉換
        except:
            return -1                              # 例外傳回 -1
    else:
        return col                                 # 非 int, float 不轉換傳回本身

接下來以 stocks[0] 台灣 50 為例測試此函式是否能正確進行資料清洗, 只要以串列生成式用一個指令就能完成 : 

[col_trans(col, col_type[idx]) for idx, col in enumerate(stocks[0])]  

此處我們利用 enumerate() 函式來取得 stocks[0] 串列的元素 col 與其索引 idx, 因為要從 col_types 串列中取得各欄位所對映之型態必須用到索引 : 

>>> stocks[0]   
['2024-05-13', '0051', '元大中型100', '53,991', '230', '4,253,963', '79.00', '79.00', '78.55', '78.95', '0.20', '78.80', '1', '79.00', '6', '0.00']
>>> [col_trans(col, col_type[idx]) for idx, col in enumerate(stocks[0])]   
['2024-05-13', '0051', '元大中型100', 53991, 230, 4253963, 79.0, 79.0, 78.55, 78.95, 0.2, 78.8, 1, 79.0, 6, 0.0]

結果顯示所有 int/float 型態的欄位都被正確地轉換了. 

我們可以將每支股票清洗後的串列依序存入一個空串列中, 結果就是一個二維串列, 然後用 csv 套件將此二維串列寫入 .csv 檔案, 完整程式碼如下 : 

import requests   
from fake_useragent import UserAgent    
import csv  
import json
import re

def col_trans(col, col_type):   # 資料清洗與轉換
    if col_type in ['int', 'float']:  # 只轉換 int/float 類型資料
        col=col.replace(',', '')       # 去除整數部分的千位逗號
        try :
            if col_type == 'int':
                return int(col)      # 轉成 int
            else:
                return float(col)   # 轉成 float
        except:
            return -1
    else:
        return col    # 非 int/float 類型不處理直接傳回

ua=UserAgent()  
headers={'User-Agent': ua.random}    
url='https://www.twse.com.tw/rwd/zh/afterTrading/MI_INDEX?date=20240513&type=ALLBUT0999&response=json&_=1715650102878'    
res=requests.get(url, headers=headers)
data=json.loads(res.text)
if 'tables' in data:  # 非交易日 data 字典中無 tables 屬性
    date_str=data['tables'][8]['title'].split(' ')[0]
    date_str=re.sub(r"\D", "-", date_str)
    date_str=date_str[:-1]
    y, m, d=date_str.split('-')
    date_str=str(int(y) + 1911) + '-' + m + '-' + d    # 民國轉西元
    stocks=data['tables'][8]['data']    # 盤後資訊在第 9 張表
    col_types=['date', 'text', 'text', 'int', 'int', 'int',
               'float', 'float', 'float', 'float', 'float', 'float', 
               'int', 'float', 'int', 'float']    # 欄位型態對映
    results=[]   # 儲存個股清洗後的結果
    for stock in stocks:   # 走訪每支股票
        if stock[2] != '0':
            del stock[9]      # 刪除第 10 欄 '漲跌+-'
            stock.insert(0, date_str)    # 交易日期插在最前面 (第 1 欄)
            results.append([col_trans(col, col_types[idx])
             for idx, col in enumerate(stock)])   # 資料清洗與轉換
    with open('twse_after_trading.csv', 'w', newline='') as f:    # 結果寫入 csv 檔
        writer=csv.writer(f)  
        writer.writerows(results)
        print('寫入檔案完成')
else:
    print('非交易日無盤後資料')
      
注意, 由於非交易日無盤後交易資料, 證交所網頁會傳回 JSON 字串 {'stat': '很抱歉,沒有符合條件的資料!'}, 故 data 字典中會找不到 tables 屬性, 故程式中使用 if 'tables' in data 來檢查是否維交易日, 是的話才進行截取與清理作業. 最後呼叫 csv 寫入物件 Writer 的 writerows() 方法將二維串列寫入檔案, 參考 :


結果如下 : 

2024-05-13,0050,元大台灣50,9642900,12460,1576521176,163.35,163.9,162.95,163.3,1.65,163.25,58,163.3,14,0.0
2024-05-13,0051,元大中型100,53991,230,4253963,79.0,79.0,78.55,78.95,0.2,78.8,1,79.0,6,0.0
2024-05-13,0052,富邦科技,476629,683,75336734,158.0,158.75,157.5,157.9,1.2,157.9,17,158.0,5,0.0
2024-05-13,0053,元大電子,18050,104,1586656,87.85,88.2,87.75,88.0,1.2,87.9,1,88.15,14,0.0
2024-05-13,0055,元大MSCI金融,512466,448,13619756,26.65,26.68,26.5,26.53,0.09,26.52,11,26.53,18,0.0
2024-05-13,0056,元大高股息,16994138,12450,663441474,39.07,39.15,38.93,39.04,0.0,39.03,8,39.04,105,0.0
2024-05-13,0057,富邦摩台,12177,14,1472245,120.9,121.0,120.75,120.75,1.65,120.85,25,121.15,15,0.0
... (略) ...
2024-05-13,9940,信義,321271,748,10599015,33.15,33.15,32.85,32.95,0.2,32.95,1,33.0,17,13.79
2024-05-13,9941,裕融,904683,1580,130613753,144.5,145.5,143.0,145.5,0.5,145.0,1,145.5,59,16.29
2024-05-13,9941A,裕融甲特,18867,23,961069,50.9,51.0,50.9,51.0,0.1,50.9,3,51.1,4,0.0
2024-05-13,9942,茂順,81343,171,10499679,130.0,130.0,128.5,129.5,0.5,129.0,4,129.5,2,10.99
2024-05-13,9943,好樂迪,488748,487,44056473,91.0,91.0,89.0,90.7,0.3,90.6,16,90.7,9,15.37
2024-05-13,9944,新麗,1017075,538,21658756,21.15,22.0,20.85,21.55,0.4,21.5,8,21.55,5,215.5
2024-05-13,9945,潤泰新,45390604,23639,1940083275,40.95,43.95,40.9,43.9,3.4,43.9,53,43.95,357,16.08
2024-05-13,9946,三發地產,2558212,1500,95295621,36.7,38.2,35.95,38.0,1.1,37.9,1,38.0,131,100.0
2024-05-13,9955,佳龍,1380053,1160,41882995,31.5,31.65,29.75,29.75,0.65,29.75,28,29.8,14,0.0
2024-05-13,9958,世紀鋼,12639860,9709,2989728306,230.0,241.0,229.5,236.0,11.5,235.5,61,236.0,15,53.15




可見已正確擷取盤後行情資訊.  


三. 寫成爬蟲函式 :

上面的程式中目標網頁 URL 是固定的, 要擷取特定日期時必須去修改 URL 字串, 我們可以將其改寫為函式, 只要傳入日期字串 (格式為 'YYmmdd') 就傳回一個二為串列, 所以需要匯入 datetime 套件來製作日期字串, 呼叫 datetime.datetime.today() 或 datetime.datetime.now() 函式都會傳回一個 datetime.datetime 物件, 然後呼叫其 strftime() 方法並傳入 '%Y%m%d' 格式字串即可 : 

>>> from datetime import datetime 
>>> datetime.today().strftime('%Y%m%d')    
'20240517'
>>> datetime.now().strftime('%Y%m%d')    
'20240517'

完整的程式碼如下 : 

# craw_twse_after_trading.py
import requests   
from fake_useragent import UserAgent    
import csv  
import json
import re
from datetime import datetime

def col_trans(col, col_type):
    if col_type in ['int', 'float']:
        col=col.replace(',', '')
        try :
            if col_type == 'int':
                return int(col)
            else:
                return float(col)
        except:
            return -1
    else:
        return col

def get_twse_after_trade(the_date):
    ua=UserAgent()  
    headers={'User-Agent': ua.random}    
    url='https://www.twse.com.tw/rwd/zh/afterTrading/MI_INDEX?date=' +\
        the_date + '&type=ALLBUT0999&response=json&_=1715650102878'    
    res=requests.get(url, headers=headers)
    data=json.loads(res.text)
    if 'tables' in data:
        date_str=data['tables'][8]['title'].split(' ')[0]
        date_str=re.sub(r"\D", "-", date_str)
        date_str=date_str[:-1]
        y, m, d=date_str.split('-')
        date_str=str(int(y) + 1911) + '-' + m + '-' + d
        stocks=data['tables'][8]['data']
        col_types=['date', 'text', 'text', 'int', 'int', 'int',
                   'float', 'float', 'float', 'float', 'float', 'float', 
                   'int', 'float', 'int', 'float']
        results=[]
        for stock in stocks:
            if stock[2] != '0':
                del stock[9]
                stock.insert(0, date_str)
                results.append([col_trans(col, col_types[idx])
                 for idx, col in enumerate(stock)])            
        return results
    else:
        return None

if __name__ == '__main__':
    the_date=datetime.now().strftime('%Y%m%d')
    results=get_twse_after_trade(the_date)
    with open('twse_after_trading.csv', 'w', newline='') as f:   
        writer=csv.writer(f)  
        writer.writerows(results)
        
只要更改 the_date 字串就可以擷取指定日期之盤後資料了. 


四. 將結果存入 SQLite 資料庫 :

在上面的測試中我們將爬蟲的擷取結果存入 csv 檔, 如果存入資料庫會讓存取更方便, 例如用 Flask/Django 建構一個網站來瀏覽或查詢盤後資料時. 此處使用小而美的單檔資料庫 SQLite, 關於 SQLite 用法參考 :


首先用 SQL Manager for SQLite 軟體建立一個資料庫 tw_stocks, 然後建立一個包含下列 16 個欄位的資料表 twse_after_trade : 
  1. date : 交易日, DATETEXT(10)
  2. stock_id : 證券代號, TEXT(10)
  3. stock_name : 證券名稱, TEXT(20)
  4. volumn : 成交股數 (成交量), INTEGER
  5. transactions : 成交筆數, INTEGER
  6. turnover : 成交金額, INTEGER
  7. open : 開盤價, REAL
  8. high : 最高價, REAL
  9. low : 最低價, REAL
  10. close : 收盤價, REAL
  11. spread : 漲跌價差, REAL
  12. last_best_bid_price : 最後揭示買價, REAL
  13. last_best_bid_volumn : 最後揭示買量, INTEGER
  14. last_best_ask_price : 最後揭示賣價, REAL
  15. last_best_ask_volumn : 最後揭示賣量, INTEGER
  16. PER : 本益比, REAL



關於 SQL Manager for SQLite 軟體用法參考 :


也可以先用 SQL Manager for SQLite 建立空白資料庫 tw_stocks 後, 用 SQL 指令建立 twse_after_trade 資料表 :

CREATE TABLE [twse_after_trade](
  [date] DATETEXT(10) NOT NULL, 
  [stock_id] TEXT(10) NOT NULL, 
  [stock_name] TEXT(20) NOT NULL, 
  [volumn] INTEGER, 
  [transactions] INTEGER, 
  [turnover] INTEGER, 
  [open] REAL, 
  [high] REAL, 
  [low] REAL, 
  [close] REAL, 
  [spread] REAL, 
  [last_best_bid_price] REAL, 
  [last_best_bid_volumn] INTEGER, 
  [last_best_ask_price] REAL, 
  [last_best_ask_volumn] INTEGER, 
  [PER] REAL)

建立 twse_after_trade 資料表後先用第一筆資料來測試看看資料表能否正常寫入, 先匯入 sqlite3 套件並建立資料庫連線 : 

>>> import sqlite3   
>>> conn=sqlite3.connect("tw_stocks.sqlite")   

製作寫入資料表的 SQL 指令後呼叫 execute() 執行, 接著呼叫 commit() 回存資料庫 :

>>> SQL="INSERT INTO twse_after_trade(date, stock_id, stock_name, " +\
    "volumn, transactions, turnover, open, high, low, " +\
    "close, spread, last_best_bid_price, last_best_bid_volumn, " +\
    "last_best_ask_price, last_best_ask_volumn, PER) " +\
    "VALUES('2024-05-17', '0050', '元大台灣50', 4652000, 2229, " +\ 
    "777580350, 167.55, 167.7, 166.7, 167.25, 0.2, 167.2, -1, " +\
    "167.25, -1, 0.0)" 
>>> conn.execute(SQL)   
<sqlite3.Cursor object at 0x0000016933A33B40>
>>> conn.commit()  

這時將 SQL Manager for SQLite 切到第二個頁籤 Browse Data 就可看到寫入之資料 :




用 SELECT 指令確認可將資料讀出來 : 

>>> SQL="SELECT * FROM twse_after_trade"   
>>> cursor=conn.execute(SQL)   
>>> print(cursor.fetchone())   
('2024-05-17', '0050', '元大台灣50', 4652000, 2229, 777580350, 167.55, 167.7, 166.7, 167.25, 0.2, 167.2, -1, 167.25, -1, 0.0)  

這樣就可以將上面的程式修改為儲存到 SQLite 版了, 完整程式碼如下 :

# craw_twse_after_trading_sqlite.py
import requests   
from fake_useragent import UserAgent    
import csv  
import json
import re
from datetime import datetime
import sqlite3

def col_trans(col, col_type):
    if col_type in ['int', 'float']:
        col=col.replace(',', '')
        try :
            if col_type == 'int':
                return int(col)
            else:
                return float(col)
        except:
            return -1
    else:
        return col

def get_twse_after_trade(the_date):
    ua=UserAgent()  
    headers={'User-Agent': ua.random}    
    url='https://www.twse.com.tw/rwd/zh/afterTrading/MI_INDEX?date=' +\
        the_date + '&type=ALLBUT0999&response=json&_=1715650102878'    
    res=requests.get(url, headers=headers)
    data=json.loads(res.text)
    if 'tables' in data:
        date_str=data['tables'][8]['title'].split(' ')[0]
        date_str=re.sub(r"\D", "-", date_str)
        date_str=date_str[:-1]
        y, m, d=date_str.split('-')
        date_str=str(int(y) + 1911) + '-' + m + '-' + d
        stocks=data['tables'][8]['data']
        col_types=['date', 'text', 'text', 'int', 'int', 'int',
                   'float', 'float', 'float', 'float', 'float', 'float', 
                   'int', 'float', 'int', 'float']
        results=[]
        for stock in stocks:
            if stock[2] != '0':
                del stock[9]
                stock.insert(0, date_str)
                results.append([col_trans(col, col_types[idx])
                 for idx, col in enumerate(stock)])            
        return results
    else:
        return None

if __name__ == '__main__':
    the_date=datetime.now().strftime('%Y%m%d')
    stocks=get_twse_after_trade(the_date)
    if stocks:
        conn=sqlite3.connect("tw_stocks.sqlite")
        for stock in stocks:
            stock=[str(i) for i in stock]
            values="VALUES('" + "','".join(stock) + "')"
            SQL="INSERT INTO twse_after_trade(date, stock_id, " +\
                "stock_name, volumn, transactions, turnover, " +\
                "open, high, low, close, spread, " +\
                "last_best_bid_price, last_best_bid_volumn, " +\
                "last_best_ask_price, last_best_ask_volumn, " +\
                "PER) " + values
            conn.execute(SQL)
        conn.commit()
        conn.close()
    else:
        print('非交易日')

由於在前面清理資料時將數值字串資料轉換成 int 與 float, 一般 SQL 指令中, 數值資料不加括號, 字串資料要加括號, 而 SQLite 的 SQL 指令不管數值或字串一律加括號也可以 (它應該是照欄位類型自動轉換, 這樣好處理), 所以此處用串列生成式先將 stock 的元素都轉成字串, 然後串成 values 字串以組成 SQL 的 INSERT 指令, 結果如下 :




不過我以前用 PHP 寫的系統是各股有自己的資料表, 擷取到資料後是分別寫入各股的資料表, 而不是像這樣全部存在一個資料表. 

也可以把資料庫檔上傳到線上 SQLite 網站 SQLite Viewer 進行瀏覽與操作 :




關於 SQLite Viewer 用法參考 :


感覺 SQLite Viewer 字大介面簡潔又可輸出 CSV 檔, 就是機敏資料不好上傳.

沒有留言:

張貼留言