python - 删除多对多: Flask-Sqlalchemy tries to delete every row with specific id

Question

我一直坚持使用flask和flask-sqlalchemy从MySQL数据库中删除多对多记录。似乎 sqlalchemy 正在尝试从关系表中删除所有行，其中 a 的 id 与链接到 b 的 id 相同。

错误(示例):

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

或

sqlalchemy.orm.exc.StaleDataError: DELETE statement on table 'ksiazka_wydawnictwo' expected to delete 552 row(s); Only 551 were matched.

INFO sqlalchemy.engine.base.Engine DELETE FROM ksiazka_kategoria WHERE ksiazka_kategoria.ksiazka_id = %s AND ksiazka_kategoria.kategoria_id = %s
2017-08-29 15:44:34,965 INFO sqlalchemy.engine.base.Engine ((166, 40), (167, 40), (168, 40), (169, 40), (170, 40), (171, 40), (172, 40), (173, 40)  ... displaying 10 of 178 total bound parameter sets ...  (1463, 19), (2046, 19))

关系表:

ksiazka_autor = db.Table('ksiazka_autor',
        db.Column('autor_id', db.Integer, db.ForeignKey('autor.id')),
        db.Column('ksiazka_id', db.Integer, db.ForeignKey('ksiazka.id'))
    )
        ksiazka_wydawnictwo = db.Table('ksiazka_wydawnictwo',
        db.Column('wydawnictwo_id', db.Integer, db.ForeignKey('wydawnictwo.id')),
        db.Column('ksiazka_id', db.Integer, db.ForeignKey('ksiazka.id'))
    )
    ksiazka_ilustrator = db.Table('ksiazka_ilustrator',
        db.Column('ilustrator_id', db.Integer, db.ForeignKey('ilustrator.id')),
        db.Column('ksiazka_id', db.Integer, db.ForeignKey('ksiazka.id'))
    )
    ksiazka_kategoria = db.Table('ksiazka_kategoria',
        db.Column('ksiazka_id', db.Integer, db.ForeignKey('ksiazka.id')),
        db.Column('kategoria_id', db.Integer, db.ForeignKey('kategoria.id'))
    )

和型号:

class Book(db.Model):
    __tablename__ = 'ksiazka'
    id = db.Column('id', db.Integer, primary_key=True)
    [...]

    authors = db.relationship('Author', secondary=ksiazka_autor, backref='authors', cascade="all", lazy='dynamic')
    category = db.relationship('Category', secondary=ksiazka_kategoria, backref='category', cascade="all", lazy='dynamic')
    publisher = db.relationship('Publisher', secondary=ksiazka_wydawnictwo, backref='publisher', cascade="all", lazy='dynamic')
    artist = db.relationship('Artist', secondary=ksiazka_ilustrator, backref='artist', cascade="all", lazy='dynamic')

class Author(db.Model):
    __tablename__ = 'autor'
    id = db.Column('id', db.Integer, primary_key=True)
    [...]
    books = db.relationship('Book', secondary=ksiazka_autor, backref=db.backref('books'), lazy='joined')

class Category(db.Model):
    __tablename__ = 'kategoria'
    id = db.Column('id', db.Integer, primary_key=True)
    [...]
    cat_books = db.relationship('Book', secondary=ksiazka_kategoria, backref=db.backref('cat_books'), lazy='joined')

class Publisher(db.Model):
    __tablename__ = 'wydawnictwo'
    id = db.Column('id', db.Integer, primary_key=True)
    [...]
    pub_books = db.relationship('Book', secondary=ksiazka_wydawnictwo, backref=db.backref('pub_books'), lazy='joined')

class Artist(db.Model):
    __tablename__ = 'ilustrator'
    id = db.Column('id', db.Integer, primary_key=True)
    [...]
    art_books = db.relationship('Book', secondary=ksiazka_ilustrator, backref=db.backref('art_books'), lazy='joined')

最后是 flask 代码:

@app.route('/delete/book/<id>')
def delete_book(id):
    book = Book.query.filter_by(id=id).first()
    db.session.delete(book)
    db.session.commit()
    return redirect('/home')

Answer 1

这是因为您已经为所需的每个关系设置了两个单独的关系。您有一个名为 Book.authors 的关系和一个名为 Author.authors 的反向引用，以及一个名为 Author.books 的关系和一个名为 Author.authors 的反向引用Book.books。这不起作用，因为它们都在 ksiazka_autor 上运行，导致您看到的冲突。您所有其他关系也是如此。

您想要的是使用 back_populates 选项:

class Book(db.Model):
    ...

    authors = db.relationship('Author', secondary=ksiazka_autor, back_populates='books', cascade="all", lazy='dynamic')

class Author(db.Model):
    ...
    books = db.relationship('Book', secondary=ksiazka_autor, back_populates='authors', lazy='joined')

手动删除相关实例并提交两次不是正确的解决方案。