[sqlalchemy] Re: AmbiguousForeignKeysError
Correction on wordings:
One parent table, two child tables, one foreign key from each child
pointing to the same field in parent with "one to one relationship" works
with no problem, but getting "AmbiguousForeignKeysError" as soon as adding
the second foreign key to child tables pointing to the second field in
parent. tried various combinations but none has worked so far. specifically
tring to add foreign_keys as suggested in error message.
Here is the code that throwing error.
class Parent(Base):
__tablename__ = 'parent'
field_one = Column(String(256),
unique=True,
nullable=False,
primary_key=True)
field_two = Column(String(128),
nullable=False,
primary_key=True)
p_child_one_fields = relationship("ChildOne",
uselist=False,
passive_deletes=True,
backref=backref("ref_to_parent_field_one",
foreign_keys="[ChildOne.field_one,
ChildOne.field_two]"),
cascade="all, delete-orphan")
p_child_two_fields = relationship("ChildTwo",
uselist=False,
passive_deletes=True,
backref=backref("ref_to_parent_field_two",
foreign_keys="[ChildTwo.field_one,
ChildTwo.field_two]"),
cascade="all, delete-orphan")
class ChildOne(Base):
__tablename__ = 'child_one'
field_one = Column(String(256),
ForeignKey('parent.field_one',
onupdate="CASCADE",
ondelete='CASCADE'),
unique=True,
nullable=False,
primary_key=True)
field_two = Column(String(256),
ForeignKey('parent.field_two',
onupdate="CASCADE",
ondelete='CASCADE'),
unique=True,
nullable=False,
primary_key=True)
class ChildTwo(Base):
__tablename__ = 'child_two'
field_one = Column(String(256),
ForeignKey('parent.field_one',
onupdate="CASCADE",
ondelete='CASCADE'),
unique=True,
nullable=False,
primary_key=True)
field_two = Column(String(256),
ForeignKey('parent.field_two',
onupdate="CASCADE",
ondelete='CASCADE'),
unique=True,
nullable=False,
primary_key=True)
Any suggestion to fix the problem will be appreciated.
Do I need to have 4 relationships or can be done with two relationships ?
Thank you
On Sat, 30 May 2020 at 18:13, Sydo Luciani wrote:
>
> One parent table, two child tables, two foreign keys pointing to a field
> in parent with "one to one relationship" works with no problem, but getting
> "AmbiguousForeignKeysError" as soon as adding the second foreignkey to
> child table. tried various combinations but none has worked so far.
> specifically tring to add foreign_keys as suggested in error message.
>
> Here is the code that throwing error.
>
> class Parent(Base):
> __tablename__ = 'parent'
>
> field_one = Column(String(256),
> unique=True,
> nullable=False,
> primary_key=True)
>
> field_two = Column(String(128),
>nullable=False,
>primary_key=True)
>
> p_child_one_field_one = relationship("ChildOne",
> uselist=False,
> passive_deletes=True,
> backref=backref("ref_to_parent_field_one",
> foreign_keys="[ChildOne.field_one,
> ChildOne.field_two]"),
> cascade="all, delete-orphan")
>
> p_child_two_field_one = relationship("ChildTwo",
> uselist=False,
> passive_deletes=True,
> backref=backref("ref_to_parent_field_two",
> foreign_keys="[ChildTwo.field_one,
> ChildTwo.field_two]"),
> cascade="all, delete-orphan")
>
>
>
> class ChildOne(Base):
> __tablename__ = 'child_one'
>
> field_one = Column(String(256),
> ForeignKey('parent.field_one',
> onupdate="CASCADE",
> ondelete='CASCADE'),
> unique=True,
> nullable=False,
> primary_key=True)
>
> field_two = Column(String(256),
>
[sqlalchemy] AmbiguousForeignKeysError
One parent table, two child tables, two foreign keys pointing to a field in
parent with "one to one relationship" works with no problem, but getting
"AmbiguousForeignKeysError" as soon as adding the second foreignkey to
child table. tried various combinations but none has worked so far.
specifically tring to add foreign_keys as suggested in error message.
Here is the code that throwing error.
class Parent(Base):
__tablename__ = 'parent'
field_one = Column(String(256),
unique=True,
nullable=False,
primary_key=True)
field_two = Column(String(128),
nullable=False,
primary_key=True)
p_child_one_field_one = relationship("ChildOne",
uselist=False,
passive_deletes=True,
backref=backref("ref_to_parent_field_one",
foreign_keys="[ChildOne.field_one,
ChildOne.field_two]"),
cascade="all, delete-orphan")
p_child_two_field_one = relationship("ChildTwo",
uselist=False,
passive_deletes=True,
backref=backref("ref_to_parent_field_two",
foreign_keys="[ChildTwo.field_one,
ChildTwo.field_two]"),
cascade="all, delete-orphan")
class ChildOne(Base):
__tablename__ = 'child_one'
field_one = Column(String(256),
ForeignKey('parent.field_one',
onupdate="CASCADE",
ondelete='CASCADE'),
unique=True,
nullable=False,
primary_key=True)
field_two = Column(String(256),
ForeignKey('parent.field_two',
onupdate="CASCADE",
ondelete='CASCADE'),
unique=True,
nullable=False,
primary_key=True)
class ChildTwo(Base):
__tablename__ = 'child_two'
field_one = Column(String(256),
ForeignKey('parent.field_one',
onupdate="CASCADE",
ondelete='CASCADE'),
unique=True,
nullable=False,
primary_key=True)
field_two = Column(String(256),
ForeignKey('parent.field_two',
onupdate="CASCADE",
ondelete='CASCADE'),
unique=True,
nullable=False,
primary_key=True)
Any suggestion to fix the problem will be appreciated.
Thank you
--
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/CAJspodik6dc3y1rxDr6PRmse6oe7tFFiv%3DNEEaz_BXKBGf%3D9Rg%40mail.gmail.com.
Re: [sqlalchemy] Re: how can i remove an entry from relational database using sqlalchemy in python
If I had time to respond yesterday, I would have said the same thing as Simon. Your database model leverages two separate parts of SQLAlchemy: * SqlAlchemy ORM (Left side of the docs https://docs.sqlalchemy.org/en/13/ ) * SqlAlchemy Core (Right side of the docs https://docs.sqlalchemy.org/en/13/ ) There is nothing wrong with that setup; the two are often used together and the docs recommend that in many situations! However... the way you use and want to query the relationship table is more suited to redefining the permissions tables from SQLAlchemy Core objects (which are created by invoking `db.Table()`) into SQLAlchemy ORM classes that inherit from `db.model`. It is possible to keep these objects in "Core" and query them as-is, but that will have some tradeoffs: * your application code will mix ORM and Core, which can cause some maintenance headaches * changes to ORM and Core are independent of each other and may cause issues like race conditions if they overlap. For example, changes made to the database via Core would not necessarily appear to ORM objects/relationships if they are already loaded. There is no right way or wrong way here. Given your familiarity with this library though, I personally suggest keeping everything in the ORM. Going to the ORM docs, your setup right now is roughly in line with a "Many to Many" setup that leverages an `association_table` ( https://docs.sqlalchemy.org/en/13/orm/basic_relationships.html#many-to-many) However, your association_table is more than just primary keys joining the two sides of the relationship - it has other fields - and you want to be querying it directly. That is more in line with the "Association Object" pattern ( https://docs.sqlalchemy.org/en/13/orm/basic_relationships.html#association-object ) Altering your model to implementing the Association Object pattern is pretty straightforward and should be easy to do based on the examples in the docs. That should give you the flexibility you need. -- 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/9121424d-8298-4018-9e95-1d4e312604e2%40googlegroups.com.
