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 的invoices号码。上述查询的结果为 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 类，添加包括级联配置的地址关系。

关系函数中的级联属性是一个以逗号分隔的级联规则列表，它决定了会话操作应如何从父级"级联"到子级。默认情况下，它是 False，这意味着它是"保存-更新，合并"。

可用的级联如下 −

• 保存-更新
• 合并
• 删除
• 删除-孤立
• 刷新-过期

经常使用的选项是"全部，删除-孤立"，表示相关对象在所有情况下都应跟随父对象，并在取消关联时被删除。

因此重新声明的 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 的客户，并查看其相关invoices对象的数量 −

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()

使用上述脚本发出的以下 SQL，计数现在为 0 −

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