Deleting From Many-to-many Sql-alchemy And Postgresql

I'm trying to delete a child object from a many-to-many relationship in sql-alchemy. I keep getting the following error: StaleDataError: DELETE statement on table 'headings_locatio

Solution 1:

I would assume that the error message is correct: indeed in your database you have 2 rows which link Location and Heading instances. In this case you should find out where and why did this happen in the first place, and prevent this from happening again

1. First, to confirm this assumption, you could run the following query against your database:

   q = session.query(
       headings_locations.c.location_id,
       headings_locations.c.heading_id,
       sa.func.count().label("# connections"),
   ).group_by(
       headings_locations.c.location_id,
       headings_locations.c.heading_id,
   ).having(
       sa.func.count() >1
   )
   

2. Assuming, the assumption is confirmed, fix it by manually deleting all the duplicates in your database (leaving just one for each).

3. After that, add a UniqueConstraint to your headings_locations table:

   headings_locations = db.Table('headings_locations',
           db.Column('id', db.Integer, primary_key=True),
           db.Column('location_id', db.Integer(), db.ForeignKey('location.id')),
           db.Column('headings_id', db.Integer(), db.ForeignKey('headings.id')),
           db.UniqueConstraint('location_id', 'headings_id', name='UC_location_id_headings_id'),
   )
   

Note that you need to need to add it to the database, it is not enough to add it to the sqlalchemy model.

Now the code where the duplicates are inserted by mistake will fail with the unique constraint violation exception, and you can fix the root of the problem.