返回文章列表
玄貓（BlackCat）

SQLAlchemy 2 資料函式庫實務應用

本篇文章將引導讀者透過 SQLAlchemy 2 學習關聯式資料函式庫的實務應用，包含產品管理、客戶訂單、評論、瀏覽量追蹤及報表生成等導向，並涵蓋 SQLite、MySQL 和 PostgreSQL 等資料函式庫的設定與操作。

資料函式庫 Python
SQLAlchemy 資料函式庫設計 Python MySQL PostgreSQL SQLite

SQLAlchemy 是一個強大的 Python SQL 工具包和物件關聯對映器（ORM），它提供開發者更簡潔、高階的資料函式庫操作方式。本篇文章將以虛擬的 RetroFun 復古電腦商店為例，示範如何使用 SQLAlchemy 2 建立和管理資料函式庫，包含產品目錄、客戶訂單、評論系統、瀏覽量統計等功能，並特別著重於高效能查詢和報表的生成。文章將逐步引導讀者設定開發環境，包括 Python 虛擬環境、SQLAlchemy 安裝，以及 SQLite、MySQL 和 PostgreSQL 等資料函式庫的組態和連線。透過實際案例，讀者將學習如何運用 SQLAlchemy 2 的 ORM 功能，有效地進行資料函式庫操作和管理，並提升資料函式庫應用程式的效能和可維護性。

SQLAlchemy 2 實務應用

前言

在2012年，我開始撰寫Flask Mega-Tutorial，當時是以一系列的部落格文章形式發表，後來修訂成電子書和視訊課程。這項工作讓無數開發者踏入了迷人的網頁開發世界。如果你猜測哪一章節在我的部落格上是最受歡迎的，你可能會正確地猜到是第一章。但你能猜到第二受歡迎的章節是哪一章嗎？

有趣的是，Flask Mega-Tutorial的第二受歡迎的章節不是第二章，而是第四章，其中介紹了資料函式庫和SQLAlchemy函式庫。如果你瀏覽人們在我的部落格上留下的數百個問題，你會發現一個共同的主題。許多問題來自開發者，他們需要知道如何做一些比我在文章中介紹的更複雜的事情，但卻無法自行解決。

多年來，我寫了一些關於最常被問到的資料函式庫問題的獨立文章，但由於這個主題非常廣泛，資料函式庫問題從未停止。本文是我嘗試解決許多開發者所面臨的龐大資料函式庫知識差距。我將帶領你進行一個實務專案，從小開始，然後隨著進度逐漸變得更加複雜。與Flask Mega-Tutorial不同，本文的目標不是建立一個完整的網頁應用程式，而是建立一個靈活、高效的實務關聯式資料函式庫，以及相關的流程和指令碼，並專注於建立高效的查詢和報告。

內容解密：

本文的目標是透過一個實務專案，讓讀者學習如何使用關聯式資料函式庫，並建立高效的查詢和報告。專案將從簡單開始，隨著進度逐漸變得更加複雜。

你將建立什麼

在本文的幫助下，你將要進行的專案目標是支援一家名為RetroFun的虛擬復古家用電腦商店的多個營運導向。RetroFun提供了一個令人印象深刻的1980年代和1990年代家用電腦收藏品銷售。在本文中，你將學習如何為這家虛構公司建立一些資料函式庫操作，包括：

  • 維護按年份、製造商、原產國或CPU等屬性分類別的產品清單。
  • 跟蹤客戶及其訂單。
  • 維護客戶評論和星級評分。
  • 跟蹤公司部落格上發布的文章的頁面瀏覽量。
  • 生成大量簡單和複雜的報告，並始終關注效率和效能。

內容解密：

本文將透過RetroFun這個虛擬案例，介紹如何建立資料函式庫操作，包括產品管理、客戶訂單管理、評論管理、頁面瀏覽量跟蹤以及生成各種報告。

先決條件

在使用本文之前，你需要具備一些先決條件。這些先決條件將在本文中詳細說明，以便你能夠順利地進行後續的學習和實踐。

內容解密：

先決條件是學習本文內容所必需具備的基本知識和技能。瞭解這些先決條件有助於你更好地掌握後續的內容。

資料函式庫設定與專案準備

本章節將引導您完成資料函式庫的設定與專案環境的準備，以便順利進行後續的範例與練習。

專案目錄建立

