SQLAlchemy 專案結構與資料函式庫查詢

SQLAlchemy 作為 Python 的 ORM 工具，簡化了資料函式庫操作的複雜性，提升了開發效率。本文除了介紹 SQLAlchemy 的基本用法外，更著重於實務上的應用，例如在 Flask 和 FastAPI 框架中如何整合 SQLAlchemy，以及如何撰寫高效的資料函式庫查詢陳述式。藉由理解 SQLAlchemy 的運作機制和最佳實踐，開發者能更有效地管理和操作資料函式庫，進而提升應用程式的效能和可維護性。尤其在處理複雜的資料關係和查詢需求時，SQLAlchemy 的優勢更加明顯，能有效避免繁瑣的 SQL 語法，並提高程式碼的可讀性。

SQLAlchemy 專案結構與設定

本文將探討使用 SQLAlchemy 建構 Flask 和 FastAPI 後端應用程式的專案結構和相關設定。我們將分析兩個不同的實作範例，並詳細說明每個部分的實作細節和技術考量。

Flask 後端實作

應用程式初始化

在 Flask 版本中，create_app 函式負責初始化應用程式例項：

def create_app():
    app = Flask(__name__)
    app.config.from_object('config')
    db.init_app(app)
    from .routes import bp
    app.register_blueprint(bp)
    return app

內容解密：

1. Flask(__name__)：使用目前模組名稱初始化 Flask 應用程式例項
2. app.config.from_object('config')：從 config 模組載入應用程式組態
3. db.init_app(app)：將 SQLAlchemy 資料函式庫例項與 Flask 應用程式繫結
4. app.register_blueprint(bp)：註冊應用程式的路由藍圖

啟動設定

Flask 應用程式的啟動組態包括兩個主要場景：

1. 使用 flask run 命令啟動開發伺服器：

   • .flaskenv 檔案組態 FLASK_APP=main.py 和 FLASK_DEBUG=true

2. 使用 Gunicorn 啟動生產環境伺服器：

   (venv) $ gunicorn -b :5000 main:app
   

環境變數載入

在 retrofun/__init__.py 中，使用 load_dotenv() 載入 .env 檔案中的環境變數：

from dotenv import load_dotenv
load_dotenv()

內容解密：

• 確保在不同 WSGI 伺服器環境下都能正確載入環境變數
• .env 檔案用於儲存敏感組態，如資料函式庫連線字串

資料函式庫遷移設定

使用 Alchemical 建立資料函式庫遷移倉函式庫：

(venv) $ python -m alchemical.alembic.cli init migrations

修改 alembic.ini 中的 alchemical_db 設定：

alchemical_db = retrofun.models:db

內容解密：

1. 使用 Alchemical 的 CLI 命令建立相容的遷移倉函式庫
2. 設定 alchemical_db 指向正確的資料函式庫例項定義

FastAPI 後端實作

應用程式初始化

FastAPI 版本的應用程式初始化在 retrofun/app.py 中進行：

from fastapi import FastAPI
from .router import router

app = FastAPI()
app.include_router(router)

內容解密：

1. 建立 FastAPI 應用程式例項
2. 使用 include_router 方法註冊 API 路由

資料函式庫設定

在 retrofun/models.py 中定義非同步資料函式庫例項：

import os
from alchemical.aio import Alchemical

db = Alchemical(os.environ['DATABASE_URL'])

內容解密：

1. 使用非同步版本的 Alchemical 建立資料函式庫連線
2. 從環境變數 DATABASE_URL 中讀取資料函式庫連線字串

共通組態與最佳實踐

1. 依賴管理：使用 requirements.txt 管理專案依賴

   (venv) $ pip install -r requirements.txt
   

2. 環境變數管理：

   • 提供 .env.template 作為環境變數設定的參考範本
   • 真正的 .env 檔案不納入版本控制，根據實際佈署環境進行組態

3. 資料函式庫相容性：

   • 確保資料函式庫 URL 使用正確的非同步資料函式庫驅動程式
   • 可以重複使用之前建立的資料函式庫

資料函式庫查詢實戰：SQLAlchemy 深度應用解析

在現代軟體開發中，資料函式庫的操作與管理是不可或缺的一環。SQLAlchemy 作為 Python 中最流行的 ORM（Object-Relational Mapping）工具之一，提供了強大且靈活的資料函式庫操作能力。本文將探討 SQLAlchemy 的進階應用，透過具體範例展示如何高效地進行複雜的資料函式庫查詢。

