Re: [sqlalchemy] declarative Association table w/ foreign_keys in relationship() still raises "multiple foreign key paths linking the tables"
That's a good suggestion, thanks. -- 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 post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] declarative Association table w/ foreign_keys in relationship() still raises "multiple foreign key paths linking the tables"
leader/follower is often used in this case.
On 12/09/2016 10:33 AM, b...@sfi.ca wrote:
Thanks Mike for the quick reply.
I have changed the code so it uses backref on one side. The models now
get created w/o error. I haven't actually tested using the relationship
but I'm sure it's fine now.
For anyone else with this problem, here's the 'fixed' code. I didn't
change the Artifact_Reference table, it's the same as posted above. I'm
not keen on the naming master/slave.. maybe primary/secondary would be
better.. It's not exactly Parent/Child.. heh.
|
class Artifact_Relation(Base):
__tablename__ = 'artifact_relation'
master_artifact_id = Column(
Integer,
ForeignKey('artifact.id', name='artifact_relation_master_id_fk',
ondelete="cascade", onupdate="cascade"),
primary_key=True,
nullable=False
)
slave_artifact_id = Column(
Integer,
ForeignKey('artifact.id', name='artifact_relation_slave_id_fk',
ondelete="cascade", onupdate="cascade"),
primary_key=True,
nullable=False
)
relationship_type = Column(String(24), nullable=False)
slave = relationship("Artifact", backref=backref("masters"),
foreign_keys=[slave_artifact_id])
master = relationship("Artifact", backref=backref("slaves"),
foreign_keys=[master_artifact_id])
class Artifact(Base):
"Artifact"
__tablename__ = 'artifact'
id = Column('id', Integer, primary_key=True)
artifact_type = Column('artifact_type', String(16), nullable=False)
__mapper_args__ = {
'polymorphic_on': artifact_type
}
artifact_references = relationship(
'Artifact_Reference',
back_populates='artifact',
cascade="all, delete-orphan",
passive_deletes=True
)
|
--
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 post to this group, send email to sqlalchemy@googlegroups.com
.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] declarative Association table w/ foreign_keys in relationship() still raises "multiple foreign key paths linking the tables"
Thanks Mike for the quick reply.
I have changed the code so it uses backref on one side. The models now get
created w/o error. I haven't actually tested using the relationship but I'm
sure it's fine now.
For anyone else with this problem, here's the 'fixed' code. I didn't change
the Artifact_Reference table, it's the same as posted above. I'm not keen
on the naming master/slave.. maybe primary/secondary would be better.. It's
not exactly Parent/Child.. heh.
class Artifact_Relation(Base):
__tablename__ = 'artifact_relation'
master_artifact_id = Column(
Integer,
ForeignKey('artifact.id', name='artifact_relation_master_id_fk',
ondelete="cascade", onupdate="cascade"),
primary_key=True,
nullable=False
)
slave_artifact_id = Column(
Integer,
ForeignKey('artifact.id', name='artifact_relation_slave_id_fk',
ondelete="cascade", onupdate="cascade"),
primary_key=True,
nullable=False
)
relationship_type = Column(String(24), nullable=False)
slave = relationship("Artifact", backref=backref("masters"),
foreign_keys=[slave_artifact_id])
master = relationship("Artifact", backref=backref("slaves"),
foreign_keys=[master_artifact_id])
class Artifact(Base):
"Artifact"
__tablename__ = 'artifact'
id = Column('id', Integer, primary_key=True)
artifact_type = Column('artifact_type', String(16), nullable=False)
__mapper_args__ = {
'polymorphic_on': artifact_type
}
artifact_references = relationship(
'Artifact_Reference',
back_populates='artifact',
cascade="all, delete-orphan",
passive_deletes=True
)
--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] declarative Association table w/ foreign_keys in relationship() still raises "multiple foreign key paths linking the tables"
Hi,
Running SQLAlchemy==1.1.4
I'm getting this
AmbiguousForeignKeysError: Could not determine join condition between
> parent/child tables on relationship Artifact.slaves - there are multiple
> foreign key paths linking the tables. Specify the 'foreign_keys' argument,
> providing a list of those columns which should be counted as containing a
> foreign key reference to the parent table.
>
and I'm wondering if I'll have to use primaryjoin= statement instead of
relying on foreign_keys= to work around this.
The code is shown below. I've included Artifact_Reference for completeness
but it's probably not needed.
Is the problem on the Artifact_Relation model, or on the Artifact model?
I'm thinking the later, but I don't see any logical way to specify
foreign_keys on Artifact as it's on the one side of many to one.
I'm following this link for Artifact_Relation (i.e. Association
table)
http://docs.sqlalchemy.org/en/latest/orm/basic_relationships.html#association-object
where Artifact serves as both the Parent and Child
I'm following this link for multiple-path
resolution
http://docs.sqlalchemy.org/en/latest/orm/join_conditions.html#relationship-foreign-keys
Even though Artifact is on both sides of the association, I can't see
needing to use backref() w/ remote_id to resolve this,
ala http://docs.sqlalchemy.org/en/latest/orm/self_referential.html
Thanks for any suggestions..
Base = declarative_base()
class Artifact_Relation(Base):
__tablename__ = 'artifact_relation'
master_artifact_id = Column(
Integer,
ForeignKey('artifact.id', name='artifact_relation_master_id_fk',
ondelete="cascade", onupdate="cascade"),
primary_key=True,
nullable=False
)
slave_artifact_id = Column(
Integer,
ForeignKey('artifact.id', name='artifact_relation_slave_id_fk',
ondelete="cascade", onupdate="cascade"),
primary_key=True,
nullable=False
)
relationship_type = Column(String(24), nullable=False)
slave = relationship("Artifact", back_populates="masters",
foreign_keys=[slave_artifact_id])
master = relationship("Artifact", back_populates="slaves",
foreign_keys=[master_artifact_id])
class Artifact(Base):
"Artifact"
__tablename__ = 'artifact'
id = Column('id', Integer, primary_key=True)
artifact_type = Column('artifact_type', String(16), nullable=False)
__mapper_args__ = {
'polymorphic_on': artifact_type
}
artifact_references = relationship(
'Artifact_Reference',
back_populates='artifact',
cascade="all, delete-orphan",
passive_deletes=True
)
slaves = relationship("Artifact_Relation", back_populates="master")
masters = relationship("Artifact_Relation", back_populates="slave")
class Artifact_Reference(Base):
"""Artifact_Reference"""
__tablename__ = 'artifact_reference'
id = Column('id', Integer, primary_key=True)
type = Column('type', String(24), nullable=False)
artifact_id = Column('artifact_id', Integer, ForeignKey(Artifact.id,
name="artifact_reference_artifact_id_fk", ondelete="cascade",
onupdate="cascade"), nullable=False)
reference = Column('reference', String(64), nullable=False)
artifact = relationship(
Artifact,
back_populates='artifact_references'
)
Index('reference_idx', reference, unique=False)
--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