首先，您需要在適當的父目錄下建立一個專案目錄，用於存放與本文專案相關的所有檔案。請開啟終端機或命令提示字元，導航至適當的父目錄，並執行以下命令：

$ mkdir retrofun
$ cd retrofun

註解：「RetroFun」是本文範例專案所屬的虛擬公司名稱。

Python 與 SQLAlchemy 安裝

如同大多數 Python 專案，您需要建立一個 Python 虛擬環境（virtual environment），以便安裝所有依賴套件。建立虛擬環境的命令如下：

$ python -m venv venv

內容解密：

  1. python -m venv：指示 Python 執行 venv 模組，該模組為 Python 標準函式庫的一部分，用於建立虛擬環境。
  2. venv（第二個）：指定虛擬環境的名稱，本例中命名為 venv，您可以根據需要自訂名稱。
  3. 建立虛擬環境後，您將在專案目錄下看到一個名為 venv 的資料夾，其中包含了獨立的 Python 環境。

虛擬環境啟動與 SQLAlchemy 安裝

建立虛擬環境後，您需要啟動它。不同作業系統下的啟動命令略有不同：

  • 在 Unix 或 MacOS 上：
    $ source venv/bin/activate
  • 在 Windows 上：
    > venv\Scripts\activate

啟動虛擬環境後，命令提示字元前會顯示虛擬環境的名稱，如 (venv) $。接著，您可以安裝 SQLAlchemy：

(venv) $ pip install sqlalchemy

內容解密：

  1. pip install sqlalchemy：使用 pip 套件管理器安裝 SQLAlchemy。
  2. SQLAlchemy 為 Python 的 SQL 工具包與物件關聯對映（ORM）系統，提供了高層次的 SQL 表達方式與資料函式庫互動功能。

資料函式庫選擇與設定

本文範例已在以下三種主要開放原始碼資料函式庫上進行測試：

  • SQLite
  • MySQL
  • PostgreSQL

其他資料函式庫只要獲得 SQLAlchemy 支援，也應能正常運作。

程式碼取得與使用

本文的所有範例程式碼均已發布於 GitHub 儲存函式庫。除了程式碼外，該儲存函式庫還包含了用於填充您將建立的資料函式庫的資料檔案。在適當的時候，本文將提供使用這些資料檔案的指示。

終端機與 Python REPL 使用慣例

在本文中，終端機命令將以 $ 或 (venv) $ 開頭，視是否啟動虛擬環境而定。例如：

$ python hello.py
hello

在 Python REPL（互動式命令列）中輸入的陳述式將以 >>> 開頭，如下所示：

>>> print('hello!')
hello

對於較長的 SQLAlchemy 查詢陳述式，本文採用多行格式顯示，以提高可讀性。例如：

>>> q = (select(select_expression)
...      .method1(expression1)
...      .method2(expression2))

內容解密：

  1. 為了在句點（.）上斷行，Python 需要將整個表示式置於括號內。
  2. 您可以在輸入 REPL 時將這些多行陳述式合併為單行。

建立虛擬環境與安裝SQLAlchemy

在開始使用SQLAlchemy之前，我們需要建立一個虛擬環境來隔離專案的依賴。虛擬環境可以確保不同專案之間的依賴不會互相干擾。

首先，開啟終端機或命令提示字元，並導航到專案目錄。然後，執行以下命令來建立虛擬環境：

$ python -m venv venv

此命令會在專案目錄中建立一個名為venv的子目錄，包含私有Python直譯器的副本。

啟動虛擬環境

要開始使用虛擬環境，需要啟動它。啟動命令取決於所使用的shell或終端機型別：

  • UNIX-based shell（如bash）：
    $ source venv/bin/activate
  • Microsoft Windows命令提示字元：
    $ venv\Scripts\activate
  • PowerShell終端機：
    $ venv\Scripts\activate.ps1

啟動虛擬環境後，命令提示字元應該會改變，以指示虛擬環境已啟動。提示字元看起來應該像這樣：

(venv) $ _

內容解密：

  • source venv/bin/activate：在UNIX-based系統中，用於啟動虛擬環境的命令。它會設定環境變數，使Python和pip指向虛擬環境中的版本。
  • venv\Scripts\activate：在Windows命令提示字元中，用於啟動虛擬環境的命令。
  • venv\Scripts\activate.ps1：在PowerShell中，用於啟動虛擬環境的命令。

安裝SQLAlchemy

虛擬環境啟動後，可以安裝SQLAlchemy：

