SQLAlchemy資料函式庫查詢分頁與關係

SQLAlchemy 提供了豐富的 API 來處理資料函式庫查詢，其中 distinct()、group_by() 和 having() 方法分別用於去除重複結果、分組資料以及篩選分組後的結果。分頁技術對於處理大量資料至關重要，limit() 和 offset() 方法提供了一種簡便的實作方式，但在大資料集下效率較低。相對地，使用 where() 子句進行分頁雖然實作較複雜，但效能更佳，尤其適用於資料頻繁變動的場景。為提升查詢效能，應根據查詢條件為相關欄位建立索引。同時，善用主鍵、唯一約束等資料函式庫約束，可以有效維護資料的完整性。此外，本文也介紹了一對多關係的資料函式庫設計，透過將重複出現的製造商資訊分離至獨立表格，不僅能減少資料冗餘，也提升了資料一致性和可維護性，是資料函式庫設計的最佳實踐之一。

資料函式庫查詢中的結果處理與分頁技術

在進行資料函式庫查詢時，經常需要處理重複結果、結果的分組、以及分頁等問題。SQLAlchemy 提供了一系列的方法來處理這些問題，包括 distinct()、group_by()、having()、limit() 和 offset() 等。

處理重複結果

當資料函式庫查詢可能傳回重複結果時，可以使用 distinct() 方法來合併相同的結果。例如，若要取得資料函式庫中所有不同的製造商，可以使用以下查詢：

>>> q = select(Product.manufacturer).order_by(Product.manufacturer).distinct()
>>> session.scalars(q).all()
['Acorn Computers Ltd', 'AGAT', ..., 'West Computer AS']

內容解密：

1. select(Product.manufacturer)：選擇 Product 表中的 manufacturer 欄位。
2. order_by(Product.manufacturer)：按 manufacturer 欄位進行排序。
3. distinct()：合併相同的結果，確保傳回的製造商列表是唯一的。

若要計算唯一的製造商數量，可以使用 count() 聚合函式並在其中呼叫 distinct() 方法：

>>> q = select(func.count(Product.manufacturer.distinct()))
>>> session.scalar(q)
76

內容解密：

1. func.count(Product.manufacturer.distinct())：計算 manufacturer 欄位中不同值的數量。
2. session.scalar(q)：執行查詢並傳回單一值，即不同的製造商數量。

使用 group_by() 分組結果

雖然 distinct() 可以合併相同的結果，但它的功能有限。當需要對結果進行更複雜的分組和聚合操作時，可以使用 group_by() 方法。例如，若要取得每個製造商的第一年和最後一年活動年份，以及他們生產的型號數量，可以使用以下查詢：

>>> q = (select(
Product.manufacturer,
func.min(Product.year),
func.max(Product.year),
func.count()
)
.group_by(Product.manufacturer)
.order_by(Product.manufacturer))
>>> session.execute(q).all()
[('Acorn Computers Ltd', 1980, 1995, 6), ..., ('West Computer AS', 1984, 1984, 1)]

內容解密：

1. group_by(Product.manufacturer)：按 manufacturer 欄位對結果進行分組。
2. func.min(Product.year) 和 func.max(Product.year)：計算每個分組中的最小和最大年份。
3. func.count()：計算每個分組中的行數，即每個製造商生產的型號數量。

對分組結果進行過濾

可以使用 having() 方法對分組後的結果進行過濾。例如，若要取得生產了五個或以上型號的製造商，可以使用以下查詢：

>>> q = (select(
Product.manufacturer,
func.count()
)
.group_by(Product.manufacturer)
.having(func.count() >= 5)
.order_by(Product.manufacturer))
>>> session.execute(q).all()
[('Acorn Computers Ltd', 6), ('Amstrad', 7), ..., ('Timex Sinclair', 6)]

內容解密：

1. having(func.count() >= 5)：對分組後的結果進行過濾，只保留那些計數大於或等於 5 的分組。

為了避免重複計算，可以使用 label() 方法為聚合函式賦予一個標籤，然後在查詢的其他部分使用這個標籤：

>>> num_products = func.count().label('num_products')
>>> q = (select(
Product.manufacturer,
num_products
)
.group_by(Product.manufacturer)
.having(num_products >= 5)
.order_by(Product.manufacturer))

內容解密：

