SQLAlchemy 1.4 Documentation
SQLAlchemy ORM
- ORM Quick Start
- Object Relational Tutorial (1.x API)
- ORM Mapped Class Configuration
- Relationship Configuration
- Querying Data, Loading Objects
- Using the Session
- Session Basics
- State Management
- Cascades¶
- Transactions and Connection Management
- Additional Persistence Techniques
- Contextual/Thread-local Sessions
- Tracking queries, object and Session Changes with Events
- Session API
- Events and Internals
- ORM Extensions
- ORM Examples
Project Versions
Cascades¶
Mappers support the concept of configurable cascade behavior on
relationship()
constructs. This refers
to how operations performed on a “parent” object relative to a
particular Session
should be propagated to items
referred to by that relationship (e.g. “child” objects), and is
affected by the relationship.cascade
option.
The default behavior of cascade is limited to cascades of the so-called save-update and merge settings. The typical “alternative” setting for cascade is to add the delete and delete-orphan options; these settings are appropriate for related objects which only exist as long as they are attached to their parent, and are otherwise deleted.
Cascade behavior is configured using the
relationship.cascade
option on
relationship()
:
class Order(Base):
__tablename__ = "order"
items = relationship("Item", cascade="all, delete-orphan")
customer = relationship("User", cascade="save-update")
To set cascades on a backref, the same flag can be used with the
backref()
function, which ultimately feeds
its arguments back into relationship()
:
class Item(Base):
__tablename__ = "item"
order = relationship(
"Order", backref=backref("items", cascade="all, delete-orphan")
)
The default value of relationship.cascade
is save-update, merge
.
The typical alternative setting for this parameter is either
all
or more commonly all, delete-orphan
. The 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.
Warning
The all
cascade option implies the
refresh-expire
cascade setting which may not be desirable when using the
Asynchronous I/O (asyncio) extension, as it will expire related objects
more aggressively than is typically appropriate in an explicit IO context.
See the notes at Preventing Implicit IO when Using AsyncSession for further background.
The list of available values which can be specified for
the relationship.cascade
parameter are described in the following subsections.
save-update¶
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
. Suppose we have an object user1
with two
related objects address1
, address2
:
>>> 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
cascade also affects attribute operations for objects
that are already present in a 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.addresses.append(address3)
>>> address3 in sess
True
A save-update
cascade can exhibit surprising behavior when removing an item from
a collection or de-associating an object from a scalar attribute. In some cases, the
orphaned objects may still be pulled into the ex-parent’s Session
; this is
so that the flush process may handle that related object appropriately.
This case usually only arises if an object is removed from one Session
and added to another:
>>> 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
The save-update
cascade is on by default, and is typically taken
for granted; it simplifies code by allowing a single call to
Session.add()
to register an entire structure of objects within
that Session
at once. While it can be disabled, there
is usually not a need to do so.
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 relationship.cascade_backrefs
flag,
is discussed in the section Controlling Cascade on Backrefs.
delete¶
The delete
cascade indicates that when a “parent” object
is marked for deletion, its related “child” objects should also be marked
for deletion. If for example we have a relationship User.addresses
with delete
cascade configured:
class User(Base):
# ...
addresses = relationship("Address", cascade="all, delete")
If using the above mapping, we have a User
object and two
related Address
objects:
>>> 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
. Using a mapping as follows:
class User(Base):
# ...
addresses = relationship("Address")
Upon deletion of a parent User
object, the rows in address
are not
deleted, but are instead de-associated:
>>> 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 on one-to-many relationships is often combined
with delete-orphan cascade, which will emit a DELETE for the
related row if the “child” object is deassociated from the parent. The
combination of delete
and delete-orphan
cascade covers both
situations where SQLAlchemy has to decide between setting a foreign key
column to NULL versus deleting the row entirely.
The feature by default works completely independently of database-configured
FOREIGN KEY
constraints that may themselves configure CASCADE
behavior.
In order to integrate more efficiently with this configuration, additional
directives described at Using foreign key ON DELETE cascade with ORM relationships should be used.
See also
Using foreign key ON DELETE cascade with ORM relationships
Using delete cascade with many-to-many relationships¶
The cascade="all, delete"
option works equally well with a many-to-many
relationship, one that uses relationship.secondary
to
indicate an association table. When a parent object is deleted, and therefore
de-associated with its related objects, the unit of work process will normally
delete rows from the association table, but leave the related objects intact.
When combined with cascade="all, delete"
, additional DELETE
statements
will take place for the child rows themselves.
The following example adapts that of Many To Many to
illustrate the cascade="all, delete"
setting on one side of the
association:
association_table = Table(
"association",
Base.metadata,
Column("left_id", Integer, ForeignKey("left.id")),
Column("right_id", Integer, ForeignKey("right.id")),
)
class Parent(Base):
__tablename__ = "left"
id = Column(Integer, primary_key=True)
children = relationship(
"Child",
secondary=association_table,
back_populates="parents",
cascade="all, delete",
)
class Child(Base):
__tablename__ = "right"
id = Column(Integer, primary_key=True)
parents = relationship(
"Parent",
secondary=association_table,
back_populates="children",
)
Above, when a Parent
object is marked for deletion
using Session.delete()
, the flush process will as usual delete
the associated rows from the association
table, however per cascade
rules it will also delete all related Child
rows.
Warning
If the above cascade="all, delete"
setting were configured on both
relationships, then the cascade action would continue cascading through all
Parent
and Child
objects, loading each children
and parents
collection encountered and deleting everything that’s connected. It is
typically not desirable for “delete” cascade to be configured
bidirectionally.
Using foreign key ON DELETE cascade with ORM relationships¶
The behavior of SQLAlchemy’s “delete” cascade overlaps with the
ON DELETE
feature of a database FOREIGN KEY
constraint.
SQLAlchemy allows configuration of these schema-level DDL behaviors
using the ForeignKey
and ForeignKeyConstraint
constructs; usage of these objects in conjunction with Table
metadata is described at ON UPDATE and ON DELETE.
In order to use ON DELETE
foreign key cascades in conjunction with
relationship()
, it’s important to note first and foremost that the
relationship.cascade
setting must still be configured to
match the desired “delete” or “set null” behavior (using delete
cascade
or leaving it omitted), so that whether the ORM or the database
level constraints will handle the task of actually modifying the data in the
database, the ORM will still be able to appropriately track the state of
locally present objects that may be affected.
There is then an additional option on relationship()
which
indicates the degree to which the ORM should try to run DELETE/UPDATE
operations on related rows itself, vs. how much it should rely upon expecting
the database-side FOREIGN KEY constraint cascade to handle the task; this is
the relationship.passive_deletes
parameter and it accepts
options False
(the default), True
and "all"
.
The most typical example is that where child rows are to be deleted when
parent rows are deleted, and that ON DELETE CASCADE
is configured
on the relevant FOREIGN KEY
constraint as well:
class Parent(Base):
__tablename__ = "parent"
id = Column(Integer, primary_key=True)
children = relationship(
"Child",
back_populates="parent",
cascade="all, delete",
passive_deletes=True,
)
class Child(Base):
__tablename__ = "child"
id = Column(Integer, primary_key=True)
parent_id = Column(Integer, ForeignKey("parent.id", ondelete="CASCADE"))
parent = relationship("Parent", back_populates="children")
The behavior of the above configuration when a parent row is deleted is as follows:
The application calls
session.delete(my_parent)
, wheremy_parent
is an instance ofParent
.When the
Session
next flushes changes to the database, all of the currently loaded items within themy_parent.children
collection are deleted by the ORM, meaning aDELETE
statement is emitted for each record.If the
my_parent.children
collection is unloaded, then noDELETE
statements are emitted. If therelationship.passive_deletes
flag were not set on thisrelationship()
, then aSELECT
statement for unloadedChild
objects would have been emitted.A
DELETE
statement is then emitted for themy_parent
row itself.The database-level
ON DELETE CASCADE
setting ensures that all rows inchild
which refer to the affected row inparent
are also deleted.The
Parent
instance referred to bymy_parent
, as well as all instances ofChild
that were related to this object and were loaded (i.e. step 2 above took place), are de-associated from theSession
.
Note
To use “ON DELETE CASCADE”, the underlying database engine must
support FOREIGN KEY
constraints and they must be enforcing:
When using MySQL, an appropriate storage engine must be selected. See CREATE TABLE arguments including Storage Engines for details.
When using SQLite, foreign key support must be enabled explicitly. See Foreign Key Support for details.
Using foreign key ON DELETE with many-to-many relationships¶
As described at Using delete cascade with many-to-many relationships, “delete” cascade works
for many-to-many relationships as well. To make use of ON DELETE CASCADE
foreign keys in conjunction with many to many, FOREIGN KEY
directives
are configured on the association table. These directives can handle
the task of automatically deleting from the association table, but cannot
accommodate the automatic deletion of the related objects themselves.
In this case, the relationship.passive_deletes
directive can
save us some additional SELECT
statements during a delete operation but
there are still some collections that the ORM will continue to load, in order
to locate affected child objects and handle them correctly.
Note
Hypothetical optimizations to this could include a single DELETE
statement against all parent-associated rows of the association table at
once, then use RETURNING
to locate affected related child rows, however
this is not currently part of the ORM unit of work implementation.
In this configuration, we configure ON DELETE CASCADE
on both foreign key
constraints of the association table. We configure cascade="all, delete"
on the parent->child side of the relationship, and we can then configure
passive_deletes=True
on the other side of the bidirectional
relationship as illustrated below:
association_table = Table(
"association",
Base.metadata,
Column("left_id", Integer, ForeignKey("left.id", ondelete="CASCADE")),
Column("right_id", Integer, ForeignKey("right.id", ondelete="CASCADE")),
)
class Parent(Base):
__tablename__ = "left"
id = Column(Integer, primary_key=True)
children = relationship(
"Child",
secondary=association_table,
back_populates="parents",
cascade="all, delete",
)
class Child(Base):
__tablename__ = "right"
id = Column(Integer, primary_key=True)
parents = relationship(
"Parent",
secondary=association_table,
back_populates="children",
passive_deletes=True,
)
Using the above configuration, the deletion of a Parent
object proceeds
as follows:
A
Parent
object is marked for deletion usingSession.delete()
.When the flush occurs, if the
Parent.children
collection is not loaded, the ORM will first emit a SELECT statement in order to load theChild
objects that correspond toParent.children
.It will then then emit
DELETE
statements for the rows inassociation
which correspond to that parent row.for each
Child
object affected by this immediate deletion, becausepassive_deletes=True
is configured, the unit of work will not need to try to emit SELECT statements for eachChild.parents
collection as it is assumed the corresponding rows inassociation
will be deleted.DELETE
statements are then emitted for eachChild
object that was loaded fromParent.children
.
delete-orphan¶
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. This is a common feature when dealing with a related
object that is “owned” by its parent, with a NOT NULL foreign key,
so that removal of the item from the parent collection results
in its deletion.
delete-orphan
cascade implies that each child object can only
have one parent at a time, and in the vast majority of cases is configured
only on a one-to-many relationship. For the much less common
case of setting it on a many-to-one or
many-to-many relationship, the “many” side can be forced to allow only
a single object at a time by configuring the relationship.single_parent
argument,
which establishes Python-side validation that ensures the object
is associated with only one parent at a time, however this greatly limits
the functionality of the “many” relationship and is usually not what’s
desired.
See also
For relationship <relationship>, delete-orphan cascade is normally configured only on the “one” side of a one-to-many relationship, and not on the “many” side of a many-to-one or many-to-many relationship. - background on a common error scenario involving delete-orphan cascade.
merge¶
merge
cascade indicates that the Session.merge()
operation should be propagated from a parent that’s the subject
of the Session.merge()
call down to referred objects.
This cascade is also on by default.
refresh-expire¶
refresh-expire
is an uncommon option, indicating that the
Session.expire()
operation should be propagated from a parent
down to referred objects. When using Session.refresh()
,
the referred objects are expired only, but not actually refreshed.
expunge¶
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.
Controlling Cascade on Backrefs¶
Note
This section applies to a behavior that is removed in SQLAlchemy 2.0.
By setting the Session.future
flag on a given
Session
, the 2.0 behavior will be achieved which is
essentially that the relationship.cascade_backrefs
flag is
ignored. See the section cascade_backrefs behavior deprecated for removal in 2.0 for notes.
In 1.x style ORM usage, the save-update cascade by default takes place on attribute change events emitted from backrefs. This is probably a confusing statement more easily described through demonstration; it means that, given a mapping such as this:
mapper_registry.map_imperatively(
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
This behavior can be disabled using the relationship.cascade_backrefs
flag:
mapper_registry.map_imperatively(
Order,
order_table,
properties={"items": relationship(Item, backref="order", cascade_backrefs=False)},
)
So above, the assignment of i1.order = o1
will append i1
to the items
collection of o1
, but will not add i1
to the session. You can, of
course, Session.add()
i1
to the session at a later point. This
option may be helpful for situations where an object needs to be kept out of a
session until it’s construction is completed, but still needs to be given
associations to objects which are already persistent in the target session.
When relationships are created by the relationship.backref
parameter on relationship()
, the sqlalchemy.orm.cascade_backrefs
parameter may be set to False
on the backref side by using the
backref()
function instead of a string. For example, the above relationship
could be declared:
mapper_registry.map_imperatively(
Order,
order_table,
properties={
"items": relationship(
Item,
backref=backref("order", cascade_backrefs=False),
cascade_backrefs=False,
)
},
)
This sets the cascade_backrefs=False
behavior on both relationships.
Notes on Delete - Deleting Objects Referenced from Collections and Scalar Relationships¶
The ORM in general never modifies the contents of a collection or scalar
relationship during the flush process. This means, if your class has a
relationship()
that refers to a collection of objects, or a reference
to a single object such as many-to-one, the contents of this attribute will
not be modified when the flush process occurs. Instead, it is expected
that the Session
would eventually be expired, either through the expire-on-commit behavior of
Session.commit()
or through explicit use of Session.expire()
.
At that point, any referenced object or collection associated with that
Session
will be cleared and will re-load itself upon next access.
A common confusion that arises regarding this behavior involves the use of the
Session.delete()
method. When Session.delete()
is invoked upon
an object and the Session
is flushed, the row is deleted from the
database. Rows that refer to the target row via foreign key, assuming they
are tracked using a relationship()
between the two mapped object types,
will also see their foreign key attributes UPDATED to null, or if delete
cascade is set up, the related rows will be deleted as well. However, even
though rows related to the deleted object might be themselves modified as well,
no changes occur to relationship-bound collections or object references on
the objects involved in the operation within the scope of the flush
itself. This means if the object was a
member of a related collection, it will still be present on the Python side
until that collection is expired. Similarly, if the object were
referenced via many-to-one or one-to-one from another object, that reference
will remain present on that object until the object is expired as well.
Below, we illustrate that after an Address
object is marked
for deletion, it’s still present in the collection associated with the
parent User
, even after a flush:
>>> address = user.addresses[1]
>>> session.delete(address)
>>> session.flush()
>>> address in user.addresses
True
When the above session is committed, all attributes are expired. The next
access of user.addresses
will re-load the collection, revealing the
desired state:
>>> session.commit()
>>> address in user.addresses
False
There is a recipe for intercepting Session.delete()
and invoking this
expiration automatically; see ExpireRelationshipOnFKChange for this. However, the usual practice of
deleting items within collections is to forego the usage of
Session.delete()
directly, and instead use cascade behavior to
automatically invoke the deletion as a result of removing the object from the
parent collection. The delete-orphan
cascade accomplishes this, as
illustrated in the example below:
class User(Base):
__tablename__ = "user"
# ...
addresses = relationship("Address", cascade="all, delete-orphan")
# ...
del user.addresses[1]
session.flush()
Where above, upon removing the Address
object from the User.addresses
collection, the delete-orphan
cascade has the effect of marking the Address
object for deletion in the same way as passing it to Session.delete()
.
The delete-orphan
cascade can also be applied to a many-to-one
or one-to-one relationship, so that when an object is de-associated from its
parent, it is also automatically marked for deletion. Using delete-orphan
cascade on a many-to-one or one-to-one requires an additional flag
relationship.single_parent
which invokes an assertion
that this related object is not to shared with any other parent simultaneously:
class User(Base):
# ...
preference = relationship(
"Preference", cascade="all, delete-orphan", single_parent=True
)
Above, if a hypothetical Preference
object is removed from a User
,
it will be deleted on flush:
some_user.preference = None
session.flush() # will delete the Preference object
See also
Cascades for detail on cascades.
flambé! the dragon and The Alchemist image designs created and generously donated by Rotem Yaari.
Created using Sphinx 7.2.6. Documentation last generated: Wed 30 Oct 2024 02:18:58 PM EDT