(venv) $ pip install sqlalchemy

本文中的程式碼需要SQLAlchemy 2.0或更新版本。

資料函式庫選擇

本文中的程式碼是通用設計，可以與SQLAlchemy支援的任何關聯式資料函式庫系統一起使用。程式碼範例已針對三種流行的開放原始碼資料函式庫進行測試：

  • SQLite
  • MySQL
  • PostgreSQL

如果您對特定的資料函式庫系統感興趣，應確保SQLAlchemy支援它，無論是透過內建還是第三方整合。如果您沒有特別的偏好，建議從SQLite開始，因為它是最容易設定和管理的。

SQLite資料函式庫安裝

SQLite是一種C語言函式庫，實作了一個小巧、快速且自包含的關聯式資料函式庫引擎。由於Python直譯器已捆綁了SQLite函式庫，因此無需安裝額外的軟體或進行任何組態即可在Python和SQLAlchemy中使用此資料函式庫。

如果您希望在Python和SQLAlchemy之外檢查和管理SQLite資料函式庫，可以從官方SQLite下載頁面下載適合您作業系統的sqlite3命令列shell。

MySQL資料函式庫設定

MySQL是一種由Oracle Corporation擁有的開放原始碼關聯式資料函式庫。與SQLite不同，此資料函式庫包含伺服器和客戶端元件，都需要安裝。

MySQL伺服器

如果您已經可以存取正在執行的MySQL伺服器，則只需建立新的資料函式庫和資料函式庫使用者，然後跳到下面的客戶端部分。本文的安裝說明示範如何安裝伺服器以及流行的phpMyAdmin管理應用程式。

如果您尚未存取MySQL安裝，最簡單的方法是使用Docker。首先安裝Docker Desktop。

將以下定義複製到專案目錄中名為docker-compose.yml的檔案中：

version: '3'
services:
  db:
    image: mysql
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: changethis!
    ports:
      - "3306:3306"
    volumes:
      - db-data:/var/lib/mysql
  admin:
    image: phpmyadmin
    restart: always
    environment:
      - PMA_ARBITRARY=1
    ports:
      - 8080:80
volumes:
  db-data:

內容解密：

  • version: '3'：指定docker-compose檔案的版本。
  • services：定義服務（在本例中為db和admin）。
  • db服務使用MySQL映像，並設定環境變數MYSQL_ROOT_PASSWORD。
  • ports：將容器的3306埠對映到主機的3306埠。
  • volumes：將主機的db-data卷掛載到容器的/var/lib/mysql，以持久化資料函式庫資料。
  • admin服務使用phpMyAdmin映像，並設定環境變數PMA_ARBITRARY=1以允許任意伺服器連線。

儲存此檔案後，在終端機中執行以下命令以啟動MySQL伺服器：

$ docker-compose up -d

首次執行此命令時，將需要一些時間，因為Docker需要從Docker Hub倉函式庫下載MySQL和phpMyAdmin容器映像。下載映像後，應該只需要幾秒鐘即可啟動容器，此時MySQL應該已佈署在您的電腦上並準備好使用。

建立新的資料函式庫和使用者

  1. 開啟phpMyAdmin：在網頁瀏覽器的位址列中輸入http://localhost:8080。
  2. 登入：使用伺服器名稱db、使用者名稱root和在docker-compose.yml檔案中設定的root密碼。
  3. 建立資料函式庫：在phpMyAdmin介面中，點選「Databases」標籤，然後在「Create Database」部分輸入新資料函式庫的名稱（例如retrofun），然後點選「Create」按鈕。
  4. 建立新使用者：點選新建立的資料函式庫的「Privileges」標籤，然後在頁面底部找到「New」部分，點選「Add user account」連結以建立新使用者。

內容解密：

  • 登入phpMyAdmin並導航到「Databases」標籤以建立新的資料函式庫。
  • 建立新使用者並授予適當的許可權，以避免使用具有過多許可權的root使用者進行日常操作。

資料函式庫設定：MySQL 與 PostgreSQL

MySQL 資料函式庫設定

要開始使用 MySQL，首先需要安裝 MySQL 伺服器和 phpMyAdmin 管理工具。以下步驟將指導你如何使用 Docker Compose 完成這些安裝。

MySQL 伺服器與 phpMyAdmin 安裝

  1. 建立 docker-compose.yml 檔案：在你的專案目錄中建立一個名為 docker-compose.yml 的檔案，並將以下組態內容複製到該檔案中。