資料函式庫查詢基礎

在開始之前，我們先了解 SQLAlchemy 的基本查詢結構。以下是一個簡單的查詢範例：

>>> q = select(Product).where(Product.year == 1983).order_by(Product.name).limit(3)
>>> session.scalars(q).all()
[Product(17, "Apple IIe"), Product(85, "Aquarius"), Product(26, "Atari 1200XL")]

內容解密：

1. select(Product)：建立一個查詢物件，目標是 Product 表。
2. .where(Product.year == 1983)：篩選條件，僅選擇 year 為 1983 的產品。
3. .order_by(Product.name)：按產品名稱排序結果。
4. .limit(3)：限制結果數量為前三筆。
5. session.scalars(q).all()：執行查詢並傳回所有結果。

進階查詢技巧

1. 多條件查詢

查詢使用特定 CPU 的產品，例如含有 “Z80” 的 CPU：

>>> q = select(Product).where(Product.cpu.like('%Z80%'))
>>> session.scalars(q).all()
[ ... 63 results ... ]

內容解密：

• Product.cpu.like('%Z80%')：使用模糊匹配查詢 CPU 欄位中包含 “Z80” 的產品。
• 此查詢適用於尋找使用 Z80 CPU 或其相容產品。

2. 複合條件查詢

查詢在 1990 年前生產、使用 “Z80” 或 “6502” CPU 的產品，並按名稱排序：

>>> q = (select(Product)
     .where(or_(Product.cpu.like('%Z80%'), Product.cpu.like('%6502%')), Product.year < 1990)
     .order_by(Product.name))
>>> session.scalars(q).all()
[ ... 90 results ... ]

內容解密：

1. or_()：組合多個條件，只要滿足其中之一即可。
2. Product.year < 1990：進一步篩選生產年份在 1990 年前的產品。
3. 結合多條件查詢與排序功能，實作複雜的資料篩選。

製造商相關查詢

1. 不同條件下的製造商查詢

查詢在 1980 年代生產產品的製造商：

>>> q = (select(Product.manufacturer).where(Product.year.between(1980, 1989)).distinct())
>>> session.scalars(q).all()
[ ... 65 results ... ]

內容解密：

• .distinct()：確保結果中不包含重複的製造商名稱。
• Product.year.between(1980, 1989)：篩選特定年份範圍內的產品。

2. 名稱符合特定模式的製造商

查詢名稱以 “T” 開頭的製造商：

>>> q = (select(Product.manufacturer)
     .where(Product.manufacturer.like('T%'))
     .order_by(Product.manufacturer)
     .distinct())
>>> session.scalars(q).all()
['Tangerine Computer Systems', 'Technosys', 'Tesla', 'Texas Instruments',
'Thomson', 'Timex Sinclair', 'Tomy', 'Tsinghua University']

內容解密：

1. like('T%')：匹配以 “T” 開頭的字串。
2. 結合排序與去重功能，提供清晰的結果列表。

統計與聚合查詢

1. 各年份產品數量統計

查詢每年生產的產品數量，並按數量降序排列：

>>> product_count = func.count(Product.id).label(None)
>>> q = (select(Product.year, product_count)
     .group_by(Product.year)
     .order_by(product_count.desc()))
>>> session.execute(q).all()
[(1983, 24), (1984, 21), (1985, 21), (1982, 17), (1986, 11), (1980, 10),
(1979, 9), (1977, 7), (1987, 6), (1981, 6), (1990, 5), (1989, 4), (1988, 2),
(1978, 2), (1969, 1), (1995, 1), (1992, 1), (1991, 1)]

內容解密：

1. func.count(Product.id)：計算每年的產品數量。
2. .group_by(Product.year)：按年份分組統計結果。
3. .order_by(product_count.desc())：按產品數量降序排序。

多表關聯查詢

1. 聯合查詢特定製造商的產品

查詢 IBM 和 Texas Instruments 生產的產品：

>>> q = (select(Product)
     .join(Product.manufacturer)
     .where(Manufacturer.name.in_(['IBM', 'Texas Instruments'])))
>>> session.scalars(q).all()
[Product(75, "PCjr"), Product(76, "IBM PS/1"), Product(132, "TI-99/4"),
Product(133, "TI-99/4A")]

