.relationship(…, cascade="all") does not delete children when multiple parents "bulk" deleted using session.execute(delete(Parent))

Question

I worte some code in Python for managing some database stuff. All I want to do, is to delete all parents of a 1-to-many relationsship , which should automatically delete all children. Here is my example code:

from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship, Session
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Parent(Base):
    __tablename__ = 'parents'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    single_parent = True
    children = relationship("Child", back_populates="parent", cascade="all, delete-orphan")

class Child(Base):
    __tablename__ = 'children'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    parent_id = Column(Integer, ForeignKey('parents.id'))
    parent = relationship('Parent', back_populates='children')



engine = create_engine("sqlite:///test.db", echo=True)
Base.metadata.create_all(bind=engine)


session = Session(engine)
# create data
parent1 = Parent(name='Parent 1', children=[Child(name='Child 1'), Child(name='Child 2')])
parent2 = Parent(name='Parent 2', children=[Child(name='Child 3'), Child(name='Child 4')])
session.add_all([parent1, parent2])
session.commit()

# Check
print("Before:")
print(session.query(Parent).all())
print(session.query(Child).all())

# Delete
session.query(Parent).delete()
session.commit()

When running, the parent elements will be deleted but none of the child elements. What am I doing wrong here? Thanks a lot for your help!

Answer 1

You need to modify parent_id declaration:

Replace:

parent_id = Column(Integer, ForeignKey('parents.id'))

With:

parent_id = Column(Integer, ForeignKey('parents.id', ondelete='CASCADE'))

However, it's not sufficient when you use SQLite as database because Foreign Key Constraints are not enabled by default. You can enable Foreign Key Constraint with a PRAGMA directive at database level: (see @GordThompson's comment below)

PRAGMA foreign_keys = ON;

Or you can handle this feature with `SQLAlchemy by listening for events:

from sqlalchemy.event import listens_for
from sqlalchemy.engine import Engine
from sqlite3 import Connection as SQLite3Connection

# enable foreign keys. if you do not do this, ON DELETE CASCADE fails silently!
@listens_for(Engine, 'connect')
def _set_sqlite_pragma(dbapi_connection, connection_record):
    if isinstance(dbapi_connection, SQLite3Connection):
        cursor = dbapi_connection.cursor()
        cursor.execute('PRAGMA foreign_keys=ON;')
        cursor.close()

Test:

# Check
print("Before:")
print(session.query(Parent).all())
print(session.query(Child).all())

# Delete only one parent
session.query(Parent).filter_by(name='Parent 2').delete()
session.commit()

# Check
print("After:")
print(session.query(Parent).all())
print(session.query(Child).all())

Output:

# I just modified the __repr__ of each table

Before:
[Parent(id=1, name='Parent 1'), Parent(id=2, name='Parent 2')]
[Child(id=1, name='Child 1', parent_name='Parent 1'), Child(id=2, name='Child 2', parent_name='Parent 1'), Child(id=3, name='Child 3', parent_name='Parent 2'), Child(id=4, name='Child 4', parent_name='Parent 2')]

After:
[Parent(id=1, name='Parent 1')]
[Child(id=1, name='Child 1', parent_name='Parent 1'), Child(id=2, name='Child 2', parent_name='Parent 1')]