學習原因：

經過前 3 篇的文章，應該對 Database 有一點概念，接下來就學習應用 Python 去存取 Database 的內容。之後作測試的時候，需要讀取 Database 的資料，與測試結果作比對來確認測試是否通過。

這篇文章會介紹用 pymysql 和 sqlalchemy 套件。

學習目標：

• 應用 Pymysql 和 sqlalchemy 套件連接 Database，並讀取資料

pymysql 套件

pymysql 是 MySQL 數據庫連接 的套件，它提供了直接的接口來與 MySQL 數據庫進行交互，可以執行 SQL 查詢、插入、更新等操作，並處理數據庫的連接和交互。

優點：

• 輕量級，使用方便。
• 直接使用 SQL 查詢，適合那些想要直接控制 SQL 語句的開發者。

缺點：

• Pandas 套件不支援，沒法搭配 Pandas 使用。

以下是應用的例子和說明：

先在 MySQL Workbench 建立一個 User Table:

CREATE TABLE `User` (
  `userId` int NOT NULL AUTO_INCREMENT,
  `email` varchar(45) DEFAULT NULL,
  `pw` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`userId`)
);

INSERT INTO user (email, pw) VALUES ('abc@abc.com', '123456')

建立一個 Python 檔：

import pymysql

db_settings = {
    ### 必須的基本資料
    "user": "root",
    "password": "<password>",
    "host": "127.0.0.1",

    ### 選填的部分
    # 一個 SQL Server 可以存在多個 Schema，一般來說會設定預設應用的 Schema
    # 若需要使用 SQL Server 內其他 Database，在 SQL 指定即可
    "database": "<schema name>",

    # 預設為 3306，若沒有改用其他 port，這個可以不填
    "port": 3306,

    # 設定讀取時數據庫未能返回響應的最大時間，會觸發超時錯誤。
    # 對於避免應用程式長時間阻塞或等待，我認為是很重要的設定。特別是測試需要大量讀取 DB 的情況。
    # 選擇適當的 read timeout 需要根據應用情況和需求進行評估。
    # 通常來說，可以從 5 秒到 30 秒之間進行調整，然後根據測試和實際使用情況進行微調。
    "read_timeout": 5,
}

# 連接 MySQL Server，執行程式碼後會自動關閉
with pymysql.connect(**db_settings) as connection:

    # 遊標（Cursor）是一個用於執行 SQL 查詢和操作數據的機制
    # 啟用 Cursor，在程式碼執行完畢後會自行關閉
    with connection.cursor() as cursor:
        select_sql = "SELECT userId, email, pw FROM User;"

        # 若沒有設定預設 database 時，需要在 SQL 指定用哪個 database 的 table
        # select_sql = "SELECT userId, email, pw FROM <schema>.user;"

        # 執行 SQL
        cursor.execute(select_sql)

        # 應用 Cursor 讀取所有資料
        result = cursor.fetchall()
        for row in result:
            print(row[0], row[1], row[2])

Output:

1 abc@abc.com 123456

問題來了，row[0], row[1], row[2] 是什麼？ 看一下 SQL 可以對比到分別是 id, email 和 pw 對吧。Column 若是再多一點，相信我，你會找到想哭。。。
跟 Excel 之前提到的一樣，避免用 index 去取值，應用明確的 Key 取值會大幅提高可讀性與維護性。

應用 DictCursor

原本的 Cursor 會預設使用 Cursor，取得的資料會是 Tuple List 的格式，只能應用 index 取對應 Column 的值。因此需要改 Cursor 為 DictCursor，會產出 Dictionary List 的格式，可以在 db_settings 時設定

import pymysql
# DictCursor 需要從 pymysql.cursors import
from pymysql.cursors import DictCursor

db_settings = {
    "user": "root",
    "password": "<password>",
    "host": "127.0.0.1",
    "database": "<schema>",
    "read_timeout" : 5,
    # 預設是 Cursor，要改為 DictCursor
    "cursorclass": DictCursor
}

with pymysql.connect(**db_settings) as connection:
    with connection.cursor() as cursor:
        select_sql = "SELECT userId, email, pw FROM user;"	    
        cursor.execute(select_sql)
	
	    result = cursor.fetchall()
	    for row in result:
            # 這裡便可以用 key 來取值了
	        print(row["userId"], row["email"], row["pw"])

雖然說使用 DictCursor 跟 Cursor 在處理大量數據時，在效能上有這麼一點點的差異，但這差異比起可讀性和維護性少多了。

sqlalchemy 套件

sqlalchemy 則是一個全功能的數據庫工具包，提供了 ORM (Object Relation Mapping) 功能以及更高級的數據庫操作方法。它不僅可以用於 MySQL，還支援多種數據庫系統。

優點：

• 提供強大的 ORM 功能，可以將 Python Object 映射到數據庫表格，簡化了數據庫操作的流程。
• 支援多種數據庫系統 (I.e. MySQL, Oracle, MsSQL, etc.)，具有更大的彈性。
• 可配合 Pandas 使用作資料處理

缺點：

• 學習曲線較陡，尤其對於初學者來說可能需要一些時間來熟悉其使用方法。

以下是一個 sqlalchemy 搭配 pandas 使用的例子:

import pandas as pd
from sqlalchemy import create_engine, text

username = "xxx"
password = "xxx"
host = "xxx.xxx.xxx.xxx"
port = 3306
database = "xxxx"

# 建立 SQL Engine
engine = create_engine(f"mysql+pymysql://{username}:{password}@{host}:{port}/{database}")

# 連接 SQL Engine，程式碼執行完畢後會自動關閉
with engine.connect() as connection:
    select_sql = "Select pw from user where email = 'abc@abc.com'"

		# 用於執行 SQL 查詢並將查詢結果轉換為 Pandas DataFrame
    df = pd.read_sql(select_sql, engine)

    for row in df.iloc():
        print(f"Password: {row['pw']}")

Output:

Password: 123456

Object Relational Mapping (ORM)

順帶一提 ORM (Object Relational Mapping) 是透過操作物件的方式來操作資料庫，以提升開發效率和程式碼可讀性。但 ORM 可能無法生成效能高的 SQL 查詢，有時候可能直接編寫 SQL 查詢可能更有效。

以下是應用 sqlalchemy 套件的 ORM 範例

from sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.orm import Session, declarative_base

username = "xxx"
password = "xxx"
host = "xxx.xxx.xxx.xxx"
port = 3306
database = "xxxx"

# 建立 SQL Engine
engine = create_engine(f"mysql+pymysql://{username}:{password}@{host}:{port}/{database}")

# 用於創建一個基本的映射類基類，可以作為所有映射類的父類，幫助定義映射類的結構和屬性
# 以及與數據庫表之間的映射關係。
Base = declarative_base()
class User(Base):
	### 定義 Table Name
    __tablename__ = 'User'

	### 定義 Table Columns
    userId = Column(Integer, primary_key=True)
    email = Column(String)
    pw = Column(String)

# 建立 ORM 的 Session 以執行，所以程式碼結束後會自動關閉
with Session(engine) as session:

	# 讀取 User 的資料，條件為 email = abc@abc.com，且取得第一筆
    user = session.query(User).filter_by(email="abc@abc.com").first()
    print(user.userId, user.email, user.pw)

這裡只是給個認知，還是不太建議初學者使用，先練好怎樣寫 SQL 吧。

簡單說了怎樣應用 Python 存取 Database 的資料，給自己一個小練習，用 Python 對 Database 的 Table 作 CRUD 吧。