多對多關係查詢與 Alembic 資料函式庫遷移

– 10 分鐘閱讀

SQLAlchemy 提供了強大的 ORM 功能，方便開發者操作資料函式庫。本文將介紹如何使用 SQLAlchemy 處理多對多關係的查詢，例如取得特定國家生產的產品、具有多個生產國家的產品等。同時，我們也會探討如何結合多對多關係與一對多關係進行更複雜的查詢，例如查詢在特定國家營運的製造商。此外，文章也將介紹 Alembic 這個資料函式庫遷移工具，說明如何使用 Alembic 管理資料函式庫版本，包含安裝設定、產生遷移指令碼、升級與降級資料函式庫等操作。最後，我們將探討在資料函式庫模型設計中使用 UUID 作為主鍵的優點，以及如何結合 SQLAlchemy 使用 UUID，同時說明日期時間欄位與唯寫關聯的應用。

多對多關係的查詢與操作

在探討多對多關係的查詢與操作之前，我們先來瞭解多對多關係的基本概念。多對多關係是指兩個實體之間存在多個對應關係，例如產品和國家之間的關係，一個產品可以在多個國家生產，而一個國家也可以生產多個產品。

使用多對多關係進行查詢

首先，我們來看一個簡單的查詢例子，傳回某個國家（例如葡萄牙）所生產的產品：

>>> c = session.scalars(
...     select(Country)
...     .where(Country.name == 'Portugal')
... ).first()
>>> c
Country(22, "Portugal")
>>> c.products
[Product(138, "Timex Sinclair 1000"), Product(139, "Timex Sinclair 1500"),
 Product(140, "Timex Sinclair 2048"), Product(141, "Timex Computer 2048"),
 Product(142, "Timex Computer 2068"), Product(143, "Komputer 2086")]

內容解密：

  1. session.scalars(select(Country).where(Country.name == 'Portugal')).first()：查詢國家名稱為葡萄牙的記錄。
  2. c.products：取得該國家所生產的產品列表。

接下來，我們看一個更複雜的查詢，傳回所有具有多個生產國家的產品，以及每個產品的生產國家數量：

>>> country_count = func.count(Country.id).label(None)
>>> q = (select(Product, country_count)
...      .join(Product.countries)
...      .group_by(Product)
...      .having(country_count >= 2)
...      .order_by(Product.name))
>>> session.execute(q).all()
[(Product(143, "Komputer 2086"), 2), (Product(142, "Timex Computer 2068"), 3),
 (Product(138, "Timex Sinclair 1000"), 3), (Product(139, "Timex Sinclair 1500"), 3),
 (Product(140, "Timex Sinclair 2048"), 3)]

內容解密：

  1. func.count(Country.id).label(None)：計算每個產品的生產國家數量。
  2. select(Product, country_count).join(Product.countries)：聯合產品和國家表格，進行多對多關係的查詢。
  3. group_by(Product)：根據產品進行分組。
  4. having(country_count >= 2)：篩選出生產國家數量大於等於2的產品。
  5. order_by(Product.name)：根據產品名稱進行排序。

多對多關係與一對多關係的結合使用

多對多關係可以與一對多關係結合使用，從而實作更複雜的查詢。例如，查詢在英國營運的製造商列表：

>>> q = (select(Manufacturer)
...      .join(Manufacturer.products)
...      .join(Product.countries)
...      .where(Country.name == 'UK')
...      .order_by(Manufacturer.name)
...      .distinct())
>>> session.scalars(q).all()
[Manufacturer(1, "Acorn Computers Ltd"), Manufacturer(2, "Amstrad"), ...,
 Manufacturer(70, "Timex Sinclair")]

內容解密：

  1. select(Manufacturer).join(Manufacturer.products).join(Product.countries)：透過產品將製造商和國家表格聯合起來。
  2. where(Country.name == 'UK')：篩選出在英國營運的製造商。
  3. distinct()：去除重複的製造商記錄。

多對多關係中的刪除操作

當組態了secondary選項的多對多關係時，SQLAlchemy會自動維護連線表格。當刪除一個實體時，SQLAlchemy會自動刪除與之相關聯的其他實體之間的連結。

例如，刪除一個國家，該國家將自動從相關產品的生產國家列表中消失。

資料函式庫遷移與Alembic的使用

在開發過程中，資料函式庫結構的變更是不可避免的。為了有效地管理這些變更，SQLAlchemy提供了Alembic這個強大的資料函式庫遷移工具。本章節將介紹如何使用Alembic來管理RetroFun專案的資料函式庫遷移。

安裝Alembic

首先，需要在虛擬環境中安裝Alembic套件：

(venv) $ pip install alembic

初始化Alembic

安裝完成後，使用以下命令初始化Alembic：

(venv) $ alembic init migrations

這將在專案目錄下建立一個名為migrations的子目錄，用於存放資料函式庫遷移指令碼。同時，也會在專案目錄下建立一個名為alembic.ini的組態檔案。

