我有一个与product_categories具有多对多关系的product模型，如下所述：
class Product(Base):
""" The SQLAlchemy declarative model class for a Product object. """
__tablename__ = 'products'
id = Column(Integer, primary_key=True)
part_number = Column(String(10), nullable=False, unique=True)
name = Column(String(80), nullable=False, unique=True)
description = Column(String(2000), nullable=False)
categories = relationship('Category', secondary=product_categories,
backref=backref('categories', lazy='dynamic'))
class Category(Base):
""" The SQLAlchemy declarative model class for a Category object. """
__tablename__ = 'categories'
id = Column(Integer, primary_key=True)
lft = Column(Integer, nullable=False)
rgt = Column(Integer, nullable=False)
name = Column(String(80), nullable=False)
description = Column(String(2000), nullable=False)
order = Column(Integer)
products = relationship('Product', secondary=product_categories,
backref=backref('products', lazy='dynamic', order_by=name))
product_categories = Table('product_categories', Base.metadata,
Column('products_id', Integer, ForeignKey('products.id')),
Column('categories_id', Integer, ForeignKey('categories.id'))
)
然后，我试图用删除这个对象
product = DBSession.query(Product).filter_by(name = 'Algaecide').one()
DBSession().delete(product)
但是，我得到的是错误消息StaleDataError: DELETE statement on table 'product_categories' expected to delete 1 row(s); Only 0 were matched.。如果您注意到，它似乎正在运行DELETE语句两次。因此，第一次它成功地从product_categories表中删除product_id，但随后出于任何原因再次尝试删除，然后决定需要抛出异常，因为它不存在。
>>> Attempting to delete the product 'Algaecide' assigned to categories: [<myproject.models.category.Category object at 0x106fa9d90>]
21:39:50 INFO [sqlalchemy.engine.base.Engine][Dummy-2] SELECT categories.id AS categories_id, categories.lft AS categories_lft, categories.rgt AS categories_rgt, categories.name AS categories_name, categories.description AS categories_description, categories.`order` AS categories_order
FROM categories, product_categories
WHERE %s = product_categories.products_id AND categories.id = product_categories.categories_id ORDER BY categories.name
21:39:50 INFO [sqlalchemy.engine.base.Engine][Dummy-2] (109L,)
21:39:50 INFO [sqlalchemy.engine.base.Engine][Dummy-2] DELETE FROM product_categories WHERE product_categories.products_id = %s AND product_categories.categories_id = %s
21:39:50 INFO [sqlalchemy.engine.base.Engine][Dummy-2] (109L, 18L)
21:39:50 INFO [sqlalchemy.engine.base.Engine][Dummy-2] DELETE FROM product_categories WHERE product_categories.products_id = %s AND product_categories.categories_id = %s
21:39:50 INFO [sqlalchemy.engine.base.Engine][Dummy-2] (109L, 18L)
21:39:50 INFO [sqlalchemy.engine.base.Engine][Dummy-2] ROLLBACK
21:39:50 ERROR [pyramid_debugtoolbar][Dummy-2] Exception at http://0.0.0.0:6543/product/Algaecide/edit
traceback url: http://0.0.0.0:6543/_debug_toolbar/exception?token=6344937a98ee26992689&tb=4421411920
Traceback (most recent call last):
File "/Users/derek/pyramid/myproject/lib/python2.7/site-packages/pyramid_debugtoolbar-2.3-py2.7.egg/pyramid_debugtoolbar/toolbar.py", line 178, in toolbar_tween
response = _handler(request)
File "/Users/derek/pyramid/myproject/lib/python2.7/site-packages/pyramid_debugtoolbar-2.3-py2.7.egg/pyramid_debugtoolbar/panels/performance.py", line 57, in resource_timer_handler
result = handler(request)
File "/Users/derek/pyramid/myproject/lib/python2.7/site-packages/pyramid/tweens.py", line 21, in excview_tween
response = handler(request)
File "/Users/derek/pyramid/myproject/lib/python2.7/site-packages/pyramid_tm-0.10-py2.7.egg/pyramid_tm/__init__.py", line 95, in tm_tween
reraise(*exc_info)
File "/Users/derek/pyramid/myproject/lib/python2.7/site-packages/pyramid_tm-0.10-py2.7.egg/pyramid_tm/__init__.py", line 83, in tm_tween
manager.commit()
File "/Users/derek/pyramid/myproject/lib/python2.7/site-packages/transaction-1.4.3-py2.7.egg/transaction/_manager.py", line 111, in commit
return self.get().commit()
File "/Users/derek/pyramid/myproject/lib/python2.7/site-packages/transaction-1.4.3-py2.7.egg/transaction/_transaction.py", line 280, in commit
reraise(t, v, tb)
File "/Users/derek/pyramid/myproject/lib/python2.7/site-packages/transaction-1.4.3-py2.7.egg/transaction/_transaction.py", line 271, in commit
self._commitResources()
File "/Users/derek/pyramid/myproject/lib/python2.7/site-packages/transaction-1.4.3-py2.7.egg/transaction/_transaction.py", line 417, in _commitResources
reraise(t, v, tb)
File "/Users/derek/pyramid/myproject/lib/python2.7/site-packages/transaction-1.4.3-py2.7.egg/transaction/_transaction.py", line 389, in _commitResources
rm.tpc_begin(self)
File "/Users/derek/pyramid/myproject/lib/python2.7/site-packages/zope.sqlalchemy-0.7.5-py2.7.egg/zope/sqlalchemy/datamanager.py", line 90, in tpc_begin
self.session.flush()
File "/Users/derek/pyramid/myproject/lib/python2.7/site-packages/SQLAlchemy-0.9.8-py2.7-macosx-10.6-intel.egg/sqlalchemy/orm/session.py", line 1919, in flush
self._flush(objects)
File "/Users/derek/pyramid/myproject/lib/python2.7/site-packages/SQLAlchemy-0.9.8-py2.7-macosx-10.6-intel.egg/sqlalchemy/orm/session.py", line 2037, in _flush
transaction.rollback(_capture_exception=True)
File "/Users/derek/pyramid/myproject/lib/python2.7/site-packages/SQLAlchemy-0.9.8-py2.7-macosx-10.6-intel.egg/sqlalchemy/util/langhelpers.py", line 60, in __exit__
compat.reraise(exc_type, exc_value, exc_tb)
File "/Users/derek/pyramid/myproject/lib/python2.7/site-packages/SQLAlchemy-0.9.8-py2.7-macosx-10.6-intel.egg/sqlalchemy/orm/session.py", line 2001, in _flush
flush_context.execute()
File "/Users/derek/pyramid/myproject/lib/python2.7/site-packages/SQLAlchemy-0.9.8-py2.7-macosx-10.6-intel.egg/sqlalchemy/orm/unitofwork.py", line 372, in execute
rec.execute(self)
File "/Users/derek/pyramid/myproject/lib/python2.7/site-packages/SQLAlchemy-0.9.8-py2.7-macosx-10.6-intel.egg/sqlalchemy/orm/unitofwork.py", line 479, in execute
self.dependency_processor.process_deletes(uow, states)
File "/Users/derek/pyramid/myproject/lib/python2.7/site-packages/SQLAlchemy-0.9.8-py2.7-macosx-10.6-intel.egg/sqlalchemy/orm/dependency.py", line 1023, in process_deletes
secondary_update, secondary_delete)
File "/Users/derek/pyramid/myproject/lib/python2.7/site-packages/SQLAlchemy-0.9.8-py2.7-macosx-10.6-intel.egg/sqlalchemy/orm/dependency.py", line 1111, in _run_crud
result.rowcount)
StaleDataError: DELETE statement on table 'product_categories' expected to delete 1 row(s); Only 0 were matched.
在这种情况下，我是不是错过了某种"抓住"的机会？看起来是一个相当标准的实现。为什么它试图执行DELETE语句两次？
经过进一步搜索，我发现了以下链接，表明这是MySQL错误：https://groups.google.com/forum/#！主题/sqlalchemy/ajYLEuhEB9k
谢天谢地，禁用supports_san_multi_rowcount起到了帮助作用！
engine = engine_from_config(settings, 'sqlalchemy.')
engine.dialect.supports_sane_rowcount = engine.dialect.supports_sane_multi_rowcount = False
现在对我来说已经足够了。以下是我在处理postgresql的过程中发现的另一个有趣的资源：https://bitbucket.org/zzzeek/sqlalchemy/issue/3015/deletes-executed-twice-when-using