Python通過SQLAlchemy-SQL_Expression來訪問操作MySQL資料庫

前言

Python支持通過多種方式訪問MySQL資料庫。可能有些剛入門的朋友們對Python訪問MySQL資料庫還不是很熟悉，故計劃對Python訪問MySQL資料庫的這幾種方式分別作一介紹。

本系列中已經對 &34; 進行了介紹。

本系列中已經對 &34; 進行了介紹。

本系列中已經對 &34; 進行了介紹。

本系列中已經對 「Python通過SQLAlchemy-Raw_SQL來訪問操作MySQL資料庫」 進行了介紹。

本文是本系列的第五篇，對 「Python通過SQLAlchemy-SQL_Expression來訪問操作MySQL資料庫」 進行介紹。

本文以Windows平臺上開發為例進行講解，相關基本開發環境為：(1) Windows 7 （64位）；(2) MySQL v8.0.21；(3) Python v3.8.5；(4) PyMySQL v0.10.0；( 5) SQLAlchemy v1.3.18。

本次分享的內容目錄如下：

前言

SQLAlchemy 介紹

SQLAlchemy 安裝

Python通過SQLAlchemy訪問資料庫的方式

SQLAlchemy-SQL_Expression方式介紹

SQLAlchemy-SQL_Expression示例代碼

結束語

---

1. SQLAlchemy 介紹

1.1 SQLAlchemy簡介

SQLAlchemy的SQL工具包及對象關係映射器是一組用於處理Python訪問操作資料庫的綜合工具。它具有幾個不同的功能區域，可以單獨使用或組合使用。其主要組件如下圖所示，組件依存關係分為多個層：

SQLAlchemy「採用簡單的Python語言，為高效和高性能的資料庫訪問設計，實現了完整的企業級持久模型」。SQLAlchemy的理念是，SQL資料庫的量級和性能重要於對象集合；而對象集合的抽象又重要於表和行。因此，SQLAlchemy採用了類似於Java裡Hibernate的數據映射模型，而不是其他ORM框架採用的Active Record模型。不過，Elixir和declarative等可選插件可以讓用戶使用聲明語法。

SQLAlchemy是Python程式語言下的一款開源軟體，使用MIT許可證發行。SQLAlchemy首次發行於2006年2月，並迅速地在Python社區中最廣泛使用的ORM工具之一，不亞於Django的ORM框架。

---

SQLAlchemy資源信息如下：

(1) SQLAlchemy官網主頁：https://www.sqlalchemy.org/

(2) SQLAlchemy官網文檔：https://docs.sqlalchemy.org/en/13/

(3) SQLAlchemy項目（PyPI）：https://pypi.org/project/SQLAlchemy

(4) SQLAlchemy最新版本：1.3.18（2020年6月26日）

1.2 dialect簡介

dialect是SQLAlchemy用於和DBAPI進行通信，根據配置文件的不同調用不同的DBAPI，進而實現對資料庫的操作。

注意：所有的dialect都需要安裝適用的DBAPI驅動，否則會一直連接不上資料庫。

本文主要是講解通過SQLAlchemy來訪問操作MySQL資料庫的，所以下面只介紹跟MySQL相關的部分。

SQLAlchemy支持MySQL從4.1版本到最新版本。SQLAlchemy支持MySQL資料庫的dialect/DBAPI選項主要如下表：

---

2. SQLAlchemy 安裝

2.1 MySQL下載安裝

省略，詳見中對應章節（第2.1章節）內容。

2.2 Python下載安裝

省略，詳見中對應章節（第2.2章節）內容。

2.3 PyMySQL下載安裝

省略，詳見中對應章節（第2.3章節）內容。

2.4 SQLAlchemy下載安裝

省略，詳見中對應章節（第2.4章節）內容。

---

3. Python通過SQLAlchemy訪問資料庫的方式

本文第1章有介紹過SQLAlchemy不同的功能區域可以單獨使用或組合使用，因此目前Python可以通過以下三種SQLAlchemy方式來訪問資料庫：

