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/d8efd08d-dbaa-4d1d-8dec-6d8091713821n%40googlegroups.com.
