CODEWITHSUNDEEP
pythonsqliteinheritancesqlalchemycascade
Niel
Niel

Reputation: 2006

Deleting from self-referential inherited objects does not cascade in SQLAlchemy-SQLite

I posted a question previously here where I tried to build a hierarchy using different objects. Each object can have any type of object as it's parent, and any type as children. I solved it by using the Node class suggested by SQLAlchemy here and letting the other objects inherit from it.

Now I'm having the problem that deleting a node does not delete it's children. I have tried a lot of solutions like different cascade settings, using ondelete='CASCADE' in the foreignkey, as well as DBSession.execute('pragma foreign_keys=on') but none are working. I think the problem is in the ParentID key because in the child it is not null when the parent is delete.

I'm pretty new to SQLAlchemy so I'm not at all sure where I'm going wrong, any help would be appreciated.

These are my current models:

DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension()))
# DBSession.execute('pragma foreign_keys=on')
Base = declarative_base()

class Node(Base):
    def getID():
        return uuid.uuid1().hex

    __tablename__ = 'Node'
    ID = Column(Text, primary_key=True, default=getID)
    ParentID = Column(Text, ForeignKey('Node.ID', ondelete='CASCADE'))
    type = Column(Text(50))

    Children = relationship("Node",
                backref=backref('Parent',
                                remote_side=[ID]
                                ),
                single_parent=True,
                cascade="all, delete, delete-orphan",
                passive_deletes = True
            )   
    __mapper_args__ = {
        'polymorphic_identity':'Node',
        'polymorphic_on':type
            }

class A(Node):
    __tablename__ = 'A'
    ID = Column(Text, ForeignKey('Node.ID', ondelete='CASCADE'), primary_key=True)
    Name = Column(Text)
    Description = Column(Text)

    __mapper_args__ = {'polymorphic_identity':'A'}


class B(Node):
    __tablename__ = 'B'
    ID = Column(Text, ForeignKey('Node.ID', ondelete='CASCADE'), primary_key=True)
    Name = Column(Text)
    Description = Column(Text)

    __mapper_args__ = {'polymorphic_identity':'B'}


class C(Node):
    __tablename__ = 'C'
    ID = Column(Text, ForeignKey('Node.ID', ondelete='CASCADE'), primary_key=True)
    Name = Column(Text)
    Description = Column(Text)
    Quantity = Column(Integer)
    Rate = Column(Integer)

    __mapper_args__ = {'polymorphic_identity':'C' }

This is how I build a hierarchy:

a = A(Name="PName",
      Description="PDesc",
      ParentID='0')

b = B(Name="BGName",
      Description="BGDesc",
      ParentID=project.ID)

c = C(Name="BIName",
      Description="BIDesc",
      Quantity=10,
      Rate=5,
      ParentID=budgetgroup.ID)

# Append the children nodes to their parents
b.Children.append(c)
a.Children.append(b)
DBSession.add(a)

And this is how I delete it:

def deleteitem(id):
        deleteid = id

        deletethis = DBSession.query(Node).filter_by(ID=deleteid).first()
        qry = DBSession.delete(deletethis)
        # qry = DBSession.query(Node).filter_by(ID=deleteid).delete(
        #             synchronize_session='fetch')
        transaction.commit()

Note: neither the one way or the other commented out delete cascades.

Upvotes: 0

Views: 1230

Answers (1)

Niel
Niel

Reputation: 2006

I was able to find a solution from this answer here.

Now my Node class looks as follows:

class Node(Base):

    __tablename__ = 'Node'
    ID = Column(Integer, primary_key=True)
    ParentID = Column(Integer, ForeignKey('Node.ID', ondelete='CASCADE'))
    type = Column(Text(50))

    Children = relationship(
                'Node',
                cascade="all",
                backref=backref("Parent", remote_side='Node.ID'),
            )

    __mapper_args__ = {
                'polymorphic_identity':'Node',
                'polymorphic_on':type
            }

And this seems to work, all my deletes are cascading.

Upvotes: 3

Related Questions