[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.
[sqlalchemy] AmbiguousForeignKeysError or CircularDependencyError
Hi,
I have a problem to do the model I need.
I have 3 types of object : Category, Picture and Video.
All of these types have some common part, like a name.
Also I want to be able to select all object with a name like bar
(Category, Picture and Video)
So I use the Joined_table inheritance pattern. All work fine.
But I want to add : they all can be child of a Category
So I done this model :
class Item(Base):
__tablename__ = 'item'
uid = Column(Integer, primary_key=True)
name = Column(String(50))
item_type = Column(String(50))
parent_id = Column(Integer, ForeignKey('category.uid'), nullable=True)
__mapper_args__ = {
'polymorphic_identity':'item',
'polymorphic_on':item_type,
}
###
class Picture(Item):
__tablename__ = 'picture'
uid = Column(Integer, ForeignKey('item.uid'), primary_key=True)
__mapper_args__ = {
'polymorphic_identity':'picture',
}
###
class Video(Item):
__tablename__ = 'picture'
uid = Column(Integer, ForeignKey('item.uid'), primary_key=True)
__mapper_args__ = {
'polymorphic_identity':'video',
}
###
class Category(Item):
__tablename__ = 'category'
uid = Column(Integer, ForeignKey('item.uid'), primary_key=True)
__mapper_args__ = {
'inherit_condition': (uid==Item.uid),
'polymorphic_identity': 'category'
}
First I add parent_id Column in the Item class. But when I do this, I have
AmbiguousForeignKeysError exception:
sqlalchemy.exc.AmbiguousForeignKeysError: Can't determine join between
'item' and 'category'; tables have more than one foreign key constraint
relationship between them. Please specify the 'onclause' of this join
explicitly.
After some research on internet (documentation, stack overflow and this
maling list) I heard about the 'inherit_condition'.
So I add it in the __mapper_args__ of Category. But when I do that, I have
a CircularDependencyError:
sqlalchemy.exc.CircularDependencyError: Circular dependency detected.
Cycles:
{
Table(
'item',
MetaData(bind=None),
Column('uid', Integer(), table=item, primary_key=True,
nullable=False),
Column('name', String(length=50), table=item),
Column('item_type', String(length=50), table=item),
Column('parent_id', Integer(), ForeignKey('category.uid'),
table=item),
schema=None),
Table(
'category',
MetaData(bind=None),
Column('uid', Integer(), ForeignKey('item.uid'), table=category,
primary_key=True, nullable=False),
schema=None)
}
all edges:
{
(
Table(
'item',
MetaData(bind=None),
Column('uid', Integer(), table=item, primary_key=True,
nullable=False),
Column('name', String(length=50), table=item),
Column('item_type', String(length=50), table=item),
Column('parent_id', Integer(), ForeignKey('category.uid'),
table=item),
schema=None),
Table(
'category',
MetaData(bind=None),
Column('uid', Integer(), ForeignKey('item.uid'),
table=category, primary_key=True, nullable=False),
schema=None)
),
(
Table(
'item',
MetaData(bind=None),
Column('uid', Integer(), table=item, primary_key=True,
nullable=False),
Column('name', String(length=50), table=item),
Column('item_type', String(length=50), table=item),
Column('parent_id', Integer(), ForeignKey('category.uid'),
table=item),
schema=None),
Table(
'video',
MetaData(bind=None),
Column('uid', Integer(), ForeignKey('item.uid'), table=video,
primary_key=True, nullable=False),
schema=None)
),
(
Table(
'item',
MetaData(bind=None),
Column('uid', Integer(), table=item, primary_key=True,
nullable=False),
Column('name', String(length=50), table=item),
Column('item_type', String(length=50), table=item),
Column('parent_id', Integer(), ForeignKey('category.uid'),
table=item),
schema=None),
Table(
'picture',
MetaData(bind=None),
Column('uid', Integer(), ForeignKey('item.uid'),
table=picture, primary_key=True, nullable=False),
schema=None)
),
(
Table(
'category',
MetaData(bind=None),
Column('uid', Integer(), ForeignKey('item.uid'),
table=category, primary_key=True, nullable=False),
schema=None),
Table(
'item',
MetaData(bind=None),
Column('uid', Integer(), table=item, primary_key=True,
nullable=False),
Column('name', String(length=50),
Re: [sqlalchemy] AmbiguousForeignKeysError or CircularDependencyError
On Thu, Dec 11, 2014 at 2:44 PM, Boris Sabatier
sabatier.bo...@gmail.com wrote:
Hi,
I have a problem to do the model I need.
I have 3 types of object : Category, Picture and Video.
All of these types have some common part, like a name.
Also I want to be able to select all object with a name like bar
(Category, Picture and Video)
So I use the Joined_table inheritance pattern. All work fine.
But I want to add : they all can be child of a Category
So I done this model :
class Item(Base):
__tablename__ = 'item'
uid = Column(Integer, primary_key=True)
name = Column(String(50))
item_type = Column(String(50))
parent_id = Column(Integer, ForeignKey('category.uid'), nullable=True)
__mapper_args__ = {
'polymorphic_identity':'item',
'polymorphic_on':item_type,
}
###
class Picture(Item):
__tablename__ = 'picture'
uid = Column(Integer, ForeignKey('item.uid'), primary_key=True)
__mapper_args__ = {
'polymorphic_identity':'picture',
}
###
class Video(Item):
__tablename__ = 'picture'
uid = Column(Integer, ForeignKey('item.uid'), primary_key=True)
__mapper_args__ = {
'polymorphic_identity':'video',
}
###
class Category(Item):
__tablename__ = 'category'
uid = Column(Integer, ForeignKey('item.uid'), primary_key=True)
__mapper_args__ = {
'inherit_condition': (uid==Item.uid),
'polymorphic_identity': 'category'
}
First I add parent_id Column in the Item class. But when I do this, I have
AmbiguousForeignKeysError exception:
sqlalchemy.exc.AmbiguousForeignKeysError: Can't determine join between
'item' and 'category'; tables have more than one foreign key constraint
relationship between them. Please specify the 'onclause' of this join
explicitly.
After some research on internet (documentation, stack overflow and this
maling list) I heard about the 'inherit_condition'.
So I add it in the __mapper_args__ of Category. But when I do that, I have a
CircularDependencyError:
sqlalchemy.exc.CircularDependencyError: Circular dependency detected.
The problem is that you've got a foreign key from category.uid to
item.uid, and also a foreign key from item.parent_id to
category.uid. Normally, foreign keys are defined in the CREATE
TABLE sql statement, but that can't work when you have a circular
dependency, because whichever table you define first will try to refer
to another table which doesn't exist yet.
The solution is use_alter, which is a flag you can pass when
defining your ForeignKey:
http://docs.sqlalchemy.org/en/rel_0_9/core/constraints.html#sqlalchemy.schema.ForeignKeyConstraint.params.use_alter
When it is set, SQLAlchemy will create the table *without* the foreign
key, then when all the tables have been defined it will run an ALTER
TABLE statement to create the foreign key.
Hope that helps,
Simon
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] AmbiguousForeignKeysError or CircularDependencyError
Thanks Simon it's work !!!
2014-12-11 16:06 GMT+01:00 Simon King si...@simonking.org.uk:
On Thu, Dec 11, 2014 at 2:44 PM, Boris Sabatier
sabatier.bo...@gmail.com wrote:
Hi,
I have a problem to do the model I need.
I have 3 types of object : Category, Picture and Video.
All of these types have some common part, like a name.
Also I want to be able to select all object with a name like bar
(Category, Picture and Video)
So I use the Joined_table inheritance pattern. All work fine.
But I want to add : they all can be child of a Category
So I done this model :
class Item(Base):
__tablename__ = 'item'
uid = Column(Integer, primary_key=True)
name = Column(String(50))
item_type = Column(String(50))
parent_id = Column(Integer, ForeignKey('category.uid'), nullable=True)
__mapper_args__ = {
'polymorphic_identity':'item',
'polymorphic_on':item_type,
}
###
class Picture(Item):
__tablename__ = 'picture'
uid = Column(Integer, ForeignKey('item.uid'), primary_key=True)
__mapper_args__ = {
'polymorphic_identity':'picture',
}
###
class Video(Item):
__tablename__ = 'picture'
uid = Column(Integer, ForeignKey('item.uid'), primary_key=True)
__mapper_args__ = {
'polymorphic_identity':'video',
}
###
class Category(Item):
__tablename__ = 'category'
uid = Column(Integer, ForeignKey('item.uid'), primary_key=True)
__mapper_args__ = {
'inherit_condition': (uid==Item.uid),
'polymorphic_identity': 'category'
}
First I add parent_id Column in the Item class. But when I do this, I have
AmbiguousForeignKeysError exception:
sqlalchemy.exc.AmbiguousForeignKeysError: Can't determine join between
'item' and 'category'; tables have more than one foreign key constraint
relationship between them. Please specify the 'onclause' of this join
explicitly.
After some research on internet (documentation, stack overflow and this
maling list) I heard about the 'inherit_condition'.
So I add it in the __mapper_args__ of Category. But when I do that, I have a
CircularDependencyError:
sqlalchemy.exc.CircularDependencyError: Circular dependency detected.
The problem is that you've got a foreign key from category.uid to
item.uid, and also a foreign key from item.parent_id to
category.uid. Normally, foreign keys are defined in the CREATE
TABLE sql statement, but that can't work when you have a circular
dependency, because whichever table you define first will try to refer
to another table which doesn't exist yet.
The solution is use_alter, which is a flag you can pass when
defining your ForeignKey:
http://docs.sqlalchemy.org/en/rel_0_9/core/constraints.html#sqlalchemy.schema.ForeignKeyConstraint.params.use_alter
When it is set, SQLAlchemy will create the table *without* the foreign
key, then when all the tables have been defined it will run an ALTER
TABLE statement to create the foreign key.
Hope that helps,
Simon
--
You received this message because you are subscribed to a topic in the Google
Groups sqlalchemy group.
To unsubscribe from this topic, visit
https://groups.google.com/d/topic/sqlalchemy/2MJwMTuSt3w/unsubscribe.
To unsubscribe from this group and all its topics, send an email to
sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] AmbiguousForeignKeysError or CircularDependencyError
Just a quick warning on this pattern (which I use as well)... When dealing with Edits/Updates and Database Migrations, you might run into issues where any mix of SqlAlchemy commands simply will not make the mapper happy. A workaround is to ignore the session and do these operations using the core SqlAlchemy engine -- you can access the tables off each Object Class , and the database connection off the session object. You run the risk of having different info in the session though, so need to avoid that. -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