version: '3'
services:
  db:
    image: mysql
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: changethis!
    ports:
      - "3306:3306"
    volumes:
      - db-data:/var/lib/mysql
  phpmyadmin:
    image: phpmyadmin/phpmyadmin
    restart: always
    ports:
      - 8080:80
    environment:
      PMA_HOST: db
volumes:
  db-data:
  1. 啟動服務：在終端機中執行以下命令來啟動 MySQL 伺服器和 phpMyAdmin。
$ docker-compose up -d

  1. 存取 phpMyAdmin：開啟網頁瀏覽器，輸入 http://localhost:8080 以存取 phpMyAdmin。預設情況下，伺服器名稱為 db，使用者名稱為 root，密碼是在 docker-compose.yml 檔案中設定的 MYSQL_ROOT_PASSWORD。

  2. 建立資料函式庫和使用者：

    • 登入 phpMyAdmin 後，建立一個新的資料函式庫，例如命名為 retrofun。
    • 建立一個新使用者，例如同樣命名為 retrofun，並設定密碼。確保將主機設定為 % 以允許從任何主機連線。
    • 授予該使用者對 retrofun 資料函式庫的所有許可權。

  3. 停止和重啟 MySQL 伺服器：

    • 要停止 MySQL 伺服器，可以執行 $ docker-compose down。
    • 要重啟伺服器，再次執行 $ docker-compose up -d。這樣做不會導致資料遺失。

MySQL 使用者端安裝

要從 Python 存取 MySQL 資料函式庫，需要安裝一個 MySQL 使用者端（或驅動程式）。這裡推薦使用 pymysql。

  1. 安裝 pymysql：在你的 Python 虛擬環境中執行以下命令。
(venv) $ pip install pymysql cryptography
  1. 連線設定：記下你的資料函式庫連線設定，包括主機名稱（從 phpMyAdmin 容器內連線時使用 db）、埠號（3306）、資料函式庫名稱（retrofun）、使用者名稱（retrofun）和密碼（你設定的密碼）。

PostgreSQL 資料函式庫設定

PostgreSQL 是另一個主要的開放原始碼關聯式資料函式庫系統，與 MySQL 相似，也需要獨立的伺服器和使用者端。

PostgreSQL 伺服器與 pgAdmin 安裝

  1. 建立 docker-compose.yml 檔案：在你的專案目錄中建立一個名為 docker-compose.yml 的檔案，並將以下組態內容複製到該檔案中。
version: '3'
services:
  db:
    image: postgres
    restart: always
    environment:
      POSTGRES_PASSWORD: changethis!
    ports:
      - "5432:5432"
    volumes:
      - db-data:/var/lib/postgresql/data
  admin:
    image: dpage/pgadmin4
    restart: always
    environment:
      PGADMIN_DEFAULT_EMAIL: [email protected]
      PGADMIN_DEFAULT_PASSWORD: changethis!
    ports:
      - 8080:80
    volumes:
      - admin-data:/var/lib/pgadmin
volumes:
  db-data:
  admin-data:
  1. 啟動服務：執行以下命令來啟動 PostgreSQL 伺服器和 pgAdmin。
$ docker-compose up -d

  1. 存取 pgAdmin：開啟網頁瀏覽器，輸入 http://localhost:8080 以存取 pgAdmin。使用你在 docker-compose.yml 檔案中設定的電子郵件和密碼登入。

  2. 新增 PostgreSQL 伺服器：

    • 在 pgAdmin 中，點選 “Add New Server” 圖示來新增伺服器。
    • 在 “General” 分頁中，為伺服器命名，例如 db。
    • 在 “Connection” 分頁中，設定 “Host name” 為 db，並輸入你設定的 PostgreSQL 管理員密碼。

  3. 建立資料函式庫和使用者：

    • 右鍵點選左側邊欄中的伺服器名稱，選擇 “Create” > “Login/Group Role…” 來建立新使用者。
    • 在 “General” 分頁中輸入使用者名稱，例如 retrofun。
    • 切換到 “Definition” 分頁，設定使用者密碼。
    • 切換到 “Privileges” 分頁，確保啟用 “Can login?” 和 “Inherit rights from the parent roles?” 選項。

連線設定

記下你的 PostgreSQL 資料函式庫連線設定，以便稍後在 Python 中使用。