Tolstov Sergey Tolstov Sergey - 3 years ago 231
Python Question

SQLAlchemy many-to-many Postgresql double delete

I try to delete data, but sqlalchemy tried to do it twice, and rollback
Base Postgresql
version 9.6
sqlalchemy 1.0.14
psycopg2 2.7.3.1

class IdentifiedObject(Base):
__tablename__ = 'identifiedobject'
mRID = Column(UUID, server_default=sqlalchemy.text("uuid_generate_v4()"), primary_key=True)
name = Column(String)
polymorphic_type = Column(String, nullable=False)
__mapper_args__={'polymorphic_identity':__tablename__,'polymorphic_on':polymorphic_type}
assoc_1 = Table("assoc_1", Base.metadata,
Column("cars_mRID", None, ForeignKey("cars.mRID")),
Column("games_mRID", None, ForeignKey("games.mRID")))
class Cars(IdentifiedObject):
__tablename__='cars'
mRID = Column(None, ForeignKey('identifiedobject.mRID'), primary_key=True)
polymorphic_type = Column(String, nullable=False)
__mapper_args__={'polymorphic_identity':__tablename__,'inherit_condition': mRID == IdentifiedObject.mRID,'polymorphic_on':polymorphic_type}
status = Column(String)
Games = relationship("Games", secondary = "assoc_1", back_populates="Cars", primaryjoin="(cars.c.mRID==assoc_1.c.cars_mRID)")
class Games(IdentifiedObject):
__tablename__='games'
mRID = Column(None, ForeignKey('identifiedobject.mRID'), primary_key=True)
polymorphic_type = Column(String, nullable=False)
__mapper_args__={'polymorphic_identity':__tablename__,'inherit_condition': mRID == IdentifiedObject.mRID,'polymorphic_on':polymorphic_type}
status = Column(String)
Cars = relationship("Cars", secondary = "assoc_1", back_populates="Games", primaryjoin="(games.c.mRID==assoc_1.c.games_mRID)")

Base.metadata.create_all(engine)
Base.prepare(engine, reflect=True)
session = Session(bind=engine)
session.add(IdentifiedObject())
games=Games(Cars=[Cars(),Cars()])
session.add (games)
session.commit()
session.close()
session.delete(games)
session.commit()


And now we found a exception

2017-09-29 09:17:44,996 INFO sqlalchemy.engine.base.Engine SELECT cars."mRID" AS "cars_mRID", identifiedobject."mRID" AS "identifiedobject_mRID", identifiedobject.name AS identifiedobject_name, cars.polymorphic_type AS cars_polymorphic_type, identifiedobject.polymorphic_type AS identifiedobject_polymorphic_type, cars.status AS cars_status
FROM assoc_1, identifiedobject JOIN cars ON cars."mRID" = identifiedobject."mRID"
WHERE %(param_1)s = assoc_1."games_mRID" AND cars."mRID" = assoc_1."cars_mRID"
2017-09-29 09:17:44,996 INFO sqlalchemy.engine.base.Engine {'param_1': UUID('7a960989-5e3e-45dc-87c1-1b62ffa3694a')}
2017-09-29 09:17:44,997 INFO sqlalchemy.engine.base.Engine DELETE FROM assoc_1 WHERE assoc_1."cars_mRID" = %(cars_mRID)s AND assoc_1."games_mRID" = %(games_mRID)s
2017-09-29 09:17:44,998 INFO sqlalchemy.engine.base.Engine ({'games_mRID': UUID('7a960989-5e3e-45dc-87c1-1b62ffa3694a'), 'cars_mRID': UUID('a3135561-e416-45c0-b9f8-aead59ef6b34')}, {'games_mRID': UUID('7a960989-5e3e-45dc-87c1-1b62ffa3694a'), 'cars_mRID': UUID('b77b9dc4-65da-45ea-be52-dc53e2bcd74b')})
2017-09-29 09:17:44,998 INFO sqlalchemy.engine.base.Engine DELETE FROM assoc_1 WHERE assoc_1."cars_mRID" = %(cars_mRID)s AND assoc_1."games_mRID" = %(games_mRID)s
2017-09-29 09:17:44,999 INFO sqlalchemy.engine.base.Engine ({'games_mRID': UUID('7a960989-5e3e-45dc-87c1-1b62ffa3694a'), 'cars_mRID': UUID('a3135561-e416-45c0-b9f8-aead59ef6b34')}, {'games_mRID': UUID('7a960989-5e3e-45dc-87c1-1b62ffa3694a'), 'cars_mRID': UUID('b77b9dc4-65da-45ea-be52-dc53e2bcd74b')})
2017-09-29 09:17:44,999 INFO sqlalchemy.engine.base.Engine ROLLBACK
Traceback (most recent call last):
File "testing.py", line 98, in <module>
session.commit()


Exception data

sqlalchemy.orm.exc.StaleDataError: DELETE statement on table 'assoc_1' expected to delete 2 row(s); Only 0 were matched.


Haven't found on other answers

Answer Source

This is caused by both specifying parts of the Declarative classes explicitly by hand and using automapper against existing tables. The relationships you define manually do not use the default naming convention used by the automapper, and so it creates another version of the relationship:

In [8]: list(inspect(Games).relationships)
Out[8]: 
[<RelationshipProperty at 0x7f932dea8ac8; Cars>,
 <RelationshipProperty at 0x7f932de6c2c8; cars_collection>]

So when you issue your delete, both relationships fire their cascades, and you get 2 DELETE statements. Overriding relationships is especially mentioned in the docs:

Above, one of the more intricate details is that we illustrated overriding one of the relationship() objects that automap would have created. To do this, we needed to make sure the names match up with what automap would normally generate, in that the relationship name would be User.address_collection ...

To fix this either revert to using the default naming scheme, or provide your own:

In [6]: def class_name_collection(base, local_cls, referred_cls, constraint):
   ...:     return referred_cls.__name__
   ...: 

and then

   ...: Base.prepare(engine, reflect=True,
   ...:              name_for_collection_relationship=class_name_collection)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download