SQLAlchemy 級聯操作與多對多關係

– 7 分鐘閱讀

在 SQLAlchemy 中，管理資料函式庫物件關係至關重要。級聯操作能自動處理關聯物件的變更，例如當父物件刪除時，子物件也一併刪除。多對多關係則允許多個物件彼此關聯，例如一個產品可以銷售到多個國家，而一個國家也可以銷售多種產品。這需要藉由關聯表來實作，並使用 SQLAlchemy 的 relationship 函式設定。理解這些機制能有效提升資料函式庫操作效率和資料一致性，對於開發複雜應用程式至關重要。

SQLAlchemy 中的級聯操作與關係解除

在SQLAlchemy中，級聯操作（cascades）是一種自動化的機制，用於處理物件之間的關係。當對一個物件執行某種操作時，SQLAlchemy 可以根據設定的級聯選項，自動對與其相關的物件執行相應的操作。這些操作包括儲存、更新、合併、刪除等。

級聯操作的基本概念

級聯操作的核心是定義在 relationship() 函式中的 cascade 引數。透過設定不同的級聯選項，可以控制當對父物件執行某個操作時，子物件會受到怎樣的影響。

常見的級聯組態

  1. ‘save-update, merge’：這是預設的級聯行為，表示當父物件被加入到session中時，子物件也會自動被加入。這種組態適合大多數的關係。

  2. ‘all, delete-orphan’：這種組態會使父物件的操作應用到子物件上，包括刪除。當父物件被刪除時，子物件也會被刪除。此外，當子物件從關係中被移除且變成孤兒（orphaned）時，也會被刪除。

設定級聯選項

在 models.py 中，可以這樣設定級聯選項：

class Manufacturer(Model):
    # ...
    products: Mapped[list['Product']] = relationship(
        cascade='all, delete-orphan', back_populates='manufacturer')
    # ...

內容解密：

  • cascade='all, delete-orphan'：這行程式碼設定了 products 關係的級聯行為為 'all, delete-orphan'。這意味著對 Manufacturer 物件的操作會應用到其 products 上，並且當產品從製造商的關係中被移除時，這些產品會被刪除。

解除物件之間的關係

有時候，需要解除兩個物件之間的關係，而不刪除物件本身。這可以透過兩種方式實作，取決於關係的方向。

從“一”端解除關係

對於一對多關係，可以使用 remove() 方法從“一”端解除與“多”端物件的關係。

>>> p = session.get(Product, 1)
>>> m = p.manufacturer
>>> m.products.remove(p)
>>> session.commit()

內容解密：

  • m.products.remove(p)：這行程式碼從製造商 m 的產品列表中移除產品 p，從而解除了 m 和 p 之間的關係。
  • session.commit()：提交變更到資料函式庫。

從“多”端解除關係

也可以從“多”端解除關係，方法是將父物件設為 None。

>>> p = session.get(Product, 2)
>>> p.manufacturer = None
>>> session.commit()

內容解密：

  • p.manufacturer = None：這行程式碼將產品 p 的製造商設為 None，從而解除了 p 和其原製造商之間的關係。

練習

  1. 查詢IBM和Texas Instruments生產的產品列表：

    >>> from db import Session
>>> from models import Product, Manufacturer
>>> session = Session()
>>> manufacturers = session.query(Manufacturer).filter(Manufacturer.name.in_(["IBM", "Texas Instruments"])).all()
>>> products = session.query(Product).filter(Product.manufacturer_id.in_([m.id for m in manufacturers])).all()

    內容解密：

    • 首先查詢名稱為 “IBM” 和 “Texas Instruments” 的製造商。
    • 然後查詢這些製造商生產的產品。

  2. 查詢在巴西營運的製造商：

    >>> manufacturers = session.query(Manufacturer).filter(Manufacturer.country == "Brazil").all()

    內容解密：

    • 查詢國家為 “Brazil” 的製造商。

  3. 查詢名稱包含“Research”的製造商生產的產品：

    >>> manufacturers = session.query(Manufacturer).filter(Manufacturer.name.like("%Research%")).all()
