版本:1.1.0b2 |发布日期:2016年7月1日

SQLAlchemy 1.1文档

级联¶ T0>

映射器在relationship()结构上支持可配置级联行为的概念。这涉及如何将相对于特定Session的“父”对象执行的操作传播到由该关系引用的项目(例如“子”对象),并且受到relationship.cascade选项。

级联的默认行为仅限于所谓的save-updatemerge设置的级联。级联的典型“替代”设置是添加deletedelete-orphan选项;这些设置适用于相关的对象,只要它们连接到它们的父对象上,这些对象只会存在,否则将被删除。

级联行为使用relationship()上的cascade选项进行配置:

class Order(Base):
    __tablename__ = 'order'

    items = relationship("Item", cascade="all, delete-orphan")
    customer = relationship("User", cascade="save-update")

为了在backref上设置级联,同样的标志可以和backref()函数一起使用,该函数最终将它的实参返回到relationship()

class Item(Base):
    __tablename__ = 'item'

    order = relationship("Order",
                    backref=backref("items", cascade="all, delete-orphan")
                )

cascade的默认值是save-update merge这个参数的典型替代设置是all或者更常见的是all, delete-orphanThe all symbol is a synonym for save-update, merge, refresh-expire, expunge, delete, and using it in conjunction with delete-orphan indicates that the child object should follow along with its parent in all cases, and be deleted once it is no longer associated with that parent.

以下小节介绍了可以为cascade参数指定的可用值列表。

保存更新¶ T0>

save-update cascade indicates that when an object is placed into a Session via Session.add(), all the objects associated with it via this relationship() should also be added to that same Session. 假设我们有一个包含两个相关对象user1的对象address1address2

>>> user1 = User()
>>> address1, address2 = Address(), Address()
>>> user1.addresses = [address1, address2]

If we add user1 to a Session, it will also add address1, address2 implicitly:

>>> sess = Session()
>>> sess.add(user1)
>>> address1 in sess
True

save-update级联还会影响已经存在于Session中的对象的属性操作。If we add a third object, address3 to the user1.addresses collection, it becomes part of the state of that Session:

>>> address3 = Address()
>>> user1.append(address3)
>>> address3 in sess
>>> True

save-update has the possibly surprising behavior which is that persistent objects which were removed from a collection or in some cases a scalar attribute may also be pulled into the Session of a parent object; this is so that the flush process may handle that related object appropriately. 这种情况通常只能在从一个Session中删除一个对象并添加到另一个时才会出现:

>>> user1 = sess1.query(User).filter_by(id=1).first()
>>> address1 = user1.addresses[0]
>>> sess1.close()   # user1, address1 no longer associated with sess1
>>> user1.addresses.remove(address1)  # address1 no longer associated with user1
>>> sess2 = Session()
>>> sess2.add(user1)   # ... but it still gets added to the new session,
>>> address1 in sess2  # because it's still "pending" for flush
True

save-update级联在默认情况下处于启用状态,通常视为理所当然;它通过一次调用Session.add()来一次注册Session内的整个对象结构,从而简化了代码。虽然可以禁用,但通常不需要这样做。

One case where save-update cascade does sometimes get in the way is in that it takes place in both directions for bi-directional relationships, e.g. backrefs, meaning that the association of a child object with a particular parent can have the effect of the parent object being implicitly associated with that child object’s Session; this pattern, as well as how to modify its behavior using the cascade_backrefs flag, is discussed in the section Controlling Cascade on Backrefs.

删除¶ T0>

delete级联表示当“父”对象被标记为删除时,其相关的“子”对象也应被标记为删除。例如,如果我们有User.addressesdelete级联关系的配置:

class User(Base):
    # ...

    addresses = relationship("Address", cascade="save-update, merge, delete")

如果使用上面的映射,我们有一个User对象和两个相关的Address对象:

>>> user1 = sess.query(User).filter_by(id=1).first()
>>> address1, address2 = user1.addresses

If we mark user1 for deletion, after the flush operation proceeds, address1 and address2 will also be deleted:

>>> sess.delete(user1)
>>> sess.commit()
DELETE FROM address WHERE address.id = ? ((1,), (2,)) DELETE FROM user WHERE user.id = ? (1,) COMMIT

Alternatively, if our User.addresses relationship does not have delete cascade, SQLAlchemy’s default behavior is to instead de-associate address1 and address2 from user1 by setting their foreign key reference to NULL. 使用映射如下:

class User(Base):
    # ...

    addresses = relationship("Address")

删除父User对象后,address中的行不会被删除,而会被取消关联:

>>> sess.delete(user1)
>>> sess.commit()
UPDATE address SET user_id=? WHERE address.id = ? (None, 1) UPDATE address SET user_id=? WHERE address.id = ? (None, 2) DELETE FROM user WHERE user.id = ? (1,) COMMIT

delete cascade is more often than not used in conjunction with delete-orphan cascade, which will emit a DELETE for the related row if the “child” object is deassociated from the parent. deletedelete-orphan级联的组合涵盖了SQLAlchemy必须决定将外键列设置为NULL还是完全删除行的两种情况。

ORM级“删除”级联与FOREIGN KEY级别“ON DELETE”级联

The behavior of SQLAlchemy’s “delete” cascade has a lot of overlap with the ON DELETE CASCADE feature of a database foreign key, as well as with that of the ON DELETE SET NULL foreign key setting when “delete” cascade is not specified. Database level “ON DELETE” cascades are specific to the “FOREIGN KEY” construct of the relational database; SQLAlchemy allows configuration of these schema-level constructs at the DDL level using options on ForeignKeyConstraint which are described at ON UPDATE and ON DELETE.

