Fix Python – SQLAlchemy: cascade delete

Question 1

I must be missing something trivial with SQLAlchemy’s cascade options because I cannot get a simple cascade delete to operate correctly — if a parent element is a deleted, the children persist, with null foreign keys.

I’ve put a concise test case here:

from sqlalchemy import Column, Integer, ForeignKey
from sqlalchemy.orm import relationship

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Parent(Base):
    __tablename__ = "parent"
    id = Column(Integer, primary_key = True)

class Child(Base):
    __tablename__ = "child"
    id = Column(Integer, primary_key = True)
    parentid = Column(Integer, ForeignKey(Parent.id))
    parent = relationship(Parent, cascade = "all,delete", backref = "children")

engine = create_engine("sqlite:///:memory:")
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)

session = Session()

parent = Parent()
parent.children.append(Child())
parent.children.append(Child())
parent.children.append(Child())

session.add(parent)
session.commit()

print "Before delete, children = {0}".format(session.query(Child).count())
print "Before delete, parent = {0}".format(session.query(Parent).count())

session.delete(parent)
session.commit()

print "After delete, children = {0}".format(session.query(Child).count())
print "After delete parent = {0}".format(session.query(Parent).count())

session.close()

Output:

Before delete, children = 3
Before delete, parent = 1
After delete, children = 3
After delete parent = 0

There is a simple, one-to-many relationship between Parent and Child. The script creates a parent, adds 3 children, then commits. Next, it deletes the parent, but the children persist. Why? How do I make the children cascade delete?

Question 2

The problem is that sqlalchemy considers Child as the parent, because that is where you defined your relationship (it doesn’t care that you called it “Child” of course).

If you define the relationship on the Parent class instead, it will work:

children = relationship("Child", cascade="all,delete", backref="parent")

(note "Child" as a string: this is allowed when using the declarative style, so that you are able to refer to a class that is not yet defined)

You might want to add delete-orphan as well (delete causes children to be deleted when the parent gets deleted, delete-orphan also deletes any children that were “removed” from the parent, even if the parent is not deleted)

EDIT: just found out: if you really want to define the relationship on the Child class, you can do so, but you will have to define the cascade on the backref (by creating the backref explicitly), like this:

parent = relationship(Parent, backref=backref("children", cascade="all,delete"))

(implying from sqlalchemy.orm import backref)

Question

Answer