1. func.count().label('num_products')：為計數函式賦予一個標籤 num_products。
2. 在 having() 和 select() 中使用 num_products，避免了重複計算。

分頁技術

對於傳回大量結果的查詢，使用分頁技術可以提高效能和使用者經驗。可以使用 limit() 和 offset() 方法來實作分頁。

>>> q = select(Product).order_by(Product.name).limit(3)
>>> session.scalars(q).all()
[Product(10, "464 Plus"), Product(11, "6128 Plus"), Product(6, "A7000")]

內容解密：

1. limit(3)：限制查詢傳回的結果數量為 3。

要實作分頁，可以結合使用 limit() 和 offset() 方法。例如，要取得第二頁的結果（假設每頁大小為 3），可以使用以下查詢：

>>> q = select(Product).order_by(Product.name).limit(3).offset(3)
>>> session.scalars(q).all()

內容解密：

1. offset(3)：設定查詢的起始偏移量為 3，即跳過前 3 個結果。

透過結合使用這些技術，可以有效地處理和呈現資料函式庫查詢結果，提高應用程式的效能和使用者經驗。

資料函式庫查詢與分頁實務

在處理資料函式庫查詢時，分頁是一個常見的需求。SQLAlchemy 提供了多種方法來實作分頁，其中最直接的方法是使用 limit() 和 offset()。以下是一個簡單的例子：

使用 limit() 和 offset() 進行分頁

q = select(Product).order_by(Product.name).limit(3).offset(3)
session.scalars(q).all()

內容解密：

1. select(Product)：建立一個查詢 Product 模型的查詢物件。
2. order_by(Product.name)：根據產品名稱排序查詢結果。
3. limit(3)：限制查詢結果最多傳回 3 筆資料。
4. offset(3)：跳過前 3 筆資料，從第 4 筆開始傳回結果。

然而，使用 offset() 進行分頁存在一些問題。首先，在大多數資料函式庫中，offset() 的實作效率並不高。此外，當資料集頻繁變化時，使用 offset() 可能會導致結果混亂。例如，如果在使用者檢視第二頁結果後新增了一個產品，那麼當使用者請求第三頁時，所有產品的位置都會向下移動一位，導致「Acorn Archimedes」再次出現在第一個位置。

使用 where() 子句進行分頁

一個更穩健的方法是使用最後傳回的專案作為參考點。請求第二頁結果的查詢如下：

q = select(Product).order_by(Product.name).where(Product.name > 'A7000').limit(3)

內容解密：

1. where(Product.name > 'A7000')：篩選出名稱大於 ‘A7000’ 的產品，從而實作分頁。
2. 這種方法的優點是，即使資料集發生變化，也不會重複或遺漏任何結果。

然而，這種方法在向後導航時會稍微複雜一些。從第二頁傳回第一頁的查詢如下：

q = (select(Product)
     .order_by(Product.name.desc())
     .where(Product.name < 'Aamber Pegasus')
     .limit(3))
session.scalars(q).all()

內容解密：

1. order_by(Product.name.desc())：反轉排序順序，以便參考第二頁之前的產品。
2. where(Product.name < 'Aamber Pegasus')：篩選出名稱小於 ‘Aamber Pegasus’ 的產品。

選擇合適的分頁方法

選擇使用 offset() 還是 where() 子句進行分頁取決於具體情況。offset() 方法實作簡單，允許使用者隨機請求任何頁碼。然而，如果資料集很少變化，這可能是最佳選擇。

使用 where() 子句的方法非常穩健，不會重複或遺漏任何結果，但實作起來更為複雜。此外，這種方法不允許隨機跳轉，使用者只能向前或向後翻頁。

根據主鍵取得元素

根據給定的主鍵值檢索資料函式庫表中的元素是一種非常有用的查詢。這種查詢最多傳回一個結果，因為主鍵是唯一的。可以使用以下查詢來檢索主鍵為 23 的產品：

q = select(Product).where(Product.id == 23)
session.scalar(q)

內容解密：

1. select(Product)：建立一個查詢 Product 模型的查詢物件。
2. where(Product.id == 23)：篩選出主鍵為 23 的產品。

SQLAlchemy 為這種常見的查詢提供了捷徑，即使用 session 物件的 get() 方法：

session.get(Product, 23)

內容解密：

1. session.get(Product, 23)：直接根據主鍵值 23 檢索 Product 模型對應的例項。

索引