設定Alembic

為了讓Alembic能夠正確地連線到專案的資料函式庫，需要對migrations/env.py檔案進行一些設定：

from db import Model, engine
import models

target_metadata = Model.metadata
config.set_main_option("sqlalchemy.url", engine.url.render_as_string(hide_password=False))

這裡匯入了Model和engine，並設定了target_metadata和sqlalchemy.url。

此外，為了支援SQLite資料函式庫，需要在run_migrations_online()函式中啟用render_as_batch選項：

context.configure(
    connection=connection, target_metadata=target_metadata,
    render_as_batch=True,
)

匯入模型

需要注意的是，儘管import models看起來似乎是不必要的，但它實際上是必須的。因為SQLAlchemy需要透過匯入模型類別來瞭解應用程式中的模型。

建立遷移指令碼

Alembic使用遷移指令碼來追蹤對資料函式庫所做的變更。可以使用以下命令自動生成初始遷移指令碼：

(venv) $ alembic revision --autogenerate -m "products, manufacturers, countries"

在執行此命令之前，需要確保資料函式庫是完全空的。可以使用以下Python程式碼清空資料函式庫：

>>> from db import Model, engine
>>> import models
>>> Model.metadata.drop_all(engine)

生成的遷移指令碼是一個Python模組，包含upgrade()和downgrade()兩個函式。upgrade()函式用於將變更應用到資料函式庫，而downgrade()函式則用於復原這些變更。

內容解密：

  1. alembic revision --autogenerate 命令的作用：此命令告訴Alembic比較目前的資料函式庫結構與模型定義之間的差異，並自動生成一個遷移指令碼來使資料函式庫結構與模型定義保持一致。
  2. upgrade() 和 downgrade() 函式的作用：upgrade()函式包含了使資料函式庫升級到最新結構所需的SQL命令或操作；downgrade()函式則包含了復原這些變更所需的命令，用於將資料函式庫降級到之前的版本。
  3. 為什麼需要 import models：SQLAlchemy透過匯入模型類別來瞭解應用程式中的模型。如果模型類別沒有被匯入，SQLAlchemy和Alembic就無法知道這些模型的存在，也就無法正確地生成遷移指令碼。

資料函式庫遷移與管理

在開發資料函式庫驅動的應用程式時，資料函式庫結構的管理是一項重要任務。Alembic是一個流行的工具，用於管理SQLAlchemy專案中的資料函式庫遷移。本文將探討如何使用Alembic來升級和降級資料函式庫結構，以及如何修改產品匯入器以配合Alembic的使用。

自動產生遷移指令碼

Alembic提供了一個自動產生遷移指令碼的功能，可以根據模型的變更自動產生升級和降級的SQL指令。雖然這個功能非常方便，但並非萬無一失，因此審查自動產生的指令碼以確保正確性是非常重要的。

升級資料函式庫

要升級資料函式庫，首先需要建立一個遷移指令碼，然後執行該指令碼。可以使用以下指令：

alembic revision --autogenerate
alembic upgrade head

第一個指令會根據模型的變更自動產生一個遷移指令碼，第二個指令則會執行該指令碼，將資料函式庫升級到最新的結構。

修改產品匯入器

由於Alembic現在負責管理資料函式庫結構，因此需要修改產品匯入器，以避免與Alembic衝突。新的匯入器將刪除所有現有的資料，而不是刪除並重新建立表格。

import csv
from sqlalchemy import delete
from db import Session
from models import Product, Manufacturer, Country, ProductCountry

def main():
    with Session() as session:
        with session.begin():
            session.execute(delete(ProductCountry))
            session.execute(delete(Product))
            session.execute(delete(Manufacturer))
            session.execute(delete(Country))

    # ... (其餘程式碼保持不變)

進階多對多關係

在某些情況下，多對多關係需要額外的資料。在RetroFun的例子中，訂單和產品之間的關係需要包含數量和單價等資訊。這種情況下，需要使用一個較為手動的工作流程來管理多對多關係。

新增客戶和訂單模型

為了實作訂單系統，需要新增客戶和訂單模型，並建立它們之間的一對多關係。同時，也需要在訂單和產品之間建立多對多關係，使用一個包含額外資料的關聯表。

from datetime import datetime
from uuid import UUID, uuid4
from sqlalchemy.orm import WriteOnlyMapped

class Order(Model):
    __tablename__ = 'orders'
    id: Mapped[UUID] = mapped_column(default=uuid4, primary_key=True)
    timestamp: Mapped[datetime] = mapped_column(default=datetime.utcnow, index=True)
    customer_id: Mapped[UUID] = mapped_column(ForeignKey('customers.id'), index=True)
    customer: Mapped['Customer'] = relationship(back_populates='orders')

class Customer(Model):
    __tablename__ = 'customers'
    # ... (其他欄位定義)

