CODEWITHSUNDEEP
sqlalchemymany-to-manyfastapi
Kasun
Kasun

Reputation: 689

SQLAlchemy Attempting to Twice Delete Many to Many Secondary Relationship PostgreSQL

Same issue report in SQLAlchemy Attempting to Twice Delete Many to Many Secondary Relationship.

But accepted answer is not working.

Model structure is

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'))

)

Issue is when try to delete Product instance, SQLAlchemy returns an error

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.

Database is Postgres and When I remove the backref from one model, no issue but I need to use backref on both models.

Setting supports_sane_rowcount not working for postgres

engine.dialect.supports_sane_rowcount = engine.dialect.supports_sane_multi_rowcount = False

Is there any workable solution for this?

Upvotes: 1

Views: 193

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123399

When I use your classes I get warnings like

SAWarning: relationship 'Product.products' will copy column products.id to column product_categories.products_id, which conflicts with relationship(s): 'Category.categories' (copies products.id to product_categories.products_id), 'Product.categories' (copies products.id to product_categories.products_id). If this is not the intention, consider if these relationships should be linked with back_populates, or if viewonly=True should be applied to one or more if they are read-only. For the less common case that foreign key constraints are partially overlapping, the orm.foreign() annotation can be used to isolate the columns that should be written towards. To silence this warning, add the parameter 'overlaps="categories,categories"' to the 'Product.products' relationship. (Background on this error at: https://sqlalche.me/e/14/qzyx)

The issue goes away if I use back_populates instead of 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",
    )

Complete working example:

from sqlalchemy import (
    create_engine,
    Column,
    Integer,
    String,
    Table,
    ForeignKey,
    select,
)
from sqlalchemy.orm import declarative_base, relationship, Session

engine = create_engine(
    "postgresql://scott:[email protected]/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
"""

Upvotes: 1

Related Questions