your example shows two relationships to the identical target class:
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 Fri, Dec 18, 2020, at 8:40 AM, Mark Aquino wrote:
> Thanks. That could work. The reason we need specific relationships to the
> child class types is for our front end when it queries the data, which we use
> sqlalchemy-graphene to do, if we query the base class relationship it returns
> empty rows and makes pagination impossible as the front end is unable to know
> how many rows containing the desired type are present when the mixer table
> contains references to several different classes by using the id shared on
> the base class.
>
> Mark Aquino
>
> *From:* [email protected] <[email protected]> on behalf
> of Mike Bayer <[email protected]>
> *Sent:* Thursday, December 17, 2020 9:32:40 PM
> *To:* noreply-spamdigest via sqlalchemy <[email protected]>
> *Subject:* Re: [sqlalchemy] Re: Can't delete cascade many-to-many with
> polymorphic relationships unless using lazy=dynamic
>
> perhaps you are looking to set viewonly=True on this second relationship?
> that will exclude it from any kind of persistence operation. it will only
> be used to load things in from the database but not accept or honor any
> mutations of data.
>
> not sure why you need to have two relationships that are identical, but if
> you do, then yes you want only one of them to be the one that writes changes
> to the DB.
>
> On Thu, Dec 17, 2020, at 7:40 PM, [email protected] wrote:
>> 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 [email protected] 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:* [email protected] <[email protected]> on behalf
>>>>> of Mike Bayer <[email protected]>
>>>>> *Sent:* Thursday, December 17, 2020 7:08:58 PM
>>>>> *To:* noreply-spamdigest via sqlalchemy <[email protected]>
>>>>> *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, [email protected] 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 [email protected]
>>>>>> 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 [email protected].
>>>>>> 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 [email protected].
>>>>> 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 [email protected].
>>>>> 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 [email protected].
>> To view this discussion on the web visit
>> https://groups.google.com/d/msgid/sqlalchemy/01d99aca-6924-4ea0-99ff-4f759f45f985n%40googlegroups.com
>>
>> <https://groups.google.com/d/msgid/sqlalchemy/01d99aca-6924-4ea0-99ff-4f759f45f985n%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 [email protected].
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/1ccba080-f91d-42bb-9eb6-65740efe0f0f%40www.fastmail.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/1ccba080-f91d-42bb-9eb6-65740efe0f0f%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 [email protected].
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/BL0PR16MB2515B4FCF9DDA84EC28718E0F0C30%40BL0PR16MB2515.namprd16.prod.outlook.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/BL0PR16MB2515B4FCF9DDA84EC28718E0F0C30%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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/497470b1-211d-4ae0-a488-ef80db78b96e%40www.fastmail.com.
