How to fix SQLAlchemy: SAWarning: DELETE statement on table expected to delete 1 row(s); 0 were matched

Question

I am building a Python Flask app with a directed graph data model using SQLAlchemy with PostgreSQL. I am having trouble setting up the delete cascade. While deleting appears to work given rudimentary inspection, I am unsure if I might be corrupting things in ways that I do not understand, because I get the following warning:

SAWarning: DELETE statement on table 'edges' expected to delete 1 row(s); 0 were matched.  
Please set confirm_deleted_rows=False within the mapper configuration to prevent this warning.  (table.description, expected,rows_matched)

Here is the core of my data model.

class Node(db.Model):
    __tablename__ = 'nodes'

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

    id = Column(BigInteger, primary_key=True)
    type = Column(String)

    in_edges = relationship("Edge", cascade="save-update, merge, delete", foreign_keys="Edge.dest", back_populates='dest_node')
    out_edges = relationship("Edge", cascade="save-update, merge, delete", foreign_keys="Edge.src", back_populates='src_node')


class Edge(db.Model):
    __tablename__ = 'edges'

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

    type = Column(String, primary_key="True")

    src = Column(Integer, ForeignKey('nodes.id'), primary_key=True)
    dest = Column(Integer, ForeignKey('nodes.id'), primary_key=True)

    src_node = relationship("Node", foreign_keys=[src], back_populates='out_edges')
    dest_node = relationship("Node", foreign_keys=[dest], back_populates='in_edges')

Nodes are connected by Edges in a directed way. An Edge's connects a src Node to a dest Node. One thing to note is that a Node has a primary key of id, while an Edge has a composite primary key of its type, src and dest. Since you can't have an Edge with src or dest set to NULL, if a Node is deleted, then all Edges that refer to it in any way must be deleted too.

In the model I have created SQLAlchemy relationships on both Node and Edge which populate each other. On the Node side, I can access incoming and outgoing Edges, and an Edge can access the Nodes it connects.

What I'm trying to do is to delete a subset of the graph. To do to this, I'm walking the the graph by starting at a Node and traversing the Node's out_edges. I am only explicitly deleting the Nodes. I am relying on the cascade delete that I've configured on both of the Node's relationships to take care of deleting the Edges.

When configured as shown, I always seem to get the warning, which seems to indicate that something is not being deleted. However, if I remove the cascade delete on the Node's in_edges, the warning goes away.

I think what might be happening is that, since an Edge must have both Nodes present, when I delete the src Node, it deletes the outgoing Edges and then when I delete those Edge's dest Node, it looks for the incoming Edge, but it's already been deleted.

So removing cascades fixes the warning and works in some situations, but there are times when I'm deleting a Node that has an incoming Edge whose src Node is not one I am visiting on my walk. Therefore it's critcal to make sure that both relationships cause a delete cascade and so I don't think suppressing the warning in this way is the right thing to do.

Can anyone explain what's happening? Is this safe to ignore? Am I doing it wrong?

Answer 1

I think your problem is related https://bitbucket.org/zzzeek/sqlalchemy/issues/2403/work-around-mysql-innodb-bug-need-per

You can try this code.

class Edge(db.Model):
    __tablename__ = 'edges'

    __mapper_args__ = {
        'polymorphic_on': type,
        'polymorphic_identity': 'edge',
        'confirm_deleted_rows': False
    }