CODEWITHSUNDEEP
pythonflasksqlalchemyflask-sqlalchemyadjacency-list
thepandaatemyface
thepandaatemyface

Reputation: 5277

Flask-SQLAlchemy many-to-many adjacency list changes after commit

I have a model that has a many-to-many relationship with itself: An operation can be prevented by another operation or itself.

operation_to_operation_association_table = db.Table(
    "preventing_operations",
    db.Column("id", db.Integer, primary_key=True),
    db.Column("preventing_operation_id", db.Integer, db.ForeignKey("operation.id")),
    db.Column("prevents_operation_id", db.Integer, db.ForeignKey("operation.id")))



class Operation(BaseModel): # BaseModel defines id and creation/update times
    name = db.Column(db.String)

    bodypart_id = db.Column(db.Integer, db.ForeignKey(BodyPart.id))
    bodypart = db.relationship("BodyPart", backref="operations")

    prevents = db.relationship("Operation", secondary=operation_to_operation_association_table,
                               foreign_keys=[operation_to_operation_association_table.c.preventing_operation_id],
                               backref="prevented_by")


    def __eq__(self, other):
        return other and self.name == other.name and self.bodypart == other.bodypart

Then in the shell, from a fresh database:

In [1]: bp = BodyPart(name="liver")

In [2]: db.session.add(bp)

In [3]: db.session.commit()

In [4]: o1, o2 = Operation(name="viewing", bodypart=bp), Operation(name="removing", bodypart=bp)

In [5]: db.session.add_all([o1, o2])

In [6]: db.session.commit()

In [7]: o1, o2
Out[7]: (Viewing the liver (1), Removing the liver (2))

In [8]: o1.prevents, o2.prevents
Out[8]: ([], [])

In [9]: o2.prevents.append(o1)

In [10]: o1.prevents, o2.prevents
Out[10]: ([], [Viewing the liver (1)])

In [11]: db.session.commit()

In [12]: o1.prevents, o2.prevents
Out[12]: ([Viewing the liver (1)], [])

Committing switches the lists around?!

Logging queries, the insert-query SQLAlchemy sends the database seems to be wrong:

INSERT INTO preventing_operations (prevents_operation_id) VALUES (?)
with values (1,)

When it should be:

INSERT INTO preventing_operations (prevents_operation_id, preventing_operation_id) VALUES (?)
with values (2, 1)

What am I doing wrong here? Am I defining my relationship incorrectly? Then why does it only change when I commit?

Upvotes: 0

Views: 492

Answers (1)

Borys Serebrov
Borys Serebrov

Reputation: 16182

The issue is with foreign_keys setup and I am actually not sure what exactly need to be done to foreign_keys.

But I suggest to use primaryjoin and secondaryjoin instead. This way the setup is more obvious for me (and it works):

prevents = relationship(
    "Operation",
    secondary=operation_to_operation,
    primaryjoin=id == operation_to_operation.c.preventing_operation_id,
    secondaryjoin=id == operation_to_operation.c.prevents_operation_id,
    backref="prevented_by")

Here is the working example and module with base model.

Run the example as this: - download both files, save into the same folder (or clone the repo) - run `python many_many_save_issue.py.

I tested it with SQLAlchemy==1.0.6.

Upvotes: 2

Related Questions