SQLAlchemy資料函式庫關聯設計與實作

– 10 分鐘閱讀

在資料函式庫應用開發中，有效管理資料函式庫關聯至關重要。本文將探討如何使用 SQLAlchemy 建立和管理資料函式庫關聯，特別是一對多關聯，並示範如何在 Python 應用程式中使用這些關聯進行資料操作。文章涵蓋了從模型設計、關聯建立到查詢最佳化和刪除操作的完整流程，並提供實際程式碼範例和詳細說明，幫助讀者理解 SQLAlchemy 中關聯操作的最佳實踐。同時，文章也討論了不同關聯載入策略的優缺點，以及如何根據實際需求選擇合適的策略，以提升應用程式效能。

資料函式庫關聯性設計與實作

在資料函式庫設計中，關聯性是至關重要的概念，特別是在將資料拆分到多個表格時。本文將探討如何在 SQLAlchemy 中實作一對多（one-to-many）的關聯性，並介紹相關的模型設計和實作細節。

模型設計與關聯性

首先，考慮一個簡單的例子：產品（Product）和製造商（Manufacturer）。在原始設計中，製造商名稱直接儲存在產品表格中。然而，這種設計可能導致資料重複和不一致性。因此，我們將製造商資訊拆分到一個獨立的表格中。

製造商模型

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

    def __repr__(self):
        return f'Manufacturer({self.id}, "{self.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_id: Mapped[int] = mapped_column(ForeignKey('manufacturers.id'), 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))

    def __repr__(self):
        return f'Product({self.id}, "{self.name}")'

關聯性實作

為了簡化關聯性的操作，SQLAlchemy 提供了 relationship 功能。透過在模型中加入 relationship 屬性，可以實作模型的關聯導航。

class Product(Model):
    # ...
    manufacturer: Mapped['Manufacturer'] = relationship(back_populates='products')
    # ...

class Manufacturer(Model):
    # ...
    products: Mapped[list['Product']] = relationship(back_populates='manufacturer')
    # ...

內容解密：

  1. manufacturer_id 外部索引鍵：在 Product 模型中，manufacturer_id 是一個外部索引鍵，參考 manufacturers 表格的 id 欄位。這建立了產品與製造商之間的關聯。
  2. relationship 屬性：manufacturer 和 products 是 relationship 屬性，分別代表產品與製造商之間的關聯。這使得我們可以透過產品直接存取其製造商，或是透過製造商存取其所有相關產品。
  3. back_populates 引數：這個引數用於指定對應的另一側關聯屬性名稱，讓 SQLAlchemy 能夠理解這兩個屬性代表同一關聯的兩側。

資料匯入指令碼更新

隨著模型變更，資料匯入指令碼也需要更新，以支援將製造商資料匯入到獨立的表格中。

def main():
    Model.metadata.drop_all(engine)  # 警告：這將刪除所有資料！
    # ... 資料匯入邏輯 ...

內容解密：

  1. 更新模型參照：匯入指令碼現在需要參照更新後的 Product 和 Manufacturer 模型。
  2. 資料匯入邏輯：指令碼需要更新以支援將製造商資料匯入到 manufacturers 表格，並正確建立產品與製造商之間的關聯。

資料函式庫關聯性與查詢最佳化

在前面的章節中，我們已經瞭解如何使用 SQLAlchemy 建立一對多（One-To-Many）的關聯性。現在，讓我們進一步探討如何有效地使用這些關聯性進行資料查詢。

一對多關聯性的建立與使用

首先，回顧一下如何在 SQLAlchemy 中建立一對多關聯性。在我們的例子中，一個製造商（Manufacturer）可以生產多個產品（Product），因此 Manufacturer 與 Product 之間存在一對多的關係。

class Manufacturer(Base):
    __tablename__ = 'manufacturers'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    products = relationship('Product', back_populates='manufacturer')

class Product(Base):
    __tablename__ = 'products'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    manufacturer_id = Column(Integer, ForeignKey('manufacturers.id'))
    manufacturer = relationship('Manufacturer', back_populates='products')