內容解密：

  1. alembic revision --autogenerate: 這個指令會根據模型的變更自動產生一個遷移指令碼，讓開發者可以輕鬆地升級或降級資料函式庫結構。
  2. alembic upgrade head: 執行這個指令會將資料函式庫升級到最新的結構，確保資料函式庫與模型定義保持一致。
  3. 修改產品匯入器: 由於Alembic負責管理資料函式庫結構，因此需要修改產品匯入器，以刪除所有現有的資料，而不是刪除並重新建立表格，避免與Alembic衝突。
  4. 進階多對多關係: 在某些情況下，多對多關係需要額外的資料，例如訂單和產品之間的關係。這種情況下，需要使用一個較為手動的工作流程來管理多對多關係，並建立一個包含額外資料的關聯表。
  5. 新增客戶和訂單模型: 為了實作訂單系統，需要新增客戶和訂單模型，並建立它們之間的一對多關係。同時，也需要在訂單和產品之間建立多對多關係，使用一個包含額外資料的關聯表。

練習

  1. 查詢在英國或美國製造的產品：使用SQLAlchemy的查詢功能，找出在英國或美國製造的產品。
  2. 查詢未在英國或美國製造的產品：找出未在英國或美國製造的產品，但可能與其他國家共同製造。
  3. 查詢具有Z80 CPU或其克隆的產品的國家：使用多對多關係，找出具有Z80 CPU或其克隆的產品的國家。
  4. 查詢在1970年代製造產品的國家（按字母順序）：找出在1970年代製造產品的國家，並按字母順序排序。
  5. 查詢產品數量最多的5個國家：找出產品數量最多的5個國家，如果有並列，則按字母順序排序。

內容解密：

這些練習旨在幫助開發者熟悉SQLAlchemy的查詢功能，並瞭解如何使用多對多關係來查詢相關資料。透過這些練習，開發者可以更好地理解如何使用SQLAlchemy來管理和查詢資料函式庫中的資料。

資料函式庫模型設計與UUID主鍵應用

在現代資料函式庫設計中，主鍵的選擇對於資料的安全性與隱私保護至關重要。本文將探討如何使用UUID作為主鍵，以及相關的資料函式庫模型設計考量。

UUID主鍵的優勢

傳統的自動遞增整數主鍵雖然使用方便，但在某些情況下可能會洩露資料函式庫的大小或相關統計資訊。為瞭解決這個問題，可以採用UUID作為主鍵。UUID是一種16位元組的二進位制序列，其中UUID4是一種適合用作主鍵的隨機產生型別。

UUID4的產生與表示

Python中的uuid模組提供了產生UUID4的功能：

>>> from uuid import uuid4
>>> id = uuid4()
>>> id.bytes
b'kF\xf8\xe9o\x94MM\xad\xfe\x15\xe1\xeb\xb1\xd0\xac'
>>> id.hex
'6b46f8e96f944d4dadfe15e1ebb1d0ac'

在SQLAlchemy中使用UUID主鍵

在SQLAlchemy 2.0版本之後，支援直接使用UUID型別。以下是一個使用UUID主鍵的範例模型：

id: Mapped[UUID] = mapped_column(default=uuid4, primary_key=True)

內容解密：

  1. Mapped[UUID]：定義id欄位的型別為UUID，並使用Mapped進行型別提示。
  2. mapped_column：用於定義欄位的設定。
  3. default=uuid4：在建立新記錄時，如果沒有指定id，則自動呼叫uuid4()函式產生一個新的UUID4值。
  4. primary_key=True：將id欄位設定為主鍵。

這種設定確保了每個新的記錄都會被賦予一個唯一的UUID4作為主鍵，而不需要手動指定。

日期與時間欄位處理

在處理訂單等需要時間戳記的資料時，通常會使用日期時間欄位。以下是一個範例：

timestamp: Mapped[datetime] = mapped_column(default=datetime.utcnow)

內容解密：

  1. Mapped[datetime]：定義timestamp欄位的型別為Python的datetime物件。
  2. default=datetime.utcnow：在建立新記錄時，如果沒有指定timestamp，則自動使用目前的UTC時間。
  3. 使用UTC時間可以避免時區混淆，並在需要時轉換為客戶端的本地時間。

唯寫關聯（Write-Only Relationships）

在某些情況下，關聯的資料量可能非常龐大，例如客戶的訂單記錄。此時，可以使用唯寫關聯來最佳化效能：

orders: WriteOnlyMapped['Order'] = relationship(back_populates='customer', lazy='write_only')

內容解密：

  1. WriteOnlyMapped['Order']：定義一個與Order模型的關聯，使用WriteOnlyMapped進行型別提示。
  2. lazy='write_only'：設定關聯的載入策略為唯寫，適合用於大型集合。
  3. 這種設定允許應用程式在需要時查詢特定的訂單子集，而不是一次性載入所有相關資料。
玄貓 BlackCat

玄貓 BlackCat

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