### Python量化資料倉儲搭建3：資料落庫程式碼封裝

```
本系列教程為量化開發者，提供本地量化金融資料倉儲的搭建教程與全套原始碼。我們以恆有數(UDATA)金融資料社群為資料來源，將金融基礎資料落到本地資料庫。教程提供全套原始碼，包括歷史資料下載與增量資料更新，資料更新任務部署與日常監控等操作。
```

在上一節講述中，我們封裝了Python操作MySQL資料庫的自定義類，存為MySQLOperation.py檔案；本節內容運算元據庫部分，將會呼叫MySQLOperation中的方法，以及pandas.to_sql和pandas.read_sql的操作。

### 一、恆有數(UDATA)操作簡介

#### 1、獲取Token

A、在恆有數官網（https://udata.hs.net）註冊並登入，在訂閱頁面，下單免費的體驗套餐；

B、在右上角，頭像下拉選單中，進入總覽頁面，複製Token；

C、在資料頁面，檢視資料介面文件，獲取介面名稱、請求引數、返回引數和Python程式碼示例；

#### 2、安裝hs_udata

```
pip install hs_udata
```

使用示例如下：

```python
import hs_udata as hs
# 設定Token
hs.set_token(token = 'xxxxxxxxxxxxxxx')  # 總覽頁面獲取個人Token
# 以分鐘線行情為例，獲取000001.SZ在2021-05-01至2021-06-01期間的分鐘線資料
# 介面文件見：https://udata.hs.net/datas/342/
df = hs.stock_quote_minutes(en_prod_code="000001.SZ",begin_date="20210501",end_date="20210601")
# 增加股票程式碼列
df['hs_code']='000001.SZ'
df.head()
```