>>> products = session.query(Product).filter(Product.manufacturer_id.in_([m.id for m in manufacturers])).all()

    內容解密：

    • 首先查詢名稱包含 “Research” 的製造商。
    • 然後查詢這些製造商生產的產品。

  4. 查詢生產根據Z80 CPU或其相容品的產品的製造商：

    >>> products = session.query(Product).filter(Product.cpu.like("Z80%")).all()
>>> manufacturers = set([p.manufacturer for p in products])

    內容解密：

    • 首先查詢CPU為 “Z80” 或其相容品的產品。
    • 然後取得這些產品的製造商。

  5. 查詢未生產根據6502 CPU或其相容品的產品的製造商：

    >>> products_6502 = session.query(Product).filter(Product.cpu.like("6502%")).all()
>>> manufacturers_6502 = set([p.manufacturer for p in products_6502])
>>> all_manufacturers = session.query(Manufacturer).all()
>>> other_manufacturers = [m for m in all_manufacturers if m not in manufacturers_6502]

    內容解密：

    • 首先查詢CPU為 “6502” 或其相容品的產品及其製造商。
    • 然後取得所有未生產這些CPU產品的製造商。

  6. 查詢製造商及其首次推出產品的年份，按年份排序：

    >>> from sqlalchemy import func
>>> results = session.query(Manufacturer.name, func.min(Product.year)).join(Product).group_by(Manufacturer.name).order_by(func.min(Product.year)).all()

    內容解密：

    • 連線 Manufacturer 和 Product 表。
    • 使用 func.min() 取得每個製造商首次推出產品的年份。
    • 按年份排序結果。

  7. 查詢目錄中有3到5個產品的製造商：

    >>> from sqlalchemy import func
>>> results = session.query(Manufacturer).join(Product).group_by(Manufacturer.id).having(func.count(Product.id).between(3, 5)).all()

    內容解密：

    • 連線 Manufacturer 和 Product 表。
    • 使用 group_by() 對製造商進行分組，並使用 having() 篩選出產品數量在3到5之間的製造商。

  8. 查詢營運超過5年的製造商：

    >>> results = session.query(Manufacturer).filter(Manufacturer.years_operating > 5).all()

    內容解密：

    • 直接篩選出營運超過5年的製造商。

多對多關係的實作與應用

在資料函式庫設計中，多對多關係是一種常見且重要的關係型別。當兩個實體之間無法明確區分出一對多關係時，就需要使用多對多關係來描述它們之間的關聯。本文將探討多對多關係的概念、實作方法以及在 SQLAlchemy 中的應用。

多對多關係的概念

多對多關係是指兩個實體之間存在多個關聯，例如產品和國家之間的關係。一個產品可能由多個國家共同生產，而一個國家也可能生產多個產品。這種關係無法透過單純的一對多關係來描述，因此需要引入多對多關係。

多對多關係的實作

要實作多對多關係，需要引入一個第三方的關聯表（join table）。這個關聯表包含了兩個實體的主鍵，用於建立它們之間的關聯。以產品和國家為例，關聯表 products_countries 包含了 product_id 和 country_id 兩個外部索引鍵，分別指向 products 表和 countries 表。

from sqlalchemy import Table, Column, ForeignKey

ProductCountry = Table(
    'products_countries',
    Model.metadata,
    Column('product_id', ForeignKey('products.id'), primary_key=True, nullable=False),
    Column('country_id', ForeignKey('countries.id'), primary_key=True, nullable=False),
)

內容解密：

  1. ProductCountry 是一個關聯表，用於建立產品和國家之間的多對多關係。
  2. Table 函式用於建立關聯表，引數包括表名、metadata 物件和欄位定義。
  3. Column 函式用於定義欄位，ForeignKey 用於建立外部索引鍵關聯。
  4. primary_key=True 表示該欄位是主鍵的一部分，nullable=False 表示該欄位不能為空。

SQLAlchemy 中的多對多關係

在 SQLAlchemy 中，多對多關係可以透過 relationship 函式來建立。以產品和國家為例，可以在 Product 和 Country 模型中分別建立 countries 和 products 屬性，用於存取相關的國家和產品。

class Product(Model):
    __tablename__ = 'products'
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(64), index=True, unique=True)
    countries: Mapped[list['Country']] = relationship(
        secondary=ProductCountry, back_populates='products')

