[sqlalchemy] many-to-many-to-one?
I'm trying to do something a little unusual, I'm open to alternative ideas
for how to accomplish this as well but I think I need a 3 column mixer
table with 3 foreign keys.
Right now I have many to many relationships between 3 tables, e.g.
a m2m b
b mbm c
however, I really something like (a_id, b_id, c_id) (again, could be a
different way that i'm totally open to using) because only some Bs and Cs
will be valid for an A, and so forth.
i still need to be able to associate many to many with each other, but with
a discriminator (where) on the third column like a.bs where c=some_value
Is an association object capable of this or am I barking up the wrong tree?
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Integer, Column, ForeignKey
from sqlalchemy.orm import relationship
Base = declarative_base()
class Association(Base):
__tablename__ = 'association'
a_id = Column(ForeignKey('a.id'), primary_key=True)
b_id = Column(ForeignKey('b.id'), primary_key=True)
c_id = Column(ForeignKey('c.id'), primary_key=True)
parent = relationship("A", back_populates="children")
child = relationship("B", back_populates="parents")
#cousin = relationship("C", back_populates="relatives") ???
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
children = relationship("Association", back_populates="parent")
class B(Base):
__tablename__ = 'b'
id = Column(Integer, primary_key=True)
parents = relationship("Association", back_populates="child")
class C(Base):
__tablename__ = 'c'
id = Column(Integer, primary_key=True)
# relatives = relationship("Association", back_populates="cousin")
--
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/e5861bb3-5da9-494e-bbf8-f9275df26dc5n%40googlegroups.com.
[sqlalchemy] Issue with "complex" many 2 many delete, FK violation
I need to delete the association table rows for many to many relationships
when I delete one, but the default behavior (to remove those rows) does not
seem to work in my case.
I have multiple levels of many to many relationships, as you can see in the
example I'll provide below and when I delete a "parent" afterwards I try to
clean up any children left behind that have no other parents. However,
these children are in many to many relationships with other children and
that's when the ORM fails to attempt to remove those children from their
related association tables (at least, in a way that I expect).
The issue is error is:
def do_executemany(self, cursor, statement, parameters, context=None):
if self.executemany_mode is EXECUTEMANY_DEFAULT:
> cursor.executemany(statement, parameters)
E sqlalchemy.exc.IntegrityError:
(psycopg2.errors.ForeignKeyViolation) update or delete on table
"test_var_region" violates foreign key constraint
"test_chain_var_region_var_region_id_fkey" on table "test_chain_var_region"
E DETAIL: Key (id)=(1) is still referenced from table
"test_chain_var_region".
E
E [SQL: DELETE FROM test_mol_sequence WHERE test_mol_sequence.id
= %(id)s]
E [parameters: ({'id': 5}, {'id': 6}, {'id': 7}, {'id': 8},
{'id': 9}, {'id': 10})]
E (Background on this error at: http://sqlalche.me/e/gkpj)
The desired effect, of course, is that the rows in test_chain_var_region
that reference the deleted chains removed. I've tried several strategies
to do this but with no change in this behavior.
Cascades could be an issue, and I would rather handle removal of any
"orphan" rows in the model tables via business logic than have the database
cascade deletes and potentially remove rows that are associated with other
objects.
import pytest
from sqlalchemy import (
Table,
Column,
Integer,
String,
ForeignKey,
create_engine,
)
from sqlalchemy.ext.declarative import declarative_base, DeclarativeMeta
from sqlalchemy.orm import relationship, Session
from sqlalchemy.util import OrderedSet
Base: DeclarativeMeta = declarative_base()
engine = create_engine(
"postgresql://postgres:postgres@localhost:5432/espresso", echo=True
)
test_chain_const_region = Table(
"test_chain_const_region",
Base.metadata,
Column("chain_id", Integer, ForeignKey("test_chain.id")),
Column("const_region_id", Integer, ForeignKey("test_const_region.id")),
)
test_chain_var_region = Table(
"test_chain_var_region",
Base.metadata,
Column("chain_id", Integer, ForeignKey("test_chain.id")),
Column("var_region_id", Integer, ForeignKey("test_var_region.id")),
)
test_molecule_chain = Table(
"test_molecule_chain",
Base.metadata,
Column("molecule_id", Integer, ForeignKey("test_molecule.id")),
Column("chain_id", Integer, ForeignKey("test_chain.id")),
)
test_mol_sequence_feat_mol_sequence = Table(
"test_mol_sequence_feat_mol_sequence",
Base.metadata,
Column("mol_sequence_feat_id", Integer,
ForeignKey("test_mol_sequence_feat.id")),
Column("mol_sequence_id", Integer, ForeignKey("test_mol_sequence.id")),
)
class TestMolecule(Base):
__tablename__ = "test_molecule"
id = Column(Integer, primary_key=True)
label = Column(String)
chains = relationship(
"TestChain",
secondary=test_molecule_chain,
collection_class=OrderedSet,
back_populates="molecules",
)
class TestMolSequence(Base):
__tablename__ = "test_mol_sequence"
id = Column(Integer, primary_key=True)
content = Column(String, nullable=False, unique=True)
parent_features = relationship(
"TestMolSequenceFeat",
secondary=test_mol_sequence_feat_mol_sequence,
collection_class=OrderedSet,
back_populates="feature_sequences",
single_parent=True,
)
chains = relationship(
"TestChain", back_populates="mol_sequence", collection_class=OrderedSet
)
class TestMolSequenceFeat(Base):
__tablename__ = "test_mol_sequence_feat"
id = Column(Integer, primary_key=True)
molecule_sequence_id = Column(
Integer, ForeignKey("test_mol_sequence.id", ondelete="CASCADE"),
)
molecule_sequence = relationship("TestMolSequence",)
start = Column(Integer)
stop = Column(Integer)
feature_sequences = relationship(
"TestMolSequence",
secondary=test_mol_sequence_feat_mol_sequence,
collection_class=OrderedSet,
back_populates="parent_features",
# single_parent=True,
)
class TestChain(Base):
__tablename__ = "test_chain"
id = Column(Integer, primary_key=True)
label = Column(String)
chain_type = Column(String)
mol_sequence_id = Column(Integer, ForeignKey("test_mol_sequence.id"))
mol_sequence = relationship("TestMolSequence", back_populates="chains")
molecules = relationship(
"TestMolecule",
secondary=test_molecule_chain,
collection_class=OrderedSet,
back_populates="chains",
)
var_regions = relationship(
"TestVarRegion",
secondary=test_chain_var_region,
collection_class=OrderedSet,
back_populates="chains",
)
const_regions = relationship(
"TestConstRegion",
secondary=test_chain_const_region,
collection_class=OrderedSet,
back_populates="chains",
)
class Te
[sqlalchemy] can you insert data for a model with a many to many relationship using alembic?
I'm trying to create an alembic bulk insert statement to add a row into a
model that has a relationship (many-to-many) with another table but don't
know if this is possible or if so what syntax to use.
In my current alembic file I do this in two or more steps:
1.) I add the rows to the table represented by the model
2.) I add the rows to the mixer table for the model and its related
model/table
like so:
g.session.bulk_insert_mappings(
CvConfiguration,
[
{
"controlled_vocabulary_type_id": controlled_vocabulary_type(
"cv_vessel_type"
),
"default_cv_id": cv_vessel_type("well_plate"),
},
],
return_defaults=True,
)
g.session.flush()
mix_cv_organization_cv_configuration_stmt =
mix_cv_organization_cv_configuration.insert().values(
[
{
"cv_organization_id":
cv_organization("biologics_generation_group"),
"cv_configuration_id": cv_configuration("cv_vessel_type",
"well_plate"),
},
],
)
g.session.execute(mix_cv_organization_cv_configuration_stmt)
I'd really like to combine the relationship into the bulk_insert_mapping if
possible, so if the relationship on the SqlAlchemy model is called
"used_by_cv_organizations" my insert looks something like this, with the
foreign key objects in a list or something.
g.session.bulk_insert_mappings(
CvConfiguration,
[
{
"controlled_vocabulary_type_id": controlled_vocabulary_type(
"cv_vessel_type"
),
"default_cv_id": cv_vessel_type("well_plate"),
"used_by_cv_organizations": [
cv_organization("biologics_generation_group")
],
},
],
return_defaults=True,
)
g.session.flush()
Is this possible? Does anyone know how to do it?
--
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/f7ddef2f-9152-4b5a-a60e-bf51794f531bn%40googlegroups.com.
Re: [sqlalchemy] Re: Can't delete cascade many-to-many with polymorphic relationships unless using lazy=dynamic
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 "", line 1, in
> 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,
Re: [sqlalchemy] Re: Can't delete cascade many-to-many with polymorphic relationships unless using lazy=dynamic
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 on behalf
> of Mike Bayer
> *Sent:* Thursday, December 17, 2020 7:08:58 PM
> *To:* noreply-spamdigest via sqlalchemy
> *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
> c
[sqlalchemy] Re: Can't delete cascade many-to-many with polymorphic relationships unless using lazy=dynamic
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("
[sqlalchemy] Can't delete cascade many-to-many with polymorphic relationships unless using lazy=dynamic
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/e6546493-673c-4aab-aa96-88984c1a2e1en%40googlegroups.com.
Re: [sqlalchemy] ORM AmbiguousForeignKeysErro
I'm having the same problem,
I have a base class called TrackedEntity that has child classes like
Request and others that inherit from it
on Request I wanted to put a reference to the id of the TrackedEntity that
created the Request
class Request(TrackedEntity, TrackedEntityContainer, VisibleIdMixin):
parent_tracked_entity_id = Column(UUID,
ForeignKey("tracked_entity.id"))
and I get the same error as above. Adding that inherit condition makes the
runtime error stop, but it doesn't make sense to me. Why can't I just have
a foreign key to that table? It's a simple many to one
@Richard: you can use @declared_attr.cascading to cascade the mapper_args
to your child classes.
On Friday, August 28, 2020 at 2:56:02 PM UTC-4 Richard Damon wrote:
> Thank you, so that go into each subclass that would have the problem.
>
> 8/28/20 2:37 PM, Mike Bayer wrote:
> > the argument you're looking for is inherit_condition:
> >
> >
> https://docs.sqlalchemy.org/en/13/orm/mapping_api.html#sqlalchemy.orm.mapper.params.inherit_condition
> >
> >
> > class Foo(...):
> >__mapper_args__ = {
> > "inherit_condition": node_id == Node.node_id
> >}
> >
>
> --
> Richard Damon
>
>
--
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/a515b477-5308-4609-af86-c0fb671151cen%40googlegroups.com.