(1) SQLAlchemy-Row_SQL方式

(2) SQLAlchemy-SQL_Expression方式

(3) SQLAlchemy-ORM方式

這三種方式的簡單對比分析如下表所示：

本文限於篇幅，只介紹第(2)種SQLAlchemy-SQL_Expression方式。

---

4. SQLAlchemy-SQL_Expression方式介紹

4.1 SQLAlchemy-SQL_Expression方式的執行流程

Python通過SQLAlchemy-SQL_Expression訪問操作MySQL資料庫的完整執行流程如下：

– (1) 導入SQLAlchemy模塊

– (2) 創建引擎（含定義資料庫連接路徑）

– (3) 定義數據表（通過MetaData類和Table類）

– (4.1) 創建/刪除數據表

-- (4.2) 構造SQL Expression（通過 SQLAlchemy Core 轉換成SQL語句）

-- (5) 創建連接（通過創建好的引擎在連接池中建立起真正的資料庫連接）

-- (6) 執行SQL語句（基於連接使用dialect調用DBAPI，然後將SQL語句交給它去執行）

4.2 相關方法介紹

一、導入SQLAlchemy

在Python中導入sqlalchemy模塊：

import sqlalchemy

在實際編碼時，為簡化代碼，一般不直接使用import sqlalchemy，而是使用sqlalchemy模塊中直接用到的相關部分，參考如下：

from sqlalchemy import create_engine......

二、定義連接路徑

要連接資料庫，需要一個連接路徑，連接路徑格式如下：

<dialect>+<driver>://<username>:<password>@<host:port>/<database>[?<options>]

如：定義一個通過dialect/DBAPI選項（PyMySQL驅動程序）到MySQL資料庫（資料庫名mydb，資料庫用戶名myuser，資料庫密碼mypasswd，資料庫主機名127.0.0.1，資料庫主機埠號3306）的連接路徑，則其示例代碼參考如下：

三、創建引擎

Engine是任何SQLAlchemy應用程式的起點。它通過連接池和Dialect傳遞信息給SQLAlchemy應用程式，它描述了如何與特定類型的資料庫/DBAPI組合進行通信。

引擎的總體架構圖示如下：

有了連接路徑以後，創建引擎只需要發出一個調用 create_engine()即可：

from sqlalchemy import create_engine······myengine = create_engine(DB_URL, max_overflow=10, echo=False)

create_engine()說明：

——create_engine()方法通過Dialect創建了一個資料庫引擎對象（此時尚未真正連接MySQL資料庫）。僅在首次調用Engine.connect() 或 Engine.execute()方法時，Pool對象才會在127.0.0.1:3306上真正建立一個DBAPI的資料庫連接。

——參數DB_URL：資料庫連接路徑

——參數max_overflow：表示設置連接池中連接數目, 默認為10；

——參數echo ：表示列印轉換後的每條SQL語句，方便調試，生產環境下可以設置為 False。

四、定義數據表

