CODEWITHSUNDEEP
pythonsqlalchemy
tonio
tonio

Reputation: 2376

Deletion behavior with relationship

This isn’t trully a problem, I just want to understand. Considering the following code:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import *
from sqlalchemy.orm import sessionmaker, relationship
Base = declarative_base()

class AB(Base):
    __tablename__= 'ab'
    id_a = Column(Integer, ForeignKey('a.id', ondelete='CASCADE'), primary_key=True)
    id_b = Column(Integer, ForeignKey('b.id', ondelete='CASCADE'), primary_key=True)
    rel = Column(Unicode)

class A(Base):
    __tablename__ = 'a'
    id = Column(Integer, primary_key=True)

class B(Base):
    __tablename__ = 'b'
    id = Column(Integer, primary_key=True)
    #1: doesn’t work try to set id_b to null
    rel_a = relationship('AB')
    # Works, but cascade='all' seems uneeded to me
    rel_a = relationship('AB', cascade='all')
    # Works 
    rel_a = relationship('AB', passive_deletes=True)

engine = create_engine('sqlite://', echo=True)

import logging
logger = logging.getLogger('sqlalchemy.engine.base.Engine')
logger.setLevel(logging.DEBUG)
handler = logger.handlers[0]
handler.setLevel(logging.DEBUG)
handler.setFormatter(logging.Formatter('%(levelname)s %(message)s', ''))

Base.metadata.create_all(engine)

sess = sessionmaker(engine)()

a1 = A()
b1 = B()
ab = AB()

sess.add_all([a1,b1])
sess.flush()

ab.id_a = a1.id
ab.id_b = b1.id
ab.rel = u'truite'
sess.add(ab)
sess.flush()
sess.delete(b1)
sess.flush()

I want records from AB table to be removed when related records from B are removed. I tried 3 types of relations (check in B table):

Leaving (3) apart, I don’t get why (2) is needed, because the ondelete='cascade' is already set, and generated DB is identical. My guess would be with (1), SQLAlchemy has enough information to has the correct behavior.

Am I missing something? Thanks.

Upvotes: 7

Views: 5161

Answers (1)

Xion
Xion

Reputation: 22770

cascade on relationship configures the cascades of Session operations, such as Session.delete. It's independent from any ON X CASCADE directives you may have on your foreign keys constraints in the database itself.

In your case, having cascade='all' tells SQLAlchemy to cascade the Session.delete (among other operations) from the parent object (AB) to the child object. Without it, the default mode of operation is to put NULL into the foreign key column and let the referenced object be.

On the other hand, passive_deletes=True instructs SQLAlchemy to rely on database to cleanup deleted objects through the ON DELETE CASCADE directives. This prevents SQLAlchemy from issuing the DELETE query by itself, as it would do in the relationship(cascade=...) case.

Upvotes: 10

Related Questions