重要的是要注意ORM和关系数据库的“级联”概念之间的区别,以及它们如何整合:

  • A database level ON DELETE cascade is configured effectively on the many-to-one side of the relationship; that is, we configure it relative to the FOREIGN KEY constraint that is the “many” side of a relationship. 在ORM级别,这个方向是相反的SQLAlchemy handles the deletion of “child” objects relative to a “parent” from the “parent” side, which means that delete and delete-orphan cascade are configured on the one-to-many side.

  • Database level foreign keys with no ON DELETE setting are often used to prevent a parent row from being removed, as it would necessarily leave an unhandled related row present. 如果在一对多关系中需要这种行为,则可以通过以下两种方法之一来捕获SQLAlchemy将外键设置为NULL的默认行为:

    • 最简单也是最常见的就是在数据库模式级别设置外键保存列为NOT NULLSQLAlchemy尝试将列设置为NULL将失败,并带有一个简单的NOT NULL约束异常。
    • 另一种更特殊的方式是将passive_deletes标志设置为字符串"all"这具有完全禁用SQLAlchemy将外键列设置为NULL的行为的效果,并且对父行发出DELETE,而不会影响子行,即使子行存在于内存中也是如此。This may be desirable in the case when database-level foreign key triggers, either special ON DELETE settings or otherwise, need to be activated in all cases when a parent row is deleted.
  • Database level ON DELETE cascade is vastly more efficient than that of SQLAlchemy. 数据库可以同时链接许多关系的一系列级联操作;例如如果行A被删除,则表B中的所有相关行都可以被删除,并且所有与这些B行中的每一行相关的C行,以及on和on都在单个DELETE语句的范围内。另一方面,SQLAlchemy为了完全支持级联删除操作,必须单独加载每个相关集合,以便定位所有可能具有更多相关集合的行。也就是说,SQLAlchemy不够复杂,无法在此上下文中一次为所有相关行发出DELETE。

  • SQLAlchemy doesn’t need to be this sophisticated, as we instead provide smooth integration with the database’s own ON DELETE functionality, by using the passive_deletes option in conjunction with properly configured foreign key constraints. 在这种情况下,SQLAlchemy只会为那些本地已存在于Session中的行发出DELETE;对于任何被卸载的集合,它都将它们留给数据库来处理,而不是为它们发出一个SELECT。Using Passive Deletes提供了这个用法的一个例子。

  • While database-level ON DELETE functionality works only on the “many” side of a relationship, SQLAlchemy’s “delete” cascade has limited ability to operate in the reverse direction as well, meaning it can be configured on the “many” side to delete an object on the “one” side when the reference on the “many” side is deleted. 然而,如果还有其他的对象是从“many”引用这个“one”一侧的,那么这很容易导致违反约束,所以它通常只在关系实际上是“一对一”时才有用。应该使用single_parent标志为这种情况建立一个Python内断言。

当使用relationship()(也包含使用secondary选项的多对多表)时,SQLAlchemy的删除级联将自动处理多对多表中的行。就像在Deleting Rows from the Many to Many Table中所描述的那样,从多对多集合中添加或删除一个对象会导致INSERT或DELETE一行中的many-当由于父对象删除操作而激活时,delete级联将不仅删除“子”表中的行,还删除多对多表中的行。

删除-孤儿¶ T0>

delete-orphan cascade adds behavior to the delete cascade, such that a child object will be marked for deletion when it is de-associated from the parent, not just when the parent is marked for deletion. 当处理一个由它的父“拥有”的相关对象时,这是一个常见的特性,它带有一个NOT NULL外键,所以从父集合中删除这个项目会导致它被删除。

delete-orphan cascade implies that each child object can only have one parent at a time, so is configured in the vast majority of cases on a one-to-many relationship. 将其设置为多对一或多对多的关系更为尴尬;对于这个用例,SQLAlchemy要求使用single_parent参数配置relationship(),建立Python端验证,确保对象仅与时间。

合并¶ T0>

merge cascade指示Session.merge()操作应该从作为Session.merge()调用对象的父级传播提交对象。这个级联也是默认的。

刷新-到期¶ T0>

refresh-expire是一个不常见的选项,指示Session.expire()操作应该从父级传播到引用的对象。当使用Session.refresh()时,被引用的对象只是过期的,但实际上并没有刷新。

抹去¶ T0>

expunge cascade indicates that when the parent object is removed from the Session using Session.expunge(), the operation should be propagated down to referred objects.

控制Backrefs上的级联

缺省情况下,save-update级联发生在从backrefs发出的属性更改事件上。这可能是通过示范更容易描述的令人困惑的陈述;这意味着,给定一个这样的映射:

mapper(Order, order_table, properties={
    'items' : relationship(Item, backref='order')
})

If an Order is already in the session, and is assigned to the order attribute of an Item, the backref appends the Item to the items collection of that Order, resulting in the save-update cascade taking place:

>>> o1 = Order()
>>> session.add(o1)
>>> o1 in session
True

>>> i1 = Item()
>>> i1.order = o1
>>> i1 in o1.items
True
>>> i1 in session
True

可以使用cascade_backrefs标志禁用此行为:

mapper(Order, order_table, properties={
    'items' : relationship(Item, backref='order',
                                cascade_backrefs=False)
})

如上所述,i1.order = o1的赋值将会附加i1 o1items集合,但不会将i1添加到会话中。You can, of course, add() i1 to the session at a later point. 这个选项可能对于一个对象需要被保留在一个会话之外,直到它的构造完成,但是仍然需要给目标会话中已经持久化的对象给予关联。