定義數據表的本質就是建立SQLAlchemy與數據表的映射關係。如果想通過`SQLAlchemy-SQL_Expression方式訪問操作MySQL資料庫，不管當前資料庫中是否已經存在數據表，都需要先行定義好了數據表，才能進行後續SQL Expression的操作。如果不定義數據表，就無法建立起映射關係，SQL Expression就不知道是附加在哪個數據表上的操作。

SQLAlchemy通過MetaData類和Table類構造函數來定義數據表（類似於常規的CREATE TABLE的SQL語句）。定義數據表時，注意在「代碼和資料庫」中的」表名和欄位名」必須保持一致。

在大多數情況下，SQL Expression Language都會根據數據表中的欄位來構造其表達式。在SQLAlchemy中，一個Column對象表示一個欄位，每一個Column對象都會跟一個Table對象相關聯。Table對象及其子對象的集合稱為database metadata，MetaData對象可以認為是記錄Table對象的目錄。MetaData對象、Table對象以及Column對象之間的關係如下圖所示：

定義數據表mytable示例代碼如下：

在定義好數據表後就能創建數據表了。

五、創建/刪除數據表

定義好了數據表後，就可以通過Metadata類的create_all()方法來創建數據表了（可以同時創建多個數據表，取決於之前已經定義的數據表）。

刪除所有已定義的數據表，如果數據表存在，則忽視metadata.drop_all(myengine)

六、構造SQL Expression

SQLAlchemy-Core可以對常見的增刪改查操作進行構造SQL Expression，簡單示例如下：

七、創建連接

有了引擎以後，可以通過引擎的connect()方法來創建一個連接對象（此時才真正連接到MySQL資料庫）

 myconn = myengine.connect()

八、執行SQL（增刪改查操作）

建立起真正的資料庫連接後，就可以通過 Connection對象的execute()方法來執行SQL（增刪改查操作）了。並返回一個ResultProxy對象（類似於DBAPI Cursor對象）。

 myresult = myconn.execute(sql_statement)

---

5. SQLAlchemy-SQL_Expression 示例代碼

下面針對Python通過SQLAlchemy-SQL_Expression方式訪問MySQL資料庫常見的操作（如：連接資料庫、創建資料庫、刪除資料庫、創建數據表、刪除數據表、插入數據記錄、刪除數據記錄、修改數據記錄、查詢數據記錄等）分別給出一個較為完整的入門示例（含詳細代碼注釋）。

注1：連接資料庫、創建資料庫、刪除資料庫的示例代碼跟上一篇的Python通過SQLAlchemy-Raw_SQL訪問MySQL資料庫中的一樣。

注2：創建數據表、刪除數據表、插入數據記錄、刪除數據記錄、修改數據記錄、查詢數據記錄是採用SQLAlchemy-SQL_Expression方式來完成的，請重點關注差異之處。

注3：本示例代碼使用的dialect/DBAPI選項是PyMySQL，所以必須提前安裝好PyMySQL驅動（可通過命令pip install pymysql完成安裝），否則會一直連接不上MySQL資料庫。如果想使用dialect/DBAPI選項是mysqlclient，則請提前安裝好MySQLdb驅動（可通過命令pip install mysqlclient完成安裝）。

5.1 創建資料庫連接示例

5.2 創建資料庫示例

注意：在通過create_engine()方法創建引擎時，參數DB_URL中包含的資料庫名DB_NAME必須實際存在，否則後續無法真正連接MySQL資料庫。因此在通過SQLAlchemy創建一個新的MySQL資料庫時，可以採用創建引擎時參數DB_URL中包含的資料庫名DB_NAME為已經存在一個資料庫名，而在創建資料庫SQL語句裡給的是待新創建的資料庫名DB_NEW_NAME。

5.3 刪除資料庫示例

5.4 創建數據表示例

5.5 刪除數據表示例

5.6 插入數據記錄示例

5.7 批量插入數據記錄示例

5.8 刪除數據記錄示例

5.9 修改數據記錄示例

5.10 查詢數據記錄示例

根據讀取數據的多少，應採用不同的方法：

-- (1) 如果讀取全部數據，使用fetchall() 方法。

-- (2) 如果讀取多條數據，使用fetchmany(n) 方法。

-- (3) 如果讀取一條數據，使用fetchone()方法。

---

結束語

通過上面的介紹，相信感興趣的朋友們已經對Python通過SQLAlchemy-SQL_Expression方式來訪問操作MySQL資料庫有了一個基本的入門了解了。想掌握更多詳細內容，請查看SQAlchemy官網文檔（https://docs.sqlalchemy.org/en/13/index.html）。

本系列下一篇文章將針對 「Python通過SQLAlchemy-ORM」訪問操作MySQL資料庫進行介紹，敬請關注後續文章！

本文為原創，如果文章對您有所幫助，喜歡的話就點個讚加關注支持一下哈：)