SQLAlchemy ORM - 刪除關聯物件

在單個表上執行刪除操作很容易。你所要做的就是從會話中刪除對映類的物件並提交操作。但是，對多個相關表的刪除操作有點棘手。

在我們的 sales.db 資料庫中，Customer 和 Invoice 類分別對映到 customer 和 invoice 表，它們之間是一對多的關係。我們將嘗試刪除 Customer 物件並檢視結果。

作為快速參考，以下是 Customer 和 Invoice 類的定義：

from sqlalchemy import create_engine, ForeignKey, Column, Integer, String
engine = create_engine('sqlite:///sales.db', echo = True)
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
from sqlalchemy.orm import relationship
class Customer(Base):
   __tablename__ = 'customers'

   id = Column(Integer, primary_key = True)
   name = Column(String)
   address = Column(String)
   email = Column(String)
   
class Invoice(Base):
   __tablename__ = 'invoices'

   id = Column(Integer, primary_key = True)
   custid = Column(Integer, ForeignKey('customers.id'))
   invno = Column(Integer)
   amount = Column(Integer)
   customer = relationship("Customer", back_populates = "invoices")
   
Customer.invoices = relationship("Invoice", order_by = Invoice.id, back_populates = "customer")

我們設定了一個會話，並使用下面的程式透過主鍵 ID 查詢獲取一個 Customer 物件：

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
session = Session()
x = session.query(Customer).get(2)

在我們的示例表中，x.name 恰好是 'Gopal Krishna'。讓我們從會話中刪除這個 x 並計算這個名稱的出現次數。

session.delete(x)
session.query(Customer).filter_by(name = 'Gopal Krishna').count()

生成的 SQL 表示式將返回 0。

SELECT count(*) 
AS count_1
FROM (
   SELECT customers.id 
   AS customers_id, customers.name 
   AS customers_name, customers.address 
   AS customers_address, customers.email 
   AS customers_email
   FROM customers
   WHERE customers.name = ?) 
AS anon_1('Gopal Krishna',) 0

但是，x 的相關 Invoice 物件仍然存在。可以透過以下程式碼驗證：

session.query(Invoice).filter(Invoice.invno.in_([10,14])).count()

這裡，10 和 14 是屬於 Gopal Krishna 客戶的發票號碼。上述查詢的結果是 2，這意味著相關物件尚未被刪除。

SELECT count(*) 
AS count_1
FROM (
   SELECT invoices.id 
   AS invoices_id, invoices.custid 
   AS invoices_custid, invoices.invno 
   AS invoices_invno, invoices.amount 
   AS invoices_amount
   FROM invoices
   WHERE invoices.invno IN (?, ?)) 
AS anon_1(10, 14) 2

這是因為 SQLAlchemy 並不假設級聯刪除；我們必須發出刪除指令。

要更改此行為，我們需要在 User.addresses 關係上配置級聯選項。讓我們關閉當前會話，使用新的 declarative_base() 並重新宣告 User 類，並在 addresses 關係中新增級聯配置。

relationship 函式中的 cascade 屬性是一個用逗號分隔的級聯規則列表，它確定會話操作應如何從父級級聯到子級。預設情況下，它是 False，這意味著它是“save-update, merge”。

可用的級聯選項如下：

• save-update
• merge
• expunge
• delete
• delete-orphan
• refresh-expire

常用選項是 "all, delete-orphan"，表示相關物件在所有情況下都應跟隨父物件，並在取消關聯時被刪除。

因此，重新宣告的 Customer 類如下所示：

class Customer(Base): 
   __tablename__ = 'customers'
   
   id = Column(Integer, primary_key = True) 
   name = Column(String) 
   address = Column(String) 
   email = Column(String) 
   invoices = relationship(
      "Invoice", 
      order_by = Invoice.id, 
      back_populates = "customer",
      cascade = "all, 
      delete, delete-orphan" 
   )

讓我們使用下面的程式刪除名為 Gopal Krishna 的 Customer，並檢視其相關 Invoice 物件的數量：

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind = engine)
session = Session()
x = session.query(Customer).get(2)
session.delete(x)
session.query(Customer).filter_by(name = 'Gopal Krishna').count()
session.query(Invoice).filter(Invoice.invno.in_([10,14])).count()

現在計數為 0，上面指令碼發出的 SQL 如下：

SELECT customers.id 
AS customers_id, customers.name 
AS customers_name, customers.address 
AS customers_address, customers.email 
AS customers_email
FROM customers
WHERE customers.id = ?
(2,)
SELECT invoices.id 
AS invoices_id, invoices.custid 
AS invoices_custid, invoices.invno 
AS invoices_invno, invoices.amount
AS invoices_amount
FROM invoices
WHERE ? = invoices.custid 
ORDER BY invoices.id (2,)
DELETE FROM invoices 
WHERE invoices.id = ? ((1,), (2,))
DELETE FROM customers 
WHERE customers.id = ? (2,)
SELECT count(*) 
AS count_1
FROM (
   SELECT customers.id 
   AS customers_id, customers.name 
   AS customers_name, customers.address 
   AS customers_address, customers.email 
   AS customers_email
   FROM customers
   WHERE customers.name = ?) 
AS anon_1('Gopal Krishna',)
SELECT count(*) 
AS count_1
FROM (
   SELECT invoices.id 
   AS invoices_id, invoices.custid 
   AS invoices_custid, invoices.invno 
   AS invoices_invno, invoices.amount 
   AS invoices_amount
   FROM invoices
   WHERE invoices.invno IN (?, ?)) 
AS anon_1(10, 14)
0