前言

昨日稍微介紹了為什麼要取得這些資料，今天來實作取得資料，並且寫入Table LegalDailyFutureOption！
今天會從期交所取得：三大法人-區分期貨與選擇權二類-依日期的資料。

本日程式碼使用：d6_3LegalPersonTrade.py

建立資料表

我們可以下載csv檔：

https://www.taifex.com.tw/data_gov/taifex_open_data.asp?data_name=MarketDataOfMajorInstitutionalTradersDividedByFuturesAndOptionsBytheDate

觀察一下我們的內容，我們會需要用到全部的資料，因此把這些資料全部列到資料庫中。這些資料分別為：

• 日期
• 身份別
• 期貨多方交易口數
• 選擇權多方交易口數
• 期貨多方交易契約金額(千元)
• 選擇權多方交易契約金額(千元)
• 期貨空方交易口數
• 選擇權空方交易口數
• 期貨空方交易契約金額(千元)
• 選擇權空方交易契約金額(千元)
• 期貨多空交易口數淨額
• 選擇權多空交易口數淨額
• 期貨多空交易契約金額淨額(千元)
• 選擇權多空交易契約金額淨額(千元)
• 期貨多方未平倉口數
• 選擇權多方未平倉口數
• 期貨多方未平倉契約金額(千元)
• 選擇權多方未平倉契約金額(千元)
• 期貨空方未平倉口數
• 選擇權空方未平倉口數
• 期貨空方未平倉契約金額(千元)
• 選擇權空方未平倉契約金額(千元)
• 期貨多空未平倉口數淨額
• 選擇權多空未平倉口數淨額
• 期貨多空未平倉契約金額淨額(千元)
• 選擇權多空未平倉契約金額淨額(千元)

因此資料庫的SQL碼會是：

