CODEWITHSUNDEEP
pythonmysqlsqlalchemy
Brian Leach
Brian Leach

Reputation: 4154

SQLAlchemy delete cascade in both directions (without the ORM)

I have some models defined using SQLAlchemy's ORM

class HasId(object):
    @declared_attr
    def id(cls):
        return Column('id', Integer, Sequence('test_id_seq'), primary_key=True)
    ...

class TestParent(HasId, Model):
    __tablename__ = 'tests'
    discriminator = Column(String(50))
    __mapper_args__ = {'polymorphic_on': discriminator}
    ...


class FooTest(TestParent):
    __tablename__ = 'footests'
    __mapper_args__ = {'polymorphic_identity': 'footests'}
    id = Column(Integer, ForeignKey('tests.id'), primary_key=True)
    parent_id = Column(Integer, ForeignKey('footests.id', ondelete='CASCADE'))
    children = relationship('FooTest',
                            foreign_keys='FooTest.parent_id',
                            lazy='joined',
                            join_depth=2,
                            cascade='save-update, merge, delete, delete-orphan')
    ...

When I delete a row from tests (The TestParent model) when not using the ORM, the corresponding row in footests gets deleted. All good.

I would like to be able to delete a row from footests outside of the ORM and have the corresponding row in tests to be deleted. This is essentially getting the cascade to work in the opposite direction.

Is this possible outside of the ORM? Is there another way to think about this? (database engine is MySQL / Mariadb)

Upvotes: 0

Views: 294

Answers (1)

univerio
univerio

Reputation: 20498

This should just work if you add/delete with the ORM:

>>> foo = FooTest()
>>> session.add(foo)
>>> session.flush()
>>> list(session.execute("SELECT * FROM tests;"))
[(u'footests', 1)]
>>> list(session.execute("SELECT * FROM footests;"))
[(1, None)]
>>> session.delete(foo)
>>> session.flush()
>>> list(session.execute("SELECT * FROM tests;"))
[]
>>> list(session.execute("SELECT * FROM footests;"))
[]

Upvotes: 1

Related Questions