內容解密：

  1. Manufacturer 類別：定義了製造商的資料表，包含 id 和 name 欄位。products 屬性透過 relationship 建立了一對多的關聯性，代表一個製造商可以有多個產品。
  2. Product 類別：定義了產品的資料表，包含 id、name 和 manufacturer_id 欄位。manufacturer_id 是外部索引鍵，參考 manufacturers 資料表的 id。manufacturer 屬性同樣透過 relationship 建立了多對一的關聯性，代表多個產品可以屬於同一個製造商。

資料匯入與關聯性操作

在匯入 CSV 資料時，我們需要正確處理製造商與產品之間的關聯性。

with open('products.csv') as f:
    reader = csv.DictReader(f)
    all_manufacturers = {}
    for row in reader:
        row['year'] = int(row['year'])
        manufacturer_name = row.pop('manufacturer')
        p = Product(**row)
        if manufacturer_name not in all_manufacturers:
            m = Manufacturer(name=manufacturer_name)
            session.add(m)
            all_manufacturers[manufacturer_name] = m
        all_manufacturers[manufacturer_name].products.append(p)

內容解密：

  1. 讀取 CSV 檔案：使用 csv.DictReader 讀取 products.csv，將每一行轉換為字典。
  2. 處理製造商與產品關聯：檢查製造商是否已存在於 all_manufacturers 字典中。如果不存在，則建立新的 Manufacturer 物件並加入到 session 中。然後，將產品新增到對應製造商的 products 列表中。
  3. products 列表操作：透過 all_manufacturers[manufacturer_name].products.append(p) 將新產品新增到相應的製造商下。這種操作類別似於列表操作，但實際上是透過 SQLAlchemy 的關聯性管理來更新外部索引鍵。

查詢與導航

一旦資料匯入完成，我們就可以透過 SQLAlchemy 的查詢功能來檢索和導航資料。

# 載入 "ZX Spectrum" 產品
p = session.scalar(select(Product).where(Product.name == 'ZX Spectrum'))

# 檢視該產品的製造商
print(p.manufacturer.name)  # 輸出: Sinclair Research

# 檢視該製造商的其他產品
for product in p.manufacturer.products:
    print(product.name)

內容解密：

  1. 查詢特定產品：使用 session.scalar 和 select 方法查詢名稱為 “ZX Spectrum” 的產品。
  2. 存取製造商資訊：透過 p.manufacturer 取得該產品的製造商例項，並進一步存取其 name 屬性。
  3. 導航到其他產品：透過 p.manufacturer.products 取得該製造商的所有產品，並遍歷輸出每個產品的名稱。

SQLAlchemy 中的 Join 操作與關聯式載入

在前面的章節中，我們看到了如何使用 SQLAlchemy 進行簡單的查詢操作。然而，當資料分散在多個表格中時，我們需要使用 Join 操作來結合這些資料。在本章節中，我們將探討 SQLAlchemy 中的 Join 操作以及關聯式載入的相關議題。

使用 Join 操作結合多個表格

當我們需要從多個表格中取得資料時，Join 操作就變得非常重要。SQLAlchemy 提供了一種簡潔的方式來執行 Join 操作。以下是一個例子：

q = select(Product.name, Manufacturer.name).join(Product.manufacturer)
session.execute(q).all()

內容解密：

  1. select(Product.name, Manufacturer.name)：選擇 Product 表格中的 name 欄位和 Manufacturer 表格中的 name 欄位。
  2. .join(Product.manufacturer)：根據 Product 表格中的 manufacturer 關聯屬性進行 Join 操作。
  3. session.execute(q).all()：執行查詢並取得所有結果。

這個查詢結合了 Product 和 Manufacturer 兩個表格，並傳回產品名稱和製造商名稱的列表。

關聯式載入：Lazy vs. Eager Relationships