CREATE TABLE `finance`.`LegalDailyFutureOption` (
  `ID` INT NOT NULL AUTO_INCREMENT,
  `TradeDate` DATE NOT NULL,
  `TradeGroup` VARCHAR(10) NOT NULL,
  `FutureLongQty` INT NOT NULL DEFAULT 0,
  `OptionLongQty` INT NOT NULL DEFAULT 0,
  `FutureLongAmount` INT NOT NULL DEFAULT 0,
  `OptionLongAmount` INT NOT NULL DEFAULT 0,
  `FutureShortQty` INT NOT NULL DEFAULT 0,
  `OptionShortQty` INT NOT NULL DEFAULT 0,
  `FutureShortAmount` INT NOT NULL DEFAULT 0,
  `OptionShortAmount` INT NOT NULL DEFAULT 0,
  `FutureNetQty` INT NOT NULL DEFAULT 0,
  `OptionNetQty` INT NOT NULL DEFAULT 0,
  `FutureNetAmount` INT NOT NULL DEFAULT 0,
  `OptionNetAmount` INT NOT NULL DEFAULT 0,
  `FutureLongOIQty` INT NOT NULL DEFAULT 0,
  `OptionLongOIQty` INT NOT NULL DEFAULT 0,
  `FutureLongOIAmount` INT NOT NULL DEFAULT 0,
  `OptionLongOIAmount` INT NOT NULL DEFAULT 0,
  `FutureShortOIQty` INT NOT NULL DEFAULT 0,
  `OptionShortOIQty` INT NOT NULL DEFAULT 0,
  `FutureShortOIAmount` INT NOT NULL DEFAULT 0,
  `OptionShortOIAmount` INT NOT NULL DEFAULT 0,
  `FutureOINetQty` INT NOT NULL DEFAULT 0,
  `OptionOINetQty` INT NOT NULL DEFAULT 0,
  `FutureOINetAmount` INT NOT NULL DEFAULT 0,
  `OptionOINetAmount` INT NOT NULL DEFAULT 0,
  `CreateTime` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`));

其中把數量、金額全部預設為0，並且不提供空值，這樣資料比較乾淨，不必額外處理異常數值狀況，直接排除0就可以。

資料寫進資料庫

資料表準備好，接著就是寫入資料。

建立匯入物件

本次作法是把這個功能變成一個單獨的物件，因此建立一個名為legal_daily_future_option 的class。並且提供屬性，讓後面比較方便取得資料：

self.title = "三大法人-區分期貨與選擇權二類-依日期"
self.url = "https://www.taifex.com.tw/data_gov/taifex_open_data.asp?data_name=MarketDataOfMajorInstitutionalTradersDividedByFuturesAndOptionsBytheDate"
self.df = None  # 把資料從csv轉乘datframe
self.csv = self.get_csv_data()  # 取得網路上的資料，格式為csv

其中url保留彈性，萬一網址修改了還可以使用參數法去改。

取得伺服器資料

在政府提供的資料格式為：csv，因此建立一個get_csv_data的功能來取得伺服器中的資料，把它存成csv和dataframe。雖然csv這次沒有用到，但這可以抓到資料後直接存成一個csv檔，當做資料備源，例如當格式改掉後，還有個參考依據，並補匯入資料。

因此主要的功能會在這邊：

try:
    csv = requests.get(self.url)
    df = pandas.read_csv(StringIO(csv.text))  # 有header
    print(df)  # debug
    self.df = df
except Exception as exc:
    print(exc)
    return False

這部份跟之前類似，就不多說明。

存入資料庫

這次把連結單獨拉出來，成為一個模組。這樣不用每次與資料庫連結時，都寫一樣的參數和方法，可直接呼叫使用，或是修改資料庫資訊，以後如果要使用Azure或是搬到其他地方，也可以直接修改這個模組，而不用動到其他程式。（我這就是滾動式進步XD）

因此建立一個叫做db_connect.py的檔案，裡面建立之前的MySQL連線：

import pymysql


class mysql_connect:
    def __init__(self) -> None:
        self.db_settings = {
            "host": "127.0.0.1",
            "port": 3306,
            "user": "root",
            "password": "pwd",
            "db": "finance",
        }

    def connect(self):
        conn = pymysql.connect(**self.db_settings)

        return conn

就這樣簡簡單單的完成。記住"password": "pwd"這邊要輸入自己的密碼，其他資料也是輸入自己的連線資訊喔！

接著就是在原本的模組中，建立一個功能，用來把資料匯入MySQL中：

def insert_mysql(self) -> bool:
    # 計數器：計算新增了幾筆
    counter = 0

    try:
        # 建立connection物件
        my_connt_obj = db_connect.mysql_connect()
        conn = my_connt_obj.connect()
        with conn.cursor() as cursor:
            # 新增SQL語法
            for _, row in self.df.iterrows():
                trade_date = str(row[0])
                cmd = f"""INSERT INTO LegalDailyFutureOption 
                (TradeDate, TradeGroup,
                FutureLongQty, OptionLongQty,
                FutureLongAmount, OptionLongAmount,
                FutureShortQty, OptionShortQty,
                FutureShortAmount, OptionShortAmount,
                FutureNetQty, OptionNetQty,
                FutureNetAmount, OptionNetAmount,
                FutureLongOIQty, OptionLongOIQty,
                FutureLongOIAmount, OptionLongOIAmount,
                FutureShortOIQty, OptionShortOIQty,
                FutureShortOIAmount, OptionShortOIAmount,
                FutureOINetQty, OptionOINetQty,
                FutureOINetAmount, OptionOINetAmount)
                values('{"{}-{}-{}".format(trade_date[:4],trade_date[4:6],trade_date[6:])}', 
                '{row[1]}',
                {row[2]}, {row[3]},
                {row[4]}, {row[5]},
                {row[6]}, {row[7]},
                {row[8]}, {row[9]},
                {row[10]}, {row[11]},
                {row[12]}, {row[13]},
                {row[14]}, {row[15]},
                {row[16]}, {row[17]},
                {row[18]}, {row[19]},
                {row[20]}, {row[21]},
                {row[22]}, {row[23]},
                {row[24]}, {row[25]});"""
                cursor.execute(cmd)

                counter += 1
            conn.commit()
    except Exception as exc:
        print(exc)
        return False

    print(f"===Finish: {counter}==")
    return True

最後執行他，就大功告成囉～
會顯示的結果如下：

日期    身份別  期貨多方交易口數  選擇權多方交易口數  期貨多方交易契約金額(千元)  ...  選擇權空方未平倉契約金額(千元)  期貨多空未平倉口數淨額  選擇權多空未平倉口數淨額  期貨多空未平倉契約金額淨額(千元)  選擇權多空未平倉契約金額淨額(千元)
0  20210906    自營商     41537     247258        46023473  ...            824397         6922         24371           31180664              343273
1  20210906     投信        27          2           76092  ...                 0         2417            62            3763399                  61
2  20210906  外資及陸資    380498     140362       372794596  ...            986546      -230431        -38464         -111817854             -499753

[3 rows x 26 columns]
===Finish: 3==
True

後記：

這篇以為一下子就可以完成，殊不知寫了很多。不過，還是無法使用網友提供建議的pandas的to_sql功能，等之後學會再導入吧！