資料函式庫使用多種演算法來導航資料，並根據查詢需求選擇最有效的演算法。其中一種始終可用的搜尋演算法是表掃描。表掃描操作會順序評估查詢篩選條件中的所有列，這種方法效率不高。

作為資料函式庫設計者，您的任務是研究應用程式發出的查詢，並確保資料被正確索引，以支援更先進的搜尋選項。將列標記為索引後，資料函式庫將為該列的資料維護二元樹結構，從而實作高效的搜尋和排序。

在範例查詢中，對 products 表的以下列進行了搜尋：

• id
• name
• manufacturer
• year

其中，id 列是表的主鍵，資料函式庫會自動對其建立索引。其他列目前未建立索引，這意味著在查詢中使用這些列時，需要進行表掃描。

索引結構

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

title 索引

rectangle "主鍵" as node1
rectangle "其他列" as node2
rectangle "提高搜尋效率" as node3

node1 --> node2
node2 --> node3

@enduml

此圖示展示了資料表與索引之間的關係。主鍵自動建立索引，而其他列可以手動建立索引，以提高搜尋效率。

資料函式庫設計最佳實踐：索引與約束

在設計資料函式庫時，適當地使用索引（Index）與約束（Constraint）對於提升查詢效能和資料完整性至關重要。本文將探討如何在SQLAlchemy中有效地使用這些功能。

索引的使用

索引是一種資料結構，能夠加速資料函式庫的查詢操作。適當地建立索引可以顯著提高查詢效能。然而，並非所有欄位都需要建立索引。對於頻繁用於查詢條件的欄位，建立索引是合理的。

在前面的範例中，我們觀察到name、manufacturer和year欄位經常被用於查詢條件。因此，為這些欄位建立索引是有益的。

class Product(Model):
    __tablename__ = 'products'
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(64), index=True)
    manufacturer: Mapped[str] = mapped_column(String(64), index=True)
    year: Mapped[int] = mapped_column(index=True)
    country: Mapped[str] = mapped_column(String(32))
    cpu: Mapped[str] = mapped_column(String(32))

內容解密：

1. index=True：在name、manufacturer和year欄位上建立索引，以加速查詢。
2. 選擇性建立索引：只對頻繁用於查詢條件的欄位建立索引，以避免不必要的效能開銷。

約束的使用

約束用於確保資料的完整性和一致性。常見的約束包括主鍵（PRIMARY KEY）、唯一約束（UNIQUE）和非空約束（NOT NULL）。

• 主鍵約束：確保每筆記錄的唯一性。在Product模型中，id欄位被定義為主鍵。
• 唯一約束：確保欄位中的值是唯一的。在Product模型中，可以為name欄位新增唯一約束，以避免重複的產品名稱。

class Product(Model):
    __tablename__ = 'products'
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(64), index=True, unique=True)
    manufacturer: Mapped[str] = mapped_column(String(64), index=True)
    year: Mapped[int] = mapped_column(index=True)
    country: Mapped[Optional[str]] = mapped_column(String(32))
    cpu: Mapped[Optional[str]] = mapped_column(String(32))

內容解密：

1. unique=True：在name欄位上新增唯一約束，確保產品名稱的唯一性。
2. Mapped[Optional[str]]：將country和cpu欄位定義為可選，允許NULL值。

資料刪除

使用SQLAlchemy刪除資料涉及呼叫session的delete()方法。

>>> p = session.get(Product, 23)
>>> session.delete(p)
>>> session.commit()

內容解密：

1. session.get(Product, 23)：檢索ID為23的產品。
2. session.delete(p)：將檢索到的產品標記為刪除。
3. session.commit()：提交變更，永久刪除產品。

練習

為了鞏固所學知識，請嘗試完成以下練習：

1. 查詢1983年生產的前三個按字母順序排列的產品。
2. 查詢使用"Z80" CPU或其相容品的產品。
3. 查詢在1990年之前生產的使用"Z80"或"6502" CPU或其相容品的產品，按名稱字母順序排序。
4. 查詢在1980年代生產產品的製造商。
5. 查詢名稱以字母"T"開頭的製造商，按字母順序排序。
6. 查詢克羅埃西亞生產產品的第一年和最後一年，以及生產的產品數量。
7. 查詢每年生產的產品數量，按產品數量降序排列。
8. 查詢美國製造商的數量。

這些練習將幫助您更好地理解如何使用SQLAlchemy進行資料函式庫操作和查詢。