當我們存取模型類別中的關聯屬性時，SQLAlchemy 會自動執行相關的查詢操作。預設情況下，SQLAlchemy 使用 Lazy Loading 的方式載入關聯資料。

內容解密：

  1. Lazy Loading：當存取關聯屬性時，SQLAlchemy 才會執行查詢操作載入相關資料。
  2. Eager Loading：可以在查詢時一併載入關聯資料，避免多次查詢資料函式庫。

要觀察 SQLAlchemy 的查詢行為，可以在建立 Engine 物件時設定 echo=True：

engine = create_engine(os.environ['DATABASE_URL'], echo=True)

這樣可以在終端機上看到 SQLAlchemy 執行的 SQL 陳述式。

觀察關聯式載入的行為

當我們存取關聯屬性時，SQLAlchemy 會自動執行相關的查詢操作。例如：

m = session.scalar(select(Manufacturer).where(Manufacturer.name == 'Texas Instruments'))
m.products

內容解密：

  1. m = session.scalar(...)：查詢名稱為 “Texas Instruments” 的製造商。
  2. m.products：存取 products 關聯屬性，觸發 SQLAlchemy 執行查詢操作載入相關產品資料。

此時，可以在終端機上看到 SQLAlchemy 執行的 SQL 陳述式，瞭解其查詢行為。

SQLAlchemy 中的關聯載入：提升資料函式庫查詢效率

SQLAlchemy 是一個強大的 ORM（Object-Relational Mapping）工具，能夠簡化資料函式庫操作並提供高效的查詢機制。在使用 SQLAlchemy 時，瞭解如何有效地載入關聯資料至關重要。本文將探討 SQLAlchemy 中的關聯載入策略，特別是「lazy」載入和「eager」載入的區別，以及如何根據實際需求選擇適當的載入方式。

預設的「Lazy」載入

預設情況下，SQLAlchemy 使用「lazy」載入策略來處理模型之間的關聯。這意味著當你存取一個關聯屬性時，SQLAlchemy 會發出一個額外的資料函式庫查詢來檢索相關資料。例如：

>>> p = session.get(Product, 127)
>>> p.manufacturer

在第一次存取 p.manufacturer 時，SQLAlchemy 會執行一個額外的查詢來取得製造商的資料。這種方式的優點是它避免了不必要的資料函式庫查詢，但缺點是如果不小心，可能會導致大量的隱式查詢，從而影回應用程式的效能。

內容解密：

  1. session.get(Product, 127)：從資料函式庫中檢索 id 為 127 的產品。
  2. p.manufacturer：存取產品的製造商屬性，觸發對製造商資料的查詢。

「Lazy」載入的陷阱

雖然「lazy」載入在某些情況下很方便，但它也可能導致效能問題。考慮以下例子：

>>> q = select(Product)
>>> for p in session.scalars(q):
print(p.name, p.manufacturer.name)

這個迴圈看似簡單，但實際上它會導致大量的資料函式庫查詢。對於每個產品，SQLAlchemy 都會發出一個額外的查詢來取得其製造商的資料。如果有大量的產品，這將導致嚴重的效能問題。

內容解密：

  1. select(Product)：建立一個查詢來檢索所有產品。
  2. session.scalars(q)：執行查詢並傳回結果。
  3. p.name, p.manufacturer.name：對於每個產品，存取其名稱和製造商名稱。存取 p.manufacturer.name 會觸發對製造商資料的查詢。

使用「Eager」載入最佳化查詢

為了避免「lazy」載入帶來的效能問題，SQLAlchemy 提供了「eager」載入策略。其中一種「eager」載入方式是使用 joinedload，它透過在初始查詢中加入連線（JOIN）子句來預先載入相關資料。

>>> from sqlalchemy.orm import joinedload
>>> q = select(Product).options(joinedload(Product.manufacturer))

透過使用 joinedload，上述迴圈將不再觸發額外的資料函式庫查詢，因為所有需要的資料都在初始查詢中被檢索出來。

