从 SQLAlchemy 中删除多对多匹配错误的行数

Question

我尝试使用 SQLAlchemy 删除 Flask 中的多对多对象。我有这些模型，类似于参考：

tags = db.Table('tags',
    db.Column('tag_id', db.Integer, db.ForeignKey('tag.id')),
    db.Column('document_id', db.Integer, db.ForeignKey('document.id'))
)
class Tag(db.Model):
    """The Tag class"""
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(256), unique=True)
    def __init__(self, name):
        self.name = name
    def __repr__(self):
        return '<Tag %r>' % self.name

class Document(db.Model):
    """The Document class"""
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(256))
    abstract = db.Column(db.String(256))
    body = db.Column(db.Text)
    # A Document has one syntax
    syntax_id = db.Column(db.Integer, db.ForeignKey('syntax.id'))
    syntax = db.relationship('Syntax', backref=db.backref('documents', lazy='dynamic'))
    is_private = db.Column(db.Boolean)
    completion = db.Column(db.Integer) #1, 2, 3 of 3
    creation_date = db.Column(db.DateTime)
    mod_date = db.Column(db.DateTime)
    license = db.Column(db.String(256))    
    # A Document has one Author
    author_id = db.Column(db.Integer, db.ForeignKey('author.id'))
    author = db.relationship('Author', backref=db.backref('documents', lazy='dynamic'))
    # A Document has many tags
    tags = db.relationship('Tag', secondary=tags, backref=db.backref('documents', lazy='dynamic'))
    # A Document has one Category
    category_id = db.Column(db.Integer, db.ForeignKey('category.id'))
    category = db.relationship('Category', backref=db.backref('documents', lazy='dynamic'))
    def __init__(self, title, body, is_private=None, creation_date=None):
        self.title = title
        self.body= body
        if is_private is None:
            self.is_private=False
        else:
            self.is_private = is_private
        self.completion = 1
        self.license = "CC-BY 4.0"
        if creation_date is None:
            self.creation_date = datetime.utcnow()
        else:
            self.creation_date = creation_date
        self.mod_date = datetime.utcnow()
    def __repr__(self):
        return '<Document %r>' % self.title

这里重要的是Document类与Tag类具有多对多关系。在外壳中，我删除了一个标签，一切都很好

>>> from aplicacio import db
>>>from aplicacio import Tag, Document
>>>d = Document.query.get(2)
>>>d.tags
[<Tag 'proportionality - linear function'>, <Tag 'how'>, <Tag 'when'>]
>>>d.tags.remove(t)
>>>d.tags
[<Tag 'proportionality - linear function'>, <Tag 'when'>]

但是当我运行commit时，这失败了：

db.session.commit()
2017-01-30 20:59:48,706 INFO sqlalchemy.engine.base.Engine DELETE FROM tags WHERE tags.tag_id = ? AND tags.document_id = ?
2017-01-30 20:59:48,706 INFO sqlalchemy.engine.base.Engine (2, 2)
2017-01-30 20:59:48,707 INFO sqlalchemy.engine.base.Engine ROLLBACK
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/lib/python3.6/site-packages/sqlalchemy/orm/scoping.py", line 157, in do
    return getattr(self.registry(), name)(*args, **kwargs)
  File "/usr/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 874, in commit
    self.transaction.commit()
  File "/usr/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 461, in commit
    self._prepare_impl()
  File "/usr/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 441, in _prepare_impl
    self.session.flush()
  File "/usr/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 2139, in flush
    self._flush(objects)
  File "/usr/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 2259, in _flush
    transaction.rollback(_capture_exception=True)
  File "/usr/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py", line 60, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/usr/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 187, in reraise
    raise value
  File "/usr/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 2223, in _flush
    flush_context.execute()
  File "/usr/lib/python3.6/site-packages/sqlalchemy/orm/unitofwork.py", line 389, in execute
    rec.execute(self)
  File "/usr/lib/python3.6/site-packages/sqlalchemy/orm/unitofwork.py", line 503, in execute
    self.dependency_processor.process_saves(uow, states)
  File "/usr/lib/python3.6/site-packages/sqlalchemy/orm/dependency.py", line 1092, in process_saves
    secondary_update, secondary_delete)
  File "/usr/lib/python3.6/site-packages/sqlalchemy/orm/dependency.py", line 1113, in _run_crud
    result.rowcount)
sqlalchemy.orm.exc.StaleDataError: DELETE statement on table 'tags' expected to delete 1 row(s); Only 5 were matched.

Answer 1

似乎我们有很多关联后标签，正如这里和那里所解释的那样。解决方式

tags = db.Table('tags',
    db.Column('tag_id', db.Integer, db.ForeignKey('tag.id')),
    db.Column('document_id', db.Integer, db.ForeignKey('document.id')),
    db.PrimaryKeyConstraint('tag_id', 'document_id')
)