![](https://oscimg.oschina.net/oscnet/up-f81906583ab1b847fefb59812b5c69172d6.png)

其餘介面使用過程與之類似；

### 二、資料落庫示例

以股票列表(stock_list)為例，講解建表、落庫、查詢等操作；全套程式碼見本文第三章；

#### 1、準備工作

（1）在MySQL資料庫中，建立資料庫udata，建立過程見第一講《Python量化資料倉儲搭建系列1：資料庫安裝與操作》；

（2）在MySQL資料庫中，建立資料更新記錄表udata.tb_update_records，表結構如下：

![](https://oscimg.oschina.net/oscnet/up-e175d58e142c353eedc730ffe0e785c5230.png)
建表SQL如下：

```sql
CREATE TABLE udata.tb_update_records (
				table_name CHAR(40),
				data_date CHAR(20),
				update_type CHAR(20),
				data_number INT(20),
				elapsed_time INT(20),
				updatetime CHAR(20)
				)
```

（3）將Token與資料庫引數寫入配置檔案DB_MySQL.config，檔案內容如下：

```
[udata]
token='你的Token'
host='127.0.0.1'
port=3306
user='root'
passwd='密碼'
db='udata'
```

（4）讀取配置檔案中的引數

```python
import configparser
# 讀取配置檔案中，恆有數和資料庫引數
configFilePath = 'DB_MySQL.config'
section = 'udata'
config = configparser.ConfigParser()
config.read(configFilePath)
# 讀取 恆有數(UData) 的 token
token = eval(config.get(section=section, option='token'))
# MySQL連線引數讀取
host = eval(config.get(section=section, option='host'))
port = int(config.get(section=section, option='port'))
db = eval(config.get(section=section, option='db'))
user = eval(config.get(section=section, option='user'))
passwd = eval(config.get(section=section, option='passwd'))
```

#### 2、建表

在社群資料頁面，檢視資料表返回引數欄位，以股票列表(stock_list，https://udata.hs.net/datas/202/）為例：

![](https://oscimg.oschina.net/oscnet/up-3aea5f5e2167662ad062fc42ae3b21dea9a.png)
將建表、刪除表格、清空資料的SQL，寫入配置檔案DB_Table.config，檔案內容如下：

```
[tb_stock_list]
DROP_TABLE='DROP TABLE IF EXISTS tb_stock_list'
CREATE_TABLE='''CREATE TABLE tb_stock_list (
				secu_code CHAR(20),
				hs_code CHAR(20),
				secu_abbr CHAR(20),
				chi_name CHAR(40),
				secu_market CHAR(20), 
				listed_state CHAR(20),
				listed_sector CHAR(20),
				updatetime CHAR(20)
				)'''
DELETE_DATA = 'truncate table tb_stock_list'
```

建表程式碼如下：

```python
# MySQL操作例項化，pymysql連線設定，可開啟、關閉、執行sql、執行sql讀取資料
MySQL = MySQLOperation(host, port, db, user, passwd)
# 讀取配置檔案中，恆有數和資料庫引數
configFilePath = 'DB_Table.config'
section = 'tb_stock_list'
config = configparser.ConfigParser()
config.read(configFilePath)
DROP_TABLE = eval(config.get(section=section, option='DROP_TABLE'))
CREATE_TABLE = eval(config.get(section=section, option='CREATE_TABLE'))
DELETE_DATA = eval(config.get(section=section, option='DELETE_DATA'))
# 刪除表結構
MySQL.Execute_Code(DROP_TABLE)
# 建立表結構
MySQL.Execute_Code(CREATE_TABLE)
```

#### 3、資料落庫

```python
import hs_udata as hs
from sqlalchemy import create_engine
from datetime import datetime
# 設定token
hs.set_token(token)
# 獲取 股票列表 資料
df = hs.stock_list()
# 在最後一列增加系統時間戳
dt = datetime.now()
df['updatetime'] = dt.strftime('%Y-%m-%d %H:%M:%S')
# 由於股票列表資料為全量更新，資料插入之前，先清空表中資料
MySQL.Execute_Code(DELETE_DATA)
# sqlalchemy 連線設定，可用於pandas.read_sql、pandas.to_sql
engine = create_engine('mysql://{0}:{1}@{2}:{3}/{4}?charset=utf8'.format(user,passwd,host,port,db))
# 將資料寫入到MySQL中的資料表
df.to_sql(name='tb_stock_list', con=engine, index=False, if_exists='append')
```

在資料庫中檢視結果如下：

![](https://oscimg.oschina.net/oscnet/up-45025624a1f56bc7924cfb25a58a7dd30e7.png)

#### 4、讀取資料

```python
import pandas as pd
result = pd.read_sql('''SELECT * FROM tb_stock_list ''', con=engine)
print(result.head())
```

#### 三、落庫程式碼封裝

將上述幾步操作，封裝到一起，定義並呼叫python中class類的屬性和方法。程式碼中涉及主要技術點如下：

（1）使用pymysql、pandas.to_sql和pandas.read_sql操作MySQL資料庫；

（2）使用class類的方法，整合建表、插入資料和查詢資料的操作；

（3）使用配置檔案的方式，從本地檔案中，讀取資料庫引數與表操作的SQL程式碼；

（4）使用try容錯機制，結合日誌函式，將執行日誌列印到本地的DB_MySQL_LOG.txt檔案；

```python
import pandas as pd
import hs_udata as hs
from MySQLOperation import *
from sqlalchemy import create_engine
from datetime import datetime
import time
import configparser
import logging
import traceback
import warnings
warnings.filterwarnings("ignore")

class TB_Stock_List:

def __init__(self,MySQL_Config,BD_Name,Table_Config,Table_Name):
        # 建立日誌
        self.logging = logging
        self.logging.basicConfig(filename='DB_MySQL_LOG.txt', level=self.logging.DEBUG
                                 , format='%(asctime)s - %(levelname)s - %(message)s')
        # 讀取配置檔案中，恆有數和資料庫引數
        configFilePath = MySQL_Config
        self.section1 = BD_Name
        config = configparser.ConfigParser()
        config.read(configFilePath)
        # 讀取 恆有數(UData) 的 token
        self.token = eval(config.get(section=self.section1, option='token'))
        # MySQL連線引數讀取
        self.host = eval(config.get(section=self.section1, option='host'))
        self.port = int(config.get(section=self.section1, option='port'))
        self.db = eval(config.get(section=self.section1, option='db'))
        self.user = eval(config.get(section=self.section1, option='user'))
        self.passwd = eval(config.get(section=self.section1, option='passwd'))
        # pymysql連線設定，可開啟、關閉、執行sql、執行sql讀取資料
        self.MySQL = MySQLOperation(self.host, self.port, self.db, self.user, self.passwd)
        # sqlalchemy 連線設定，可用於pandas.read_sql、pandas.to_sql
        self.engine = create_engine('mysql://{0}:{1}@{2}:{3}/{4}?charset=utf8'.format(self.user
                                                                                      , self.passwd
                                                                                      , self.host
                                                                                      , self.port
                                                                                      , self.db))
        # 讀取配置檔案中，恆有數和資料庫引數
        configFilePath = Table_Config
        self.section2 = Table_Name
        config = configparser.ConfigParser()
        config.read(configFilePath)
        self.DROP_TABLE_SQL = eval(config.get(section=self.section2, option='DROP_TABLE'))
        self.CREATE_TABLE_SQL = eval(config.get(section=self.section2, option='CREATE_TABLE'))
        self.DELETE_DATA_SQL = eval(config.get(section=self.section2, option='DELETE_DATA'))

self.logging.info('*********************{0}.{1}*********************'.format(self.section1, self.section2))

def CREATE_TABLE(self):
        try:
            # 刪除表結構
            self.MySQL.Execute_Code('SET FOREIGN_KEY_CHECKS = 0')
            self.MySQL.Execute_Code(self.DROP_TABLE_SQL)
            # 建立表結構
            self.MySQL.Execute_Code(self.CREATE_TABLE_SQL)
            self.logging.info('表{0}.{1}，表格建立成功'.format(self.section1,self.section2))
        except:
            self.logging.info('表{0}.{1}，表格建立失敗'.format(self.section1,self.section2))
            self.logging.debug(traceback.format_exc())

def UPDATE_DATA(self):
        try:
            # 設定token
            hs.set_token(self.token)
            time_start = time.time()  # 計時
            # 獲取 股票列表 資料
            df = hs.stock_list()
            # 在最後一列增加系統時間戳
            dt = datetime.now()
            df['updatetime'] = dt.strftime('%Y-%m-%d %H:%M:%S')
            # 由於股票列表資料為全量更新，資料插入之前，先清空表中資料
            self.MySQL.Execute_Code(self.DELETE_DATA_SQL)
            # 將資料寫入到MySQL中的資料表
            df.to_sql(name='tb_stock_list', con=self.engine, index=False, if_exists='append')
            time_end = time.time()  # 計時
            elapsed_time = round(time_end-time_start,2)
            # 向mysql庫中記錄一條資料更新記錄：表名,資料日期,更新方式,更新條數,更新耗時,系統時間
            self.RECORDS_SQL = '''INSERT INTO udata.tb_update_records 
            VALUES ('{0}','{1}','全量',{2},{3}, SYSDATE())'''.format(self.section2
                                                                   ,dt.strftime('%Y-%m-%d'),len(df),elapsed_time)
            self.MySQL.Execute_Code(self.RECORDS_SQL)
            self.logging.info('表{0}.{1}，資料更新成功'.format(self.section1,self.section2))
        except:
            self.logging.info('表{0}.{1}，資料更新失敗'.format(self.section1,self.section2))
            self.logging.debug(traceback.format_exc())

def READ_DATA(self, WhereCondition=''):
        try:
            result = pd.read_sql('''SELECT * FROM {0}.{1} '''.format(self.section1,self.section2)
                                 + WhereCondition, con=self.engine)
            self.logging.info('表{0}.{1}，資料讀取成功'.format(self.section1,self.section2))
            return result
        except:
            self.logging.info('表{0}.{1}，資料讀取失敗'.format(self.section1,self.section2))
            self.logging.debug(traceback.format_exc())
            return 0
        
if __name__ == '__main__':
    MySQL_Config = 'DB_MySQL.config'
    BD_Name = 'udata'
    Table_Config = 'DB_Table.config'
    Table_Name = 'tb_stock_list'
    # 例項化
    TB_Stock_List_Main = TB_Stock_List(MySQL_Config,BD_Name,Table_Config,Table_Name)
    # 建立表結構
    TB_Stock_List_Main.CREATE_TABLE()
    # 更新資料
    TB_Stock_List_Main.UPDATE_DATA()
    # 讀取資料
    data = TB_Stock_List_Main.READ_DATA()
```

### 四、程式碼及配置檔案下載

（1）附件檔案目錄：

**DB_MySQL.config**

**DB_Table.config**

**MySQLOperation.py**

**TB_Stock_List.py**

（2）下載附件檔案後，將配置檔案DB_MySQL.config中的Token與資料庫引數，修改為自己的引數；將程式碼放置在同一目錄下執行。

下一節《Python量化資料倉儲搭建系列4：股票資料落庫》

Python量化資料倉儲搭建3：資料落庫程式碼封裝

相關文章