在SQLAlchemy尝试两次删除多对多次要关系时报告同样的问题。
但接受的答案不起作用
模型结构为
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实例时，SQLAlchemy返回一个错误
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.
数据库是Postgres和当我删除backref从一个模型，没有问题，但我需要使用backref
设置supports_sane_rowcount不工作对于postgres
engine.dialect.supports_sane_rowcount = engine.dialect.supports_sane_multi_rowcount = False
有可行的解决方案吗?
当我使用你的类时，我得到警告，如
SAWarning: relationship 'Product。产品’将复制栏目产品。Id到列product_categories。products_id，它与关系:'Category '冲突。类别(复制产品)。(product_categories.products_id)，类别(复制产品)。Id改为product_categories.products_id)。如果不是这样，请考虑这些关系是否应该与back_populates链接，或者如果它们是只读的，是否应该将viewonly=True应用于一个或多个关系。对于外键约束部分重叠这种不太常见的情况，可以使用form .foreign()注释来隔离应该写入的列。要静音此警告，请向"Product"添加参数"overlaps="categories,categories"。产品的关系。(此错误的背景信息:https://sqlalche.me/e/14/qzyx)
如果我使用back_populates而不是backref，问题就会消失:
class Product(Base):
# …
categories = relationship(
"Category",
secondary=product_categories,
back_populates="products",
lazy="dynamic",
order_by = "Category.name",
)
class Category(Base):
# …
products = relationship(
"Product",
secondary=product_categories,
back_populates="categories",
lazy="dynamic",
order_by="Product.name",
)
完整的工作示例:
from sqlalchemy import (
create_engine,
Column,
Integer,
String,
Table,
ForeignKey,
select,
)
from sqlalchemy.orm import declarative_base, relationship, Session
engine = create_engine(
"postgresql://scott:tiger@192.168.0.199/test",
)
Base = declarative_base()
product_categories = Table(
"product_categories",
Base.metadata,
Column("products_id", Integer, ForeignKey("products.id")),
Column("categories_id", Integer, ForeignKey("categories.id")),
)
class Product(Base):
__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,
back_populates="products",
lazy="dynamic",
order_by = "Category.name",
)
class Category(Base):
__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,
back_populates="categories",
lazy="dynamic",
order_by="Product.name",
)
Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)
# setup
with Session(engine) as sess:
category_a = Category(
lft=0, rgt=0, name="category_a", description="category_a"
)
product_a = Product(
part_number="product_a",
name="product_a",
description="product_a",
categories=[category_a],
)
sess.add_all([category_a, product_a])
sess.commit()
# test
engine.echo = True
with Session(engine) as sess:
p = sess.execute(
select(Product).where(Product.part_number == "product_a")
).scalar()
sess.delete(p)
sess.commit()
"""SQL emitted:
DELETE FROM product_categories WHERE product_categories.products_id = %(products_id)s AND product_categories.categories_id = %(categories_id)s
[generated in 0.00074s] {'products_id': 1, 'categories_id': 1}
DELETE FROM products WHERE products.id = %(id)s
[generated in 0.00069s] {'id': 1}
COMMIT
"""