### Python量化資料倉儲搭建系列2：Python運算元據庫

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

在上一節講述中，我們選擇了MySQL作為本系列教程的資料庫，故本文著重講解Python操作MySQL的步驟，並封裝方法。在文末簡單介紹Python操作MongoDB、SQLite、PostgreSQL資料庫；

### 一、pymysql用法

#### 1、安裝pymysql模組

```python
pip install pymysql
```

#### 2、連線資料庫

```python
from pymysql import * 
# 開啟資料庫連線，資料庫引數可以在MySQL介面或資料庫配置檔案中檢視
conn = pymysql.connect(host = '資料庫IP',
                       port = '埠',
                       user = '使用者名稱',
                       password = '密碼',
                       database='資料庫名稱')

# 使用 cursor() 方法建立一個遊標物件 cursor
cursor = conn.cursor()

# 在資料庫操作執行完畢後，關閉資料庫連線
# conn.close()
```

#### 3、常見SQL程式碼執行

```python
from pymysql import * 
# 執行SQL程式碼：建表、刪表、插入資料
def Execute_Code(sql_str):
    # 開啟資料庫連線
	conn = pymysql.connect(host = '127.0.0.1',port = 3306,user = 'root',
                           password = '密碼',database='udata')
    # 使用 cursor() 方法建立一個遊標物件 cursor
    cursor = conn.cursor()
    try:
        # 使用execute()方法執行SQL
        cursor.execute(sql)
        # 提交到資料庫執行
        conn.commit()
	except:
        # 發生錯誤時回滾
        conn.rollback()
    # 關閉資料庫連線
    conn.close() 
```

A、建表

```python
sql_str = '''CREATE TABLE TB_Stock_List_Test (
                        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));'''
Execute_Code(sql_str)
```

B、插入資料

```python
sql_str = '''
INSERT INTO TB_Stock_List_Test
(`secu_code`,`hs_code`,`secu_abbr`,`chi_name`,`secu_market`,`listed_state`
,`listed_sector`,`updatetime`)
VALUES
('000001','000001.SZ','平安銀行','平安銀行股份有限公司','深圳證券交易所','上市',
'主機板','2021-10-25 20:10:55');
'''
Execute_Code(sql_str)
```

C、更新資料

```python
sql_str = "UPDATE tb_stock_list SET updatetime = '2021-10-30 20:10:55' "
Execute_Code(sql_str)
```

D、刪除資料

```python
sql_str = 'DELETE FROM tb_stock_list'
Execute_Code(sql_str)
```

E、刪除表格

```python
sql_str = 'DROP TABLE IF EXISTS tb_stock_list'
Execute_Code(sql_str)
```

#### 4、查詢操作

```python
def Select_Code(sql_str):
    # 開啟資料庫連線
	conn = pymysql.connect(host = '127.0.0.1',port = 3306,user = 'root',
                           password = '密碼',database='udata')
    # 使用 cursor() 方法建立一個遊標物件 cursor
    cursor = conn.cursor()
    # 使用execute()方法執行SQL
    cursor.execute(sql_str)
    # 獲取所有記錄列表
    results = cursor.fetchall()
    # 關閉資料庫連線
    conn.close()
    
    return results
```

```python
sql_str = 'select * from tb_stock_list'
results = Select_Code(sql_str)
results
```

#### 5、方法封裝

將上述用法，封裝為自定義類，存為MySQLOperation.py檔案，程式碼如下：

```python
from pymysql import * 
# MySQL操作函式
class MySQLOperation:
    def __init__(self, host, port, db, user, passwd, charset='utf8'):
        # 引數初始化
        self.host = host
        self.port = port
        self.db = db
        self.user = user
        self.passwd = passwd
        self.charset = charset
    def open(self):
        # 開啟資料庫連線
        self.conn = connect(host=self.host,port=self.port
                            ,user=self.user,passwd=self.passwd
                            ,db=self.db,charset=self.charset)
        # 使用 cursor() 方法建立一個遊標物件 cursor
        self.cursor = self.conn.cursor()
    def close(self):
        # 斷開資料庫連線
        self.cursor.close()
        self.conn.close()
    def Execute_Code(self, sql):
        # 執行SQL程式碼：建表、刪表、插入資料
        try:
            self.open()               # 開啟資料庫連線
            self.cursor.execute(sql)  # 使用execute()方法執行SQL
            self.conn.commit()        # 提交到資料庫執行 
            self.close()              # 斷開資料庫連線
        except Exception as e:
            self.conn.rollback()      # 發生錯誤時回滾
            self.close()              # 斷開資料庫連線
            print(e)
    def Select_Code(self, sql):
        # 執行SQL程式碼，查詢資料
        try:
            self.open()                        # 開啟資料庫連線
            self.cursor.execute(sql)           # 使用execute()方法執行SQL
            result = self.cursor.fetchall()    # 獲取所有記錄列表
            self.close()                       # 斷開資料庫連線
            return result                      # 返回查詢資料
        except Exception as e:
            self.conn.rollback()               # 發生錯誤時回滾
            self.close()                       # 斷開資料庫連線
            print(e)
```

插入與查詢用法如下，其餘用法類似，此處不再贅述；

