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

Question

I have two tables. One is called books and one is called library_categories. Now I want to order the books into library_categories, with the condition that a book can be in multiple library categories. Here are my books

class Books(db.Model):

    book_id = db.Column(db.String(1000), primary_key=True)
    price = db.Column(db.String(1000))

    def __repr__(self):
        return '<Books %r>' % (self.arxiv_id)

And here are my library categories

class Library_category(db.Model):

    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    name = db.Column(db.String(1000))

def __repr__(self):
    return '<Library_category %r>' % (self.id)

where the category can be created by any user. Now I want to link the library categories and the books and I do this through a table

library_category_items = db.Table('library_category_items',
    db.Column('books_book_id', db.String(1000), db.ForeignKey('books.book_id')),
    db.Column('library_category_id', db.Integer, db.ForeignKey('library_category.id'))
)

this all seems to work fine, but when I want to delete a book from this table which is present in two categories I get

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

The delete is done as

users = models.User.query.all()
for user in users:
    for category in user.library_categories.all():
        for book in category.items:
            category.items.remove(book)
        db.session.delete(category)
    db.session.commit() 

Where my User model has the attribute library_categories which gives all categories for this user and items is an attribute for category which gives all books in that category. These relations are defined as

user = db.relationship("User", backref=db.backref('library_categories', lazy='dynamic'))
items = db.relationship('Books', secondary=library_category_items, backref=db.backref('library_categories', lazy='dynamic'))

(these are defined within library_category)

Any idea what I do wrong? thanks carl

EDIT: I can all make it work if I prevent the items of the categories to be deleted with

viewonly=True,

in

items = db.relationship('Books', viewonly=True, secondary=library_category_items, backref=db.backref('library_categories', lazy='dynamic'))

however, sql-alchemy will reuse the ids for a table and therefore it could be that a new category is created, which has entries from an old (already deleted) category... does anybody know a better solution? Is there a way to prevent sqlalchemy from using the same id multiple times?

Answer 1

The reason you are seeing this error is that you have duplicate entry in your library_category_items table and category.items.remove(book) expects one. You need to find out why did you have the same row entered twice into the database and prevent it.