CODEWITHSUNDEEP
pythonsqlitesqlalchemy
RedRaven
RedRaven

Reputation: 735

SQLAlchemy Cascade Delete (likely session confusion)

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

Upvotes: 1

Views: 368

Answers (1)

javex
javex

Reputation: 7544

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.

Upvotes: 2

Related Questions