CODEWITHSUNDEEP
pythonsqlormsqlalchemycascade
julx
julx

Reputation: 9091

Stop all on delete cascading in SQLAlchemy

Trying to wrap my head around the following problem:

I have three classes, A, AB and B, as so:

class AB(Base):
    id = Column(Integer, primary_key=True)
    a_id = Column(Integer, ForeignKey('a.id'), nullable=False)
    a = relationship(
        'A', 
        cascade='save-update', 
        backref=backref(
            'abs', 
            cascade='save-update', 
            uselist=True
        )
    )
    b_id = Column(Integer, ForeignKey('b.id'), nullable=False)
    b = relationship(
        'B', 
        cascade='save-update', 
        backref=backref(
            'abs', 
            cascade='save-update', 
            uselist=True
        )
    )
    __tablename__ = 'ab'

class A(Base)
    id = Column(Integer, primary_key=True)
    __tablename__ = 'a'

class B(Base)
    id = Column(Integer, primary_key=True)
    __tablename__ = 'b'

Essentially, this is a m2m relationship between A and B. The only non-standard thing is that there is a column id in the table AB. It's there for a reason.

I want to implement "merging" of two instances of A. We are given a1 and a2. Then, before deleting a1, all of its relationships with ABs should be reassigned to a2. It is vitally important to retain the values of AB.id in the process (hence, no new instances of AB should actually be created or deleted).

THE PROBLEM No matter how I try, every time I delete an instance of A, SQLAlchemy attempts to update the foreign_key with a NULL value, consequently breaking the NOT NULL constraint. It does that by issuing an explicit UPDATE ab SET a_id = NULL WHERE id = .... It does so, even though I have the following loop in my program:

for ab in a1.abs:
    ab.a_id = a2.id
    session.db.add(ab)

session.db.delete(a1)

Therefore it would seem to me that before delete is issued, all the abs related to a1 were safely moved to a2, however something is wrong.

SOME NON-SOLUTIONS

Would very much appreciate your help!

Upvotes: 0

Views: 1184

Answers (2)

julx
julx

Reputation: 9091

Whenever there are problems with synchronisation between the contents of the relationships and the actual set of rows in the database, I recommend using session.expire(). It forces values being reloaded from the DB the next time data is accessed.

About expire & refresh:

http://docs.sqlalchemy.org/en/latest/orm/session.html#refreshing-expiring

About stale data in collections:

http://docs.sqlalchemy.org/en/rel_0_8/orm/session.html#deleting-from-collections

These functions saved my life. Now, everything works as expected. Moreover, I can do bulk updates via sqlalchemy.sql.expression API which is generally much faster without sacrificing data integrity in the ORM layer.

Upvotes: 1

Daren
Daren

Reputation: 3407

Hmmm... something is very wrong here.

"UPDATE ab SET a_id = NULL WHERE id = a1.id" should affect 0 rows since you have already updated all ab where ab.a_id = a1.id.

Therefore only one possible explanation, you have failed to update ab. Computers tend not to lie. We only miss their logic sometimes.

My solution: to make sure everyhting is ok, before issuing the delete, find all rows of ab that point to a1.id if as it is to be expected, you find at least one, try to understand why that row is still there.

maybe session.db.add(ab) can be replaced by somehting like session.db.update(ab) and the add() is adding new rows to your ab table, or if no update exist or you can't use it, then delete first all ab where ab.a_id = a1.id after making sure you have correctly made copies (which I think you have)

Upvotes: 0

Related Questions