class Country(Model):
    __tablename__ = 'countries'
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(32), index=True, unique=True)
    products: Mapped[list['Product']] = relationship(
        secondary=ProductCountry, back_populates='countries')

內容解密：

  1. relationship 函式用於建立多對多關係，secondary 引數指定了關聯表。
  2. back_populates 引數用於建立雙向關聯，使得 Product 和 Country 可以互相存取對方的屬性。
  3. Mapped[list['Country']] 和 Mapped[list['Product']] 表示屬性是一個列表，分別包含了相關的國家和產品。

多對多關係的建立與查詢

在資料函式庫模型設計中，多對多關係是一種常見的關聯形式，允許兩個實體之間存在多個對應關係。在本文中，我們將探討如何使用 SQLAlchemy 建立多對多關係，以及如何進行相關的查詢操作。

多對多關係的建立

為了建立多對多關係，我們需要定義一個關聯表（join table），該表包含兩個外部索引鍵，分別指向兩個相關實體的主鍵。以產品（Product）和國家（Country）為例，我們可以建立一個多對多關係，使得一個產品可以與多個國家關聯，同時一個國家也可以與多個產品關聯。

# 定義關聯表
product_country = Table(
    'product_country', Model.metadata,
    Column('product_id', Integer, ForeignKey('products.id'), primary_key=True),
    Column('country_id', Integer, ForeignKey('countries.id'), primary_key=True)
)

# 在 Product 模型中定義 countries 關係屬性
class Product(Model):
    # ...
    countries = relationship('Country', secondary=product_country, back_populates='products')

# 在 Country 模型中定義 products 關係屬性
class Country(Model):
    # ...
    products = relationship('Product', secondary=product_country, back_populates='countries')

內容解密：

  1. product_country 表的定義：該表作為關聯表，包含兩個外部索引鍵，分別是 product_id 和 country_id，用於建立產品和國家之間的關聯。
  2. relationship 的使用：在 Product 和 Country 模型中，分別定義了 countries 和 products 關係屬性，並透過 secondary 引數指定關聯表。
  3. back_populates 的作用：確保兩個關係屬性之間的雙向關聯，使得 SQLAlchemy 能夠正確維護兩者之間的關係。

產品匯入指令碼的更新

為了正確地將國家資訊與產品關聯，我們需要更新產品匯入指令碼。

# 更新後的 import_products.py
import csv
from db import Model, Session, engine
from models import Product, Manufacturer, Country

def main():
    # ...
    with Session() as session:
        with session.begin():
            # ...
            for row in reader:
                # ...
                countries = row.pop('country').split('/')
                p = Product(**row)
                # ...
                for country in countries:
                    if country not in all_countries:
                        c = Country(name=country)
                        session.add(c)
                        all_countries[country] = c
                    all_countries[country].products.append(p)
                # ...

if __name__ == '__main__':
    main()

內容解密：

  1. countries 的處理：將 country 欄位的字串值分割成國家名稱列表，並據此建立或檢索相應的 Country 例項。
  2. 產品與國家的關聯：透過將產品追加到國家的 products 關係屬性中，建立兩者之間的關聯。
  3. 雙向關聯的建立：SQLAlchemy 會自動維護雙向關聯，無論是透過 all_countries[country].products.append(p) 還是 p.countries.append(all_countries[country])。

多對多關係的查詢

建立多對多關係後，我們可以透過定義的關係屬性進行查詢。

# 查詢產品及其相關國家
>>> p = session.scalar(select(Product).where(Product.name == 'Timex Sinclair 1000'))
>>> p.countries
[Country(1, "UK"), Country(3, "USA"), Country(22, "Portugal")]

# 查詢國家及其相關產品
>>> c = session.scalar(select(Country).where(Country.name == 'UK'))
>>> c.products
[Product(138, "Timex Sinclair 1000"), ...]

內容解密：

  1. 查詢產品的國家：透過存取產品的 countries 關係屬性，取得與該產品相關的國家列表。
  2. 查詢國家的產品：同樣，透過存取國家的 products 關係屬性，取得與該國家相關的產品列表。
  3. 懶載入機制：預設情況下，SQLAlchemy 使用懶載入機制，在首次存取關係屬性時執行查詢以取得相關資料。
玄貓 BlackCat

玄貓 BlackCat

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