內容解密：

  1. joinedload(Product.manufacturer)：指定使用 joinedload 策略來載入產品的製造商關聯。
  2. select(Product).options(...)：建立一個查詢，並透過 options 方法應用 joinedload 策略。

設定預設載入策略

除了在查詢時指定載入策略外，還可以在定義模型關聯時設定預設的載入策略。例如，可以將 manufacturer 關聯的預設載入策略設定為 joined：

class Product(Model):
    # ...
    manufacturer: Mapped['Manufacturer'] = relationship(
        lazy='joined', back_populates='products')
    # ...

這樣，所有涉及 Product.manufacturer 的查詢預設都會使用 joinedload 策略。

內容解密：

  1. lazy='joined'：在定義 manufacturer 關聯時指定預設使用 joined 載入策略。
  2. back_populates='products'：指定雙向關聯的另一端。

資料函式庫關聯與刪除操作的探討

在處理資料函式庫關聯時，SQLAlchemy 提供了多種載入策略（loaders），以便根據不同的使用場景最佳化效能。瞭解這些載入策略對於設計高效的資料函式庫操作至關重要。

載入策略的選擇

SQLAlchemy 提供了多種載入策略，包括 select、immediate、joined、subquery、selectin、write_only、noload、raise 和 raise_on_sql，以及 dynamic。每種策略都有其特定的使用場景和優缺點。

載入策略分類別

根據載入時機，這些策略可以分為三類別：

  1. 延遲載入（Lazy Load）：預設的 select 載入策略，以及遺留的 dynamic 載入策略。
  2. 積極載入（Eager Load）：包括 joined、selectin、subquery 和 immediate 載入策略。
  3. 明確載入（Explicit Load）：包括 write_only（SQLAlchemy 2.0 及以上版本）、noload、raise 和 raise_on_sql。

選擇合適的預設載入策略

對於小型應用程式，選擇預設的載入策略需要考慮效能和複雜度。在本例中，由於應用程式尚處於初期階段，因此保留預設的 select 載入策略是合理的選擇。

刪除相關物件與級聯刪除

刪除物件時，需要考慮關聯物件的處理。在一對多關係中，刪除「多」端的物件通常沒有問題。然而，刪除「一」端的物件可能會導致問題，因為「多」端的物件可能仍然參照被刪除的物件。

刪除範例

以下範例示範了刪除產品及其製造商的過程：

p = session.get(Product, 24)
m = p.manufacturer
session.delete(p)
session.commit()

刪除產品成功，但刪除製造商時出現錯誤，因為其他產品仍然參照該製造商。

session.delete(m)
session.commit()

錯誤訊息如下：

sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) NOT NULL constraint failed: products.manufacturer_id

解決方案

為瞭解決這個問題，需要組態級聯刪除（cascade delete）或更新關聯物件的 foreign key。SQLAlchemy 提供了多種級聯選項，包括 save-update、delete、delete-orphan 等。

程式碼範例

class Manufacturer(Base):
    __tablename__ = 'manufacturers'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    products = relationship('Product', backref='manufacturer', cascade='all, delete-orphan')

class Product(Base):
    __tablename__ = 'products'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    manufacturer_id = Column(Integer, ForeignKey('manufacturers.id'))

內容解密：

在上述程式碼中，我們定義了 Manufacturer 和 Product 兩個類別之間的關係。其中，Manufacturer 類別具有一個 products 屬性，該屬性使用 relationship 函式定義了一對多的關係。cascade='all, delete-orphan' 引數指定了級聯刪除的行為。當一個 Manufacturer 物件被刪除時，所有與之相關的 Product 物件也將被刪除。

級聯刪除的組態與影響

組態級聯刪除可以簡化刪除操作的處理，但也需要謹慎考慮其影響。啟用級聯刪除後，被刪除物件的所有相關物件也將被刪除，這可能導致資料丟失。

玄貓 BlackCat

玄貓 BlackCat

技術愛好者，專注於分享程式開發、雲端技術與 AI 應用的心得體會。