Sqlalchemy 简明教程

SQLAlchemy ORM - Deleting Related Objects

对单个表执行删除操作很容易。你所要做的就是从会话中删除映射类的对象并提交该操作。但是，对多个相关表的删除操作有点棘手。

It is easy to perform delete operation on a single table. All you have to do is to delete an object of the mapped class from a session and commit the action. However, delete operation on multiple related tables is little tricky.

在我们的 sales.db 数据库中，Customer 和 Invoice 类被映射到客户和账单表，关系类型为一对多。我们将尝试删除 Customer 对象并查看结果。

In our sales.db database, Customer and Invoice classes are mapped to customer and invoice table with one to many type of relationship. We will try to delete Customer object and see the result.

作为快速参考，以下是 Customer 和 Invoice 类的定义：

As a quick reference, below are the definitions of Customer and Invoice classes −

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 对象：

We setup a session and obtain a Customer object by querying it with primary ID using the below program −

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

在我们的示例表中，x.name 恰好是“Gopal Krishna”。让我们从会话中删除这个 x，并计算这个名字的出现次数。

In our sample table, x.name happens to be 'Gopal Krishna'. Let us delete this x from the session and count the occurrence of this name.

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

生成的 SQL 表达式将返回 0。

The resulting SQL expression will return 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 对象仍然存在。可以通过以下代码进行验证：

However, the related Invoice objects of x are still there. It can be verified by the following code −

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

此处，10 和 14 是属于客户 Gopal Krishna 的发票号码。上述查询的结果为 2，这意味着相关的对象尚未被删除。

Here, 10 and 14 are invoice numbers belonging to customer Gopal Krishna. Result of the above query is 2, which means the related objects have not been deleted.

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 不会认为是级联删除；我们必须提供一个命令来删除它。

This is because SQLAlchemy doesn’t assume the deletion of cascade; we have to give a command to delete it.

要更改此行为，我们配置 User.addresses 关系中的级联选项。让我们关闭正在进行的会话，使用新的 declarative_base() 并重新声明 User 类，并添加包含级联配置的 addresses 关系。

To change the behavior, we configure cascade options on the User.addresses relationship. Let us close the ongoing session, use new declarative_base() and redeclare the User class, adding in the addresses relationship including the cascade configuration.

relationship 函数中的级联属性是一个级联规则的分隔列表，该列表确定会话操作应如何从父级级联到子级。默认情况下，它是错误的，这意味着它是“保存-更新，合并”。

The cascade attribute in relationship function is a comma-separated list of cascade rules which determines how Session operations should be “cascaded” from parent to child. By default, it is False, which means that it is "save-update, merge".

可用的级联如下 −

The available cascades are as follows −

  1. save-update

  2. merge

  3. expunge

  4. delete

  5. delete-orphan

  6. refresh-expire

通常使用的选项是“全部、删除孤立项”，以表示在所有情况下相关对象都应与父对象一起执行，并在取消关联时将其删除。

Often used option is "all, delete-orphan" to indicate that related objects should follow along with the parent object in all cases, and be deleted when de-associated.

因此，重新声明的 Customer 类如下所示 −

Hence redeclared Customer class is shown below −

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 对象的数量 −

Let us delete the Customer with Gopal Krishna name using the below program and see the count of its related Invoice objects −

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 −

The count is now 0 with following SQL emitted by above script −

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