Cascade Rules in SQLAlchemy

Last night at the PyAtl meeting, there was a question about how you define your cascade rules in SQLAlchemy mappers. I'll confess that it confused me at first, too, but here's all you need to know:

What's "cascading" in the mapper is session-based operations. This includes putting an object into the session (saving it), deleting an object from the session, etc. Generally, you don't care about all that stuff, because it Just Works most of the time, as long as you specify cascade="all" on your relation() properties in your mappers. What this means is "whatever session operation you do to the mapped class, do it to the related class as well".

One little confusing thing is that there's another thing you'll often want to specify in your cascade rules, and that's the "delete-orphan". In fact, most of my 1:N relation()s look like:

mapper(ParentClass, parent, properties=dict(
    children=relation(ChildClass, backref='parent', 
                      cascade='all,delete-orphan')
    )
)
      

The "delete-orphan" specifies that if you ever have a ChildClass instance that is "orphaned", that is, not connected to some ParentClass, go ahead and delete that ChildClass. You want to specify this whenever you don't want ChildClass instances hanging out with null ParentClass references. Note that even if you don't specify "delete-orphan", deletes on the ParentClass instance will still cascade to related ChildClass instances. An example is probably best. Say you have the following schema and mapper setup:

photo = Table(
    'photo', metadata,
    Column('id', Integer, primary_key=True))
tag = Table(
    'tag', metadata,
    Column('id', Integer, primary_key=True),
    Column('photo_id', None, ForeignKey('photo.id')),
    Column('tag', String(80)))

class Photo(object): pass
        
class Tag(object): pass
        
session.mapper(Photo, photo, properties=dict(
    tags=relation(Tag, backref='photo', cascade="all"),
    session.mapper(Tag, tag)
      

I'll go ahead and create some photos and tags:

p1 = Photo(tags=[
    Tag(tag='foo'),
    Tag(tag='bar'),
    Tag(tag='baz') ])
p2 = Photo(tags=[
    Tag(tag='foo'),
    Tag(tag='bar'),
    Tag(tag='baz') ])
session.flush()
session.clear()
      

Now if I delete one of the photos, I'll delete the tags associated
with it, as well:

>>> for t in Tag.query():
...     print t.id, t.photo_id, t.tag
... 
1 1 foo
2 1 bar
3 1 baz
4 2 foo
5 2 bar
6 2 baz
>>> session.delete(Photo.query.get(1))
>>> session.flush()
>>> for t in Tag.query():
...     print t.id, t.photo_id, t.tag
... 
4 2 foo
5 2 bar
6 2 baz
      

At this point, everything is the same whether I specify
"delete-orphan" or not. The difference is in what happens when I
just remove an item from a photo's "tags" collection:

>>> p2 = Photo.query.get(2)
>>> del p2.tags[0]
>>> session.flush()
>>> for t in Tag.query():
...     print t.id, t.photo_id, t.tag
... 
4 None foo
5 2 bar
6 2 baz
       

See how the "foo" tag is just hanging out there with no photo?
That's what "delete-orphan" is designed to prevent. If we'd
specified "delete-orphan", we'd have the following result:

>>> p2 = Photo.query.get(2)
>>> del p2.tags[0]
>>> session.flush()
>>> for t in Tag.query():
...     print t.id, t.photo_id, t.tag
... 
5 2 bar
6 2 baz
       

So there you go. If you don't mind orphans, then use
cascade="all" and leave off the
"delete-orphan". If you'd rather have them disappear when
disconnected from their parent, use
cascade="all,delete-orphan".