內容解密：

1. .join(Product.manufacturer)：透過外部索引鍵關聯 Product 和 Manufacturer 表。
2. Manufacturer.name.in_()：篩選特定製造商名稱的產品。

Plantuml 圖示說明

@startuml
skinparam backgroundColor #FEFEFE
skinparam defaultTextAlignment center
skinparam rectangleBackgroundColor #F5F5F5
skinparam rectangleBorderColor #333333
skinparam arrowColor #333333

title Plantuml 圖示說明

rectangle "Foreign Key" as node1
rectangle "Many-to-Many" as node2
rectangle "One-to-Many" as node3

node1 --> node2
node2 --> node3

@enduml

此圖示展示了 Product、Manufacturer 和 Country 三個表之間的關聯結構。其中，Product 與 Manufacturer 為一對多關係，而 Product 與 Country 為多對多關係。

圖示解密：

1. Product Table 與 Manufacturer Table 之間透過外部索引鍵建立一對多關係。
2. Product Table 與 Country Table 之間透過多對多關係表實作關聯，用於表示產品在不同國家的生產情況。

SQLAlchemy 查詢範例解析與應用

SQLAlchemy 是 Python 中一個流行的 SQL 工具包和物件關係對映（ORM）系統，提供了一種靈活且高效的方式來與資料函式庫互動。本文將探討 SQLAlchemy 的查詢功能，並透過具體的範例來展示如何使用 SQLAlchemy 進行複雜的資料查詢。

國家產品數量排名

首先，我們來看一個查詢國家產品數量的例子。假設我們要找出產品數量最多的前 5 個國家，如果有並列情況，則按國家名稱的字母順序排列。

程式碼範例

product_count = func.count(Product.id).label(None)
q = (select(Country, product_count)
     .join(Country.products)
     .group_by(Country)
     .order_by(product_count.desc(), Country.name)
     .limit(5))
session.execute(q).all()

內容解密

1. func.count(Product.id).label(None)：計算每個國家的產品數量。
2. select(Country, product_count)：選擇要查詢的欄位，包括國家資訊和產品數量。
3. join(Country.products)：將 Country 表與 Product 表進行連線，根據國家與產品的關聯。
4. group_by(Country)：按國家進行分組，以便計算每個國家的產品數量。
5. order_by(product_count.desc(), Country.name)：先按產品數量降序排序，若數量相同，則按國家名稱升序排序。
6. limit(5)：限制結果只傳回前 5 個國家。

特定國家中的製造商產品數量

接下來，我們查詢在特定國家（如英國或美國）中，產品數量超過 3 的製造商。

程式碼範例

product_count = func.count(Product.id.distinct()).label(None)
q = (select(Manufacturer, product_count)
     .join(Manufacturer.products)
     .join(Product.countries)
     .where(Country.name.in_(['UK', 'USA']))
     .group_by(Manufacturer)
     .having(product_count > 3))
session.execute(q).all()

內容解密

1. func.count(Product.id.distinct())：計算不同產品的數量，避免重複計算同一產品。
2. where(Country.name.in_(['UK', 'USA']))：篩選出只在英國或美國銷售的產品。
3. having(product_count > 3)：篩選出產品數量大於 3 的製造商。

同時在多個國家銷售的產品

查詢同時在英國和美國銷售的產品。

程式碼範例

q = (select(Product)
     .join(Product.countries)
     .where(Country.name.in_(['UK', 'USA']))
     .group_by(Product)
     .having(func.count(Country.id) > 1))
session.execute(q).all()

內容解密

1. where(Country.name.in_(['UK', 'USA']))：篩選出在英國或美國銷售的產品。
2. having(func.count(Country.id) > 1)：確保產品至少與兩個不同的國家相關聯，即同時在英國和美國銷售。

訂單金額超過 $300 的查詢

查詢訂單金額超過 $300 的訂單，並按金額降序排列。

程式碼範例

order_total = func.sum(OrderItem.unit_price * OrderItem.quantity).label(None)
q = (select(Order, order_total)
     .join(Order.order_items)
     .group_by(Order)
     .having(order_total > 300)
     .order_by(order_total.desc()))
session.execute(q).all()

內容解密

1. func.sum(OrderItem.unit_price * OrderItem.quantity)：計算每個訂單的總金額。
2. having(order_total > 300)：篩選出訂單總金額超過 $300 的訂單。