SQLAlchemy Cascade Delete (likely session confusion)

Question

I am trying to cascade a deletion into the children when the parent is deleted. Per this question SQLAlchemy: cascade delete I added the cascade="all, delete-orphan" option into the child's backref. This does seem to work to an extent. The children are in fact deleted from the database (confirmed through SQLlite database browser), but the children is still visible "python side":

CODE:

class Product(Base):
    __tablename__ = 'products'
    id = Column(Integer, primary_key=True)
    product_name = Column(String(250), unique=True)
    vendor_id = Column(Integer, ForeignKey('vendors.id'), nullable=False)

    vendor = relationship('Vendor', backref = backref('products', order_by=id, cascade="all, delete-orphan"))

    def __init__(self, product_name, vendor_id):
        self.product_name = product_name
        self.vendor_id = vendor_id

    def __repr__(self):
        return '<Product: %r Product ID: %r Vendor ID: %r>' % (self.product_name, self.id, self.vendor_id)


class Module(Base):
    __tablename__ = 'modules'
    id = Column(Integer, primary_key=True)
    module_name = Column(String(250), unique=True)
    product_id = Column(Integer, ForeignKey('products.id'), nullable=False)

    product = relationship('Product', backref = backref('modules', order_by=id, cascade="all, delete-orphan"))

    def __init__(self, module_name, product_id):
        self.module_name = module_name
        self.product_id = product_id


    def __repr__(self):
        return '<Module: %r Module ID: %r Product ID: %r>' % (self.module_name, self.id ,self.product_id)

TEST:

msg('Module Tests')
Product2Mod1 = Module('Product2Mod1',1)
Product2Mod2 = Module('Product2Mod2',1)
Product1Mod1 = Module('Product1Mod1',2)
Product1Mod2 = Module('Product1Mod2',2)
Product1Mod3 = Module('Product1Mod3',2)
db_session.add(Product2Mod1)
db_session.add(Product2Mod2)
db_session.add(Product1Mod1)
db_session.add(Product1Mod2)
db_session.add(Product1Mod3)
db_session.commit()
msg("Product2Mod1 Product:")
print Product2Mod1.product

msg('delete tests')

print "Query to show all products: \n"
print Product.query.all()

print "\nQuery to show all modules: \n"
print Module.query.all()

print "\ndeleting product 1: db_session.delete(Product1) --> db_session.commit()"
db_session.delete(Product1)
db_session.commit()
db_session.flush()

print "\nQuery to check for changes with products and modules (THIS IS CORRECT):\n"
print Product.query.all()
print Module.query.all()

print "\nThe modules below belong to the deleted product, they should have disappeared (But they did not, THIS IS INCORRECT):"
print Product1Mod1
print Product1Mod2
print Product1Mod3

score 2 · Accepted Answer · answered Mar 09 '14 at 23:31

Actually, SQLAlchemy behaves correctly here. To understand it you have to take a step away from SQLAlchemy. You tell a library to delete something in its backend. However, you still have references to instances you created yourself and just gave it. Take this artifical example:

some_obj = MyClass()
backend_storage.store(some_obj)
backend_storage.delete(some_obj)

What would you expect to happen with some_obj now in your local space? Should this library just delete your variable?

SQLAlchemy behaves in just this way: It knows these objects are gone and so asking about them won't return them any more. However, you still have them locally and they already exist there, so as long as you have refences to them, they will persist in memory.

One additional thing: It does not matter, if you created these objects yourself or they were returned by the library. They are "yours" right now and now external code should mess with that. For example:

some_obj = backend_storage.load_one(MyClass)  # load the first object of MyClass
backend_storage.delete(some_obj)

This is the same as above: some_obj is yours. You can even re-add it now if you want, the backend won't care.

So does that mean that in SQLalchemy to fully get rid of something I need to do `session.delete(some_obj)` then `session.commit()`and finally `del some_obj`? I'm just surprised the ORM doesn't do that for you... the example here [SQLA example](http://docs.sqlalchemy.org/en/latest/_modules/examples/adjacency_list/adjacency_list.html) seems to imply you do not. — RedRaven, Mar 11 '14 at 20:39
You do not have to delete them explicitly, just stop using them. If you have references stored somewhere (e.g. in a global list of all items), then you have to take care yourself to remove them. Maybe it helps if you think of it similar to a cache: You wouldn't want SQLAlchemy deleting from your cache, do you? Concerning the example: The object being delete is 'node1' and if you look carefully, there is no reference stored to it anywhere. Additionally, SQLAlchemy **does** delete it from its own collection (i.e. `node.children`). — javex, Mar 11 '14 at 21:48

SQLAlchemy Cascade Delete (likely session confusion)

1 Answers1