iT邦幫忙

2021 iThome 鐵人賽

DAY 28
0
永豐金融APIs

深入解析 Shioaji API系列 第 28

Day 28 - 建立自己的K線資料庫 (下)

本篇重點

  • 建立Index,加快SQLite存取速率
  • 產生日K線資料
  • 產生週K線資料
  • 產生月K線資料

建立Index,加快SQLite存取速率

在產生日K、週K和月K資料之前,我們先將原本的SQLite資料庫,加上Index索引,以加快SQLite的存取速度。雖然建立Index會讓DB檔案大小變大,但卻可以讓資料庫的存取效率大大提升。
若要在SQLite中,建立Index可以透過兩種方式,第一個方式是用之前介紹的DB Browser for SQLite來建立Index
首先,執行DB Browser for SQLite並打開資料庫後,按「Create Index」
https://ithelp.ithome.com.tw/upload/images/20211012/201408278a1V2dlE5t.png
名稱請自行定義,而資料表則選擇你是要替哪個資料表建立索引,這裡我們選擇之前建立的1分K「stocks_1min_kbars」
https://ithelp.ithome.com.tw/upload/images/20211012/20140827VVgEfDIeM1.png
接著,選擇所要建立索引的欄位,選擇後請點「▶」按鈕將該欄位新增至右側的Index Column
https://ithelp.ithome.com.tw/upload/images/20211012/20140827H7X5t3zDqx.png
這裡我是選擇建立code及ts這兩個欄位的索引,確認後按下「OK」後,DB Browser for SQLite就會開始建立索引內容,此過程可能會因為資料量大而花上一點時間,請耐心等侯
https://ithelp.ithome.com.tw/upload/images/20211012/20140827M9thLsTsa1.png
Index建立完成後,請按「Write Changes」,將剛才做的修改寫入資料庫中。
https://ithelp.ithome.com.tw/upload/images/20211013/20140827Q6bPhZ4qAH.png
建立Index後shioaji.db檔案大小由7.35GB變為10.8GB,我跑了下列三檔股票的1分K轉5分K,並比較建立Index前後,執行pd.read_sql所需的時間(單位:秒)。可以看到讀取時間有大幅的減少。

股票 未建立Index,read_sql執行時間 建立Index後,read_sql執行時間
2330 94.51796960830688 0.5804615020751953
2890 15.362500667572021 0.5675475597381592
2890 15.250365018844604 0.5844612121582031

產生日K線資料

上面有提到,在SQLite可以手動建立Index來增加存取效率,除了上述的手動建立外,也可以透過pandas寫入資料時一併建立。我們以產生日K線資料為例,程式內容如下

import pandas as pd
import sqlite3
import time

conn = sqlite3.connect('D:/shioaji.db') #建立資料庫連線

# 傳入股票代碼,產生日K資料並存至資料庫
def generate_1day_kbar(stock_code):
    print(f'generate 1dayK for stock:{stock_code}')
    # index_col,產生DataFrame後將index設定為ts欄位
    df = pd.read_sql(f'SELECT code, Open, High, Low, Close, Volume, ts FROM stocks_1min_kbars WHERE code = {stock_code}', conn, parse_dates=['ts'], index_col=['ts'])
    
    # rule設為1D,表示resample為一天的資料
    df_1day_kbar = df.resample(rule='1D').agg({
        'code': 'first',
        'Open': 'first', 
        'High': 'max', 
        'Low': 'min', 
        'Close': 'last',
        'Volume': 'sum'})
    df_1day_kbar.dropna(axis=0, inplace=True)
    df_1day_kbar.reset_index(inplace=True) #重設index
    df_1day_kbar.set_index(['code', 'ts'], inplace=True) #重新將Index指定為code、ts
    df_1day_kbar.to_sql('stocks_1day_kbars', conn, if_exists='append')
    print(f'stock:{stock_code}, 1dayK is added to DB...')

cursor = conn.cursor()
for code in cursor.execute('SELECT DISTINCT code FROM stocks_1min_kbars'):
    generate_1day_kbar(code[0])

conn.close() #關閉資料庫連線

上面的程式中,我們在resample後先執行reset_index(),然後再次執行set_index,並將code及ts欄位傳入,這樣在執行to_sql時,pandas在建立資料庫table時,就會一併建立索引Index,執行DB Browser for SQLite並打開資料庫,就可以看到SQLite中已建立對應的Index。
https://ithelp.ithome.com.tw/upload/images/20211013/20140827pL1qr643f7.png

產生週K線資料

若產生日K線資料,接著產生週K時,就可以直接抓stocks_1day_kbars中的資料去做處理。程式範例如下:

import pandas as pd
import sqlite3

conn = sqlite3.connect('D:/shioaji.db') #建立資料庫連線

# 傳入股票代碼,產生週K資料並存至資料庫
def generate_week_kbar(stock_code):
    print(f'generate 1dayK for stock:{stock_code}')
    df = pd.read_sql(f'SELECT code, Open, High, Low, Close, Volume, ts FROM stocks_1day_kbars WHERE code = {stock_code}', conn, parse_dates=['ts'], index_col='ts')
    # resample並指定每週的最後一天為週五Friday
    df_week_kbar = df.resample(rule='W-FRI').agg({
        'code': 'first',
        'Open': 'first', 
        'High': 'max', 
        'Low': 'min', 
        'Close': 'last',
        'Volume': 'sum'})
    df_week_kbar.dropna(axis=0, inplace=True)
    df_week_kbar.reset_index(inplace=True)
    df_week_kbar.set_index(['code', 'ts'], inplace=True)
    df_week_kbar.to_sql('stocks_week_kbars', conn, if_exists='append')
    print(f'stock:{stock_code}, weeK is added to DB...')

cursor = conn.cursor()
for code in cursor.execute('SELECT DISTINCT code FROM stocks_1min_kbars'):
    generate_week_kbar(code[0])

conn.close() #關閉資料庫連線

產生月K線資料

產生月K線的資料,程式範例如下:

import pandas as pd
import sqlite3

conn = sqlite3.connect('D:/shioaji.db') #建立資料庫連線

# 傳入股票代碼,產生5分K資料並存至資料庫
def generate_month_kbar(stock_code):
    print(f'generate month kbar for stock:{stock_code}')
    df = pd.read_sql(f'SELECT code, Open, High, Low, Close, Volume, ts FROM stocks_1day_kbars WHERE code = {stock_code}', conn, parse_dates=['ts'], index_col='ts')
    # rule指定為月
    df_month_kbar = df.resample(rule='M').agg({
        'code': 'first',
        'Open': 'first', 
        'High': 'max', 
        'Low': 'min', 
        'Close': 'last',
        'Volume': 'sum'})
    df_month_kbar.reset_index(inplace=True)
    df_month_kbar.set_index(['code', 'ts'], inplace=True)
    df_month_kbar.to_sql('stocks_month_kbars', conn, if_exists='append')
    print(f'stock:{stock_code}, month kbar is added to DB...')

cursor = conn.cursor()
for code in cursor.execute('SELECT DISTINCT code FROM stocks_1min_kbars'):
    generate_month_kbar(code[0])

conn.close() #關閉資料庫連線

上一篇
Day 27 - 建立自己的K線資料庫 (中)
下一篇
Day 29 - 計算均線資料
系列文
深入解析 Shioaji API30

尚未有邦友留言

立即登入留言