```python
import pandas as pd
host='127.0.0.1'
port=3306
user='root'
passwd="密碼"
db='udata'
# 方法例項化
MySQL = MySQLOperation(host, port, db, user, passwd)
# 插入操作程式碼
sql_str = '''
INSERT INTO tb_stock_list
(`secu_code`,`hs_code`,`secu_abbr`,`chi_name`,`secu_market`,`listed_state`,`listed_sector`,`updatetime`)
VALUES
('000001','000001.SZ','平安銀行','平安銀行股份有限公司','深圳證券交易所','上市',
'主機板','2021-10-25 20:15:55');
'''
MySQL.Execute_Code(sql_str)
# 查詢資料
sql_str = 'select * from tb_stock_list'
results = MySQL.Select_Code(sql_str)
results
```

### 二、sqlalchemy用法

由於上述pymysql用法已經可以滿足大部分使用需求，sqlalchemy實現功能與之類似。這裡著重介紹一下基於sqlalchemy連結資料庫的pandas.to_sql和pandas.read_sql操作。

#### 1、安裝pymysql模組

```python
pip install sqlalchemy
```

#### 2、連線資料庫

```python
from sqlalchemy import create_engine
host='127.0.0.1'
port = 3306
user='root'
password='密碼'
database='udata'
engine = create_engine('mysql://{0}:{1}@{2}:{3}/{4}?charset=utf8'.format(user
                                                                         ,password
                                                                         ,host
                                                                         ,port
                                                                         ,database))
```

#### 3、pandas.to_sql

將DataFrame中的資料，寫入MySQL資料庫，程式碼示例如下：

```python
import pandas as pd
# 定義需要寫入的資料，DataFrame格式
data = pd.DataFrame([['000001','000001.SZ','平安銀行','平安銀行股份有限公司'
                      ,'深圳證券交易所','上市','主機板','2021-10-25 20:12:55'],
                   ['000002','000002.SZ','萬 科Ａ','萬科企業股份有限公司'
                    ,'深圳證券交易所','上市','主機板','2021-10-25 20:12:55']])
# 列名賦值
data.columns = ['secu_code','hs_code', 'secu_abbr', 'chi_name'
                , 'secu_market', 'listed_state','listed_sector','updatetime']
# 寫入資料庫
data.to_sql(name='tb_stock_list', con=engine, index=False, if_exists='append')
```

`if_exists` 引數用於當目標表已經存在時的處理方式，預設是 `fail`，即目標表存在就失敗。另外兩個選項是 `replace` 表示替代原表，即刪除再建立，`append` 選項僅新增資料。

#### 4、pandas.read_sql

從資料庫中，將資料讀取為DataFrame，程式碼示例如下：

```python
# 將sql查詢結果，賦值為result
result = pd.read_sql('''SELECT * FROM tb_stock_list ''', con=engine)
result
```

### 三、Python操作其他常見資料庫

#### 1、MongoDB

（1）安裝pymongo：pip install pymongo

（2）操作簡介

```python
import pymongo
# 連線MongoDB
conn = pymongo.MongoClient(host='localhost',port=27017
                           ,username='username', password='password')
# 指定資料庫
db = conn['udata']  # db = client.udata
# 指定集合
collection = db['tb_stock_list']  # collection = db.tb_stock_list
# 插入資料 insert_one()、insert_many()
data1 = {}  # 集合，鍵值對，1條資料
data2 = {}  # 集合，鍵值對，1條資料
result = collection.insert_many([data1, data2])
# result = collection.insert_one(data1)
# 查詢資料 find_one()、find()
result = collection.find_one({'secu_code': '000001'})
# 更新資料 update_one()、update()
result = collection.update_one({'secu_code': '000001'}, {'$set': {'hs_code': '000001'}})
# 刪除資料 remove()、delete_one()和delete_many()
result = collection.remove({'secu_code': '000001'})
```

#### 2、SQLite

（1）安裝sqlite3：pip install sqlite3

（2）操作簡介

```python
import sqlite3
# 連線資料庫
conn = sqlite3.connect('udata.db')
# 建立遊標
cursor = conn.cursor()
# 執行SQL
sql = "增減刪等SQL程式碼"
cursor.execute(sql)
# 查詢資料
sql = "查詢sql程式碼"
values = cursor.execute(sql)
# 提交事物
conn.commit()
# 關閉遊標
cursor.close()
# 關閉連線
conn.close()
```

#### 3、PostgreSQL

（1）安裝psycopg2：pip install psycopg2

（2）操作簡介

```python
import psycopg2
# 連線資料庫
conn = psycopg2.connect(database="udata", user="postgres"
                        , password="密碼", host="127.0.0.1", port="5432")
# 建立遊標
cursor = conn.cursor()
# 執行SQL
sql = "增減刪等SQL程式碼"
cursor.execute(sql)
# 查詢全部資料
sql = "查詢sql程式碼"
cursor.execute(sql)
rows = cursor.fetchall()
# 事物提交
conn.commit()
# 關閉資料庫連線
conn.close()
```

綜上，Python運算元據庫的簡要介紹就結束了；還有很多型別的資料庫，Python操作它們的過程大同小異，後續我也將會繼續梳理相關資料。

下一節《Python量化投資資料倉儲搭建3：資料落庫程式碼封裝》

Python量化資料倉儲搭建系列2：Python運算元據

相關文章