本篇重點

• 建立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」

名稱請自行定義，而資料表則選擇你是要替哪個資料表建立索引，這裡我們選擇之前建立的1分K「stocks_1min_kbars」

接著，選擇所要建立索引的欄位，選擇後請點「▶」按鈕將該欄位新增至右側的Index Column

這裡我是選擇建立code及ts這兩個欄位的索引，確認後按下「OK」後，DB Browser for SQLite就會開始建立索引內容，此過程可能會因為資料量大而花上一點時間，請耐心等侯

Index建立完成後，請按「Write Changes」，將剛才做的修改寫入資料庫中。

建立Index後shioaji.db檔案大小由7.35GB變為10.8GB，我跑了下列三檔股票的1分K轉5分K，並比較建立Index前後，執行pd.read_sql所需的時間(單位：秒)。可以看到讀取時間有大幅的減少。

產生日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。

產生週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() #關閉資料庫連線