一對多關係的資料函式庫設計與實作

在前一章中，我們學習瞭如何在 products 表格上執行各種查詢。值得注意的是，其中一些查詢的設計目的是為了取得產品製造商而非產品本身，這需要透過分組結果來消除重複資料。

在許多情況下，分組是一種很好的解決方案，尤其是在使用聚合函式計算每個群組的資訊時，這些資訊並非直接儲存在資料函式庫中。然而，在某些情況下，當將次要資料儲存在與主要實體相同的表格中時，產生的重複資料可能會成為問題，因為表格可能會變得比需要的更大，而且重複資料中的拼寫差異可能會導致錯誤的分組結果。

在本章中，您將學習如何透過建立多個相關的表格來消除重複，使用關聯式資料函式庫設計中的基本模式之一：一對多關係。

何時使用關係

您可能會想知道是什麼讓 Product 模型中的 manufacturer 列成為一個適合移至其自己的獨立模型類別和資料函式庫表格的候選者。對此沒有明確的答案，只是某些實體在直覺上是第一類別的，而且毫無疑問它們應該是獨立的，而其他的則不容易被識別，可能需要對資料函式庫設計進行一次或多次迭代，才能清楚地看到建立獨立表格的好處。

例如，如果 RetroFun 資料函式庫需要擴充套件以儲存來自客戶的訂單，沒有人會質疑這些訂單應該儲存在自己的表格中。但是每個訂單都需要客戶資訊。客戶應該儲存在自己的表格中還是作為訂單的額外列？對於不太可能重複訂購的企業來說，您可能會考慮將客戶詳細資訊直接與訂單一起儲存的更簡單方法，但對於大多數企業來說，將客戶資訊單獨儲存可能更方便。資料函式庫設計決策並非絕對，應根據應用程式的需求進行。

作為經驗法則，當您發現資訊正在被重複時，您應該考慮透過新增新表格和關係來消除這種重複。就 manufacturer 列而言，獨立的表格意味著每個公司名稱只會存在一次，因此永遠不會有拼寫錯誤影響產品分組的風險。將製造商儲存在自己的表格中還可以重新命名公司名稱或新增其他詳細資訊，如公司地址和電話號碼，所有這些更改只需進行一次，並立即對所有相關產品生效。

一對多關係實作

關聯式資料函式庫透過關係在不同表格中儲存的實體之間建立連結。主要有兩種型別的關係：一對多和多對多。關聯式資料函式庫文獻還提到了另外兩種關係型別：多對一和一對一，它們是一對多型別的特殊形式。

一對多關係描述了兩個表格 A 和 B 之間的關係，使得 A 中的一個條目可以連結到 B 中的任意數量的條目，但 B 中的每個條目最多隻能連結到 A 中的一個條目。在這種情況下，表格 A 是關係中的「一」，而表格 B 是「多」。

這種模式適用於電腦製造商與其電腦產品之間的關係。製造商可以生產許多電腦型號，而這些電腦型號中的每一個都只由一家製造商生產。因此，製造商是「一」，而產品是「多」。

定義關係的第一步是為所涉及的兩個實體建立資料函式庫表格（或在使用 SQLAlchemy ORM 時建立模型）。目前狀態下的資料函式庫有一個 products 表格，現在需要一個 manufacturers 表格。在 models.py 的底部新增一個 Manufacturer 模型來表示製造商：

Listing 10 models.py: Manufacturers 模型

class Manufacturer(Model):
    __tablename__ = 'manufacturers'
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(64), index=True, unique=True)

內容解密：

1. __tablename__ = 'manufacturers'：定義了資料函式庫中對應的表格名稱為 manufacturers。
2. id: Mapped[int] = mapped_column(primary_key=True)：定義了一個名為 id 的整數欄位，並將其設定為主鍵，用於唯一標識每個製造商。
3. name: Mapped[str] = mapped_column(String(64), index=True, unique=True)：定義了一個名為 name 的字串欄位，用於儲存製造商的名稱。該欄位被設定為索引，並且是唯一的，以確保每個製造商名稱只出現一次。

透過建立 manufacturers 表格，我們為建立一對多關係奠定了基礎，使得每個製造商可以與多個產品相關聯，而每個產品只與一個製造商相關聯。這種設計不僅消除了重複資料，還提高了資料的一致性和可維護性。