My operating assumption is that sqlalchemy looks at each relationship and
tries to delete it, but since the previous relationship to the same base
class was already deleted, it throws the exception and the session rolls
back.
The error from above is essentially the same as the actual error in my code
base. I guess ideally there would be a way to just disable that
functionality but my solution works. Just will take several hours to
change everything correctly like this because we have about 200 models that
are all interconnected like this. I apologize for not giving a proper
working example I would have needed to create a brand new project
unfortunately because the code base is so complicated
On Thursday, December 17, 2020 at 7:32:43 PM UTC-5 maqui...@gmail.com wrote:
> 1. target database = postgres,
>
> example queries and stacktrace:
>
> >>> from webapp.database.orm.models import ParentClass, ChildClass,
> ChildChildClass
> >>> p = ParentClass()
> >>> c = ChildClass()
> >>> cc = ChildChildClass()
> >>> c.children.append(cc)
> >>> p.children.append(c)
> >>> session.add(p)
> >>> session.commit()
> >>> p = session.query(ParentClass).one()
> >>> [cc for c in p.children for cc in c.children]
> [ChildChildClass(id=UUID('1253a435-3330-4e36-bafc-ad8ff5176c4d'))]
> >>> session.delete(p)
> >>> session.flush()
> Traceback (most recent call last):
> File "<console>", line 1, in <module>
> File
> "/Users/aquinmx3/venv/espresso3.8/lib/python3.8/site-packages/sqlalchemy/orm/session.py",
>
> line 2496, in flush
> self._flush(objects)
> File
> "/Users/aquinmx3/venv/espresso3.8/lib/python3.8/site-packages/sqlalchemy/orm/session.py",
>
> line 2637, in _flush
> transaction.rollback(_capture_exception=True)
> File
> "/Users/aquinmx3/venv/espresso3.8/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py",
>
> line 68, in __exit__
> compat.raise_(
> File
> "/Users/aquinmx3/venv/espresso3.8/lib/python3.8/site-packages/sqlalchemy/util/compat.py",
>
> line 178, in raise_
> raise exception
> File
> "/Users/aquinmx3/venv/espresso3.8/lib/python3.8/site-packages/sqlalchemy/orm/session.py",
>
> line 2597, in _flush
> flush_context.execute()
> File
> "/Users/aquinmx3/venv/espresso3.8/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py",
>
> line 422, in execute
> rec.execute(self)
> File
> "/Users/aquinmx3/venv/espresso3.8/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py",
>
> line 538, in execute
> self.dependency_processor.process_deletes(uow, states)
> File
> "/Users/aquinmx3/venv/espresso3.8/lib/python3.8/site-packages/sqlalchemy/orm/dependency.py",
>
> line 1104, in process_deletes
> self._run_crud(
> File
> "/Users/aquinmx3/venv/espresso3.8/lib/python3.8/site-packages/sqlalchemy/orm/dependency.py",
>
> line 1201, in _run_crud
> raise exc.StaleDataError(
> sqlalchemy.orm.exc.StaleDataError: DELETE statement on table
> 'mix_child_class_child_child_class' expected to delete 1 row(s); Only 0
> were matched.
> >>>
> KeyboardInterrupt
>
> Works when ChildClass is declared as follows instead of as written in
> original question:
> class ChildClass(Base):
> __tablename__ = "child_class"
> id = Column("id", UUID(as_uuid=True), primary_key=True,
> default=uuid4())
> parents = relationship(
> "ParentClass",
> secondary="mix_parent_class_child_class",
> passive_deletes=True,
> )
> children = relationship(
> "ChildChildClass",
> secondary="mix_child_class_child_child_class",
> cascade="all, delete",
> )
> children2 = relationship(
> "ChildChildClass",
> secondary="mix_child_class_child_child_class",
> passive_deletes=True
> )
>
> >>> from webapp.database.orm.models import ParentClass, ChildClass,
> ChildChildClass
> >>> p = ParentClass()
> >>> c = ChildClass()
> >>> cc = ChildChildClass()
> >>> c.children.append(cc)
> >>> p.children.append(c)
> >>> session.add(p)
> >>> session.commit()
> >>> p = session.query(ParentClass).one()
> >>> session.delete(p)
> >>> session.commit()
>
>
> On Thursday, December 17, 2020 at 7:25:58 PM UTC-5 Mike Bayer wrote:
>
>>
>>
>> On Thu, Dec 17, 2020, at 7:15 PM, Mark Aquino wrote:
>>
>> They seem to be runnable except the base should be= DeclarativeMeta
>>
>>
>> OK, runnable example is below.
>>
>> how about:
>>
>> 1. target database type (Postgresql?)
>>
>> 2. sample data, inserts, etc.
>>
>> 3. sample queries
>>
>> 4. operation that fails
>>
>> 5. stack trace, etc
>>
>>
>> thanks!
>>
>> from uuid import uuid4
>>
>> from sqlalchemy import Column
>> from sqlalchemy.dialects.postgresql.base import UUID
>> from sqlalchemy.orm import relationship
>> from sqlalchemy.ext.declarative import declarative_base
>>
>> Base = declarative_base()
>>
>>
>> class ChildClass(Base):
>> __tablename__ = "child_class"
>> id = Column("id", UUID(as_uuid=True), primary_key=True,
>> default=uuid4())
>> parents = relationship(
>> "ParentClass",
>> secondary="mix_parent_class_child_class",
>> passive_deletes=True,
>> )
>> children = relationship(
>> "ChildChildClass",
>> secondary="mix_child_class_child_child_class",
>> cascade="all, delete",
>> )
>> children2 = relationship(
>> "ChildChildClass",
>> secondary="mix_child_class_child_child_class",
>> cascade="all, delete",
>> )
>>
>>
>> class ChildChildClass(Base):
>> __tablename__ = "child_child_class"
>> id = Column("id", UUID(as_uuid=True), primary_key=True,
>> default=uuid4())
>> parents = relationship(
>> "ChildClass",
>> secondary="mix_child_class_child_child_class",
>> passive_deletes=True,
>> )
>>
>>
>> class ParentClass(Base):
>> __tablename__ = "parent_class"
>> id = Column("id", UUID(as_uuid=True), primary_key=True,
>> default=uuid4())
>> children = relationship(
>> "ChildClass",
>> secondary="mix_parent_class_child_class",
>> cascade="all, delete",
>> )
>>
>>
>> from sqlalchemy.sql.schema import Table, ForeignKey, Column,
>> UniqueConstraint
>>
>>
>> mix_parent_class_child_class = Table(
>> "mix_parent_class_child_class",
>> Base.metadata,
>> Column(
>> "parent_class_id",
>> ForeignKey("parent_class.id", ondelete="CASCADE"),
>> nullable=False,
>> ),
>> Column("child_class_id", ForeignKey("child_class.id"),
>> nullable=False),
>> UniqueConstraint(
>> "parent_class_id",
>> "child_class_id",
>> name="uix_parent_class_child_class",
>> ),
>> )
>>
>> mix_child_class_child_child_class = Table(
>> "mix_child_class_child_child_class",
>> Base.metadata,
>> Column(
>> "child_class_id",
>> ForeignKey("child_class.id", ondelete="CASCADE"),
>> nullable=False,
>> ),
>> Column(
>> "child_child_class_id",
>> ForeignKey("child_child_class.id"),
>> nullable=False,
>> ),
>> UniqueConstraint(
>> "child_class_id",
>> "child_child_class_id",
>> name="uix_child_class_child_child_class",
>> ),
>> )
>>
>>
>>
>>
>>
>> The relationships aren’t to the same target in real life,
>>
>> It’s like this:
>>
>> Class BaseClass:
>> ...
>>
>> Class SubClassA(BaseClass)
>> ...
>>
>> Class SubclassB(BaseClass):
>> ...
>>
>> (Plus Many other subclasses)
>>
>> A Mixer “BaseClassBaseClass” such that child subclassA and subclassB are
>> both present in mix_base_class_bass_class
>>
>> BaseClass has relationships to BaseClass, SubClassA and SubClassB, so you
>> can access only SubClassAs and SubClassBs or all SubClassXs that could be
>> filtered to whatever subclass you need. All these relationships use the
>> same mixer table, because they all share a primary key with BaseClass (and
>> removing the need to make explicit mixed tables for every subclass).
>>
>> Mark Aquino
>> ------------------------------
>>
>> *From:* sqlal...@googlegroups.com <sqlal...@googlegroups.com> on behalf
>> of Mike Bayer <mik...@zzzcomputing.com>
>> *Sent:* Thursday, December 17, 2020 7:08:58 PM
>> *To:* noreply-spamdigest via sqlalchemy <sqlal...@googlegroups.com>
>> *Subject:* Re: [sqlalchemy] Re: Can't delete cascade many-to-many with
>> polymorphic relationships unless using lazy=dynamic
>>
>> your examples aren't complete or runnable so I don't really know what the
>> issue is, although having two relationships to the same target class seems
>> a little unusual and I'm not sure why you'd need that.
>>
>>
>> On Thu, Dec 17, 2020, at 6:01 PM, maqui...@gmail.com wrote:
>>
>>
>> I think I may have just found a solution? Not sure if this is correct but
>> it looks like it worked when i changed the "extra" relationship to
>> passive_deletes=True instead of cascade
>>
>> class ChildClass(XPressoBase):
>> __tablename__ = "child_class"
>> id = Column("id", UUID(as_uuid=True), primary_key=True, default=uuid4())
>> parents = relationship("ParentClass",
>> secondary="mix_parent_class_child_class", passive_deletes=True)
>> children = relationship("ChildChildClass",
>> secondary="mix_child_class_child_child_class", cascade="all, delete")
>> children2 = relationship("ChildChildClass",
>> secondary="mix_child_class_child_child_class", passive_deletes=True)
>> On Thursday, December 17, 2020 at 5:50:06 PM UTC-5 maqui...@gmail.com
>> wrote:
>>
>> I have a polymorphic data model where association tables are to base
>> classes and some relationships that link to different child classes to
>> filter out the non-matching base classes, however these seem to result in
>> sqlalchemy being unable to delete cascade properly
>>
>> In a real case lets say i have
>> class Base():
>> children = relationship("Base", secondary="mix_base_base",
>> cascade="all, delete")
>> someTypes = relationship("SomeType", secondary="mix_base_base",
>> cascade="all, delete")
>> other_types = relationship("OtherType", secondary="mix_base_base",
>> cascade="all")
>>
>> class SomeType(Base):
>> parents = relationship("Base", secondary="mix_base_base",
>> passive_deletes=True)
>>
>> class OtherType(Base):
>> parents = relationship("Base", secondary="mix_base_base",
>> passive_deletes=True)
>>
>>
>> if I delete a base that doesn't have relationships to SomeType and
>> OtherType, then everything works great. However once I add those extra
>> relationships sqlalchemy no longer deletes the children relationships.
>> This can be overcome by using lazy="dynamic" on the relationships, but at
>> GREAT performance cost (about 100%), which isn't good.
>>
>> Does anyone know how to get around this without using dynamic loading?
>> The specific relationships are important for front end pagination of data,
>> but the performance cost is too great for the amount of data in this system
>> to double the query times.
>>
>> a simplified and stupid workable example where I just add a redundant
>> children2 relationship to ChildClass, resulting in the same problem:
>>
>> from uuid import uuid4
>>
>> from sqlalchemy import Column
>> from sqlalchemy.dialects.postgresql.base import UUID
>> from sqlalchemy.orm import relationship
>>
>> from webapp.database.orm.base import XPressoBase
>>
>> class ChildClass(Base):
>> __tablename__ = "child_class"
>> id = Column("id", UUID(as_uuid=True), primary_key=True, default=uuid4())
>> parents = relationship("ParentClass",
>> secondary="mix_parent_class_child_class", passive_deletes=True)
>> children = relationship("ChildChildClass",
>> secondary="mix_child_class_child_child_class", cascade="all, delete")
>> children2 = relationship("ChildChildClass",
>> secondary="mix_child_class_child_child_class", cascade="all, delete")
>>
>> class ChildChildClass(Base):
>> __tablename__ = "child_child_class"
>> id = Column("id", UUID(as_uuid=True), primary_key=True, default=uuid4())
>> parents = relationship("ChildClass",
>> secondary="mix_child_class_child_child_class", passive_deletes=True)
>>
>> class ParentClass(Base):
>> __tablename__ = "parent_class"
>> id = Column("id", UUID(as_uuid=True), primary_key=True, default=uuid4())
>> children = relationship("ChildClass",
>> secondary="mix_parent_class_child_class", cascade="all, delete")
>> from sqlalchemy.sql.schema import Table, ForeignKey, Column,
>> UniqueConstraint
>>
>> from webapp.database.orm.base import XPressoBase
>>
>> mix_parent_class_child_class = Table(
>> "mix_parent_class_child_class",
>> XPressoBase.metadata,
>> Column("parent_class_id", ForeignKey("parent_class.id",
>> ondelete="CASCADE"), nullable=False),
>> Column("child_class_id", ForeignKey("child_class.id"), nullable=False),
>> UniqueConstraint(
>> "parent_class_id", "child_class_id", name="uix_parent_class_child_class"
>> ),
>> )
>>
>> mix_child_class_child_child_class = Table(
>> "mix_child_class_child_child_class",
>> XPressoBase.metadata,
>> Column("child_class_id", ForeignKey("child_class.id",
>> ondelete="CASCADE"), nullable=False),
>> Column("child_child_class_id", ForeignKey("child_child_class.id"),
>> nullable=False),
>> UniqueConstraint(
>> "child_class_id", "child_child_class_id",
>> name="uix_child_class_child_child_class"
>> ),
>> )
>>
>>
>> --
>> SQLAlchemy -
>> The Python SQL Toolkit and Object Relational Mapper
>>
>> http://www.sqlalchemy.org/
>>
>> To post example code, please provide an MCVE: Minimal, Complete, and
>> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
>> description.
>> ---
>> You received this message because you are subscribed to the Google Groups
>> "sqlalchemy" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to sqlalchemy+...@googlegroups.com.
>> To view this discussion on the web visit
>> https://groups.google.com/d/msgid/sqlalchemy/a172d1cf-7105-48d8-a1ea-50889fa6e5c7n%40googlegroups.com
>>
>> <https://groups.google.com/d/msgid/sqlalchemy/a172d1cf-7105-48d8-a1ea-50889fa6e5c7n%40googlegroups.com?utm_medium=email&utm_source=footer>
>> .
>>
>>
>>
>> --
>> SQLAlchemy -
>> The Python SQL Toolkit and Object Relational Mapper
>>
>> http://www.sqlalchemy.org/
>>
>> To post example code, please provide an MCVE: Minimal, Complete, and
>> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
>> description.
>> ---
>> You received this message because you are subscribed to the Google Groups
>> "sqlalchemy" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to sqlalchemy+...@googlegroups.com.
>> To view this discussion on the web visit
>> https://groups.google.com/d/msgid/sqlalchemy/ab17a1d6-e936-4a7c-bbc9-e0459d1e6da0%40www.fastmail.com
>>
>> <https://groups.google.com/d/msgid/sqlalchemy/ab17a1d6-e936-4a7c-bbc9-e0459d1e6da0%40www.fastmail.com?utm_medium=email&utm_source=footer>
>> .
>>
>>
>> --
>> SQLAlchemy -
>> The Python SQL Toolkit and Object Relational Mapper
>>
>> http://www.sqlalchemy.org/
>>
>> To post example code, please provide an MCVE: Minimal, Complete, and
>> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
>> description.
>> ---
>> You received this message because you are subscribed to the Google Groups
>> "sqlalchemy" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to sqlalchemy+...@googlegroups.com.
>>
>> To view this discussion on the web visit
>> https://groups.google.com/d/msgid/sqlalchemy/BL0PR16MB251536B80589AC7738798A8DF0C30%40BL0PR16MB2515.namprd16.prod.outlook.com
>>
>> <https://groups.google.com/d/msgid/sqlalchemy/BL0PR16MB251536B80589AC7738798A8DF0C30%40BL0PR16MB2515.namprd16.prod.outlook.com?utm_medium=email&utm_source=footer>
>> .
>>
>>
>>
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/01d99aca-6924-4ea0-99ff-4f759f45f985n%40googlegroups.com.
