Re: [sqlalchemy] AmbiguousForeignKeysError or CircularDependencyError

2014-12-11 Thread Jonathan Vanasco 
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.

Re: [sqlalchemy] AmbiguousForeignKeysError or CircularDependencyError

2014-12-11 Thread Boris SABATIER 
Thanks Simon it's work !!!

2014-12-11 16:06 GMT+01:00 Simon King :
> On Thu, Dec 11, 2014 at 2:44 PM, Boris Sabatier
>  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

2014-12-11 Thread Simon King 
On Thu, Dec 11, 2014 at 2:44 PM, Boris Sabatier
 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.

[sqlalchemy] AmbiguousForeignKeysError or CircularDependencyError

2014-12-11 Thread Boris Sabatier 
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=, primary_key=True, 
nullable=False),
Column('name', String(length=50), table=),
Column('item_type', String(length=50), table=),
Column('parent_id', Integer(), ForeignKey('category.uid'), 
table=),
schema=None),
Table(
'category',
MetaData(bind=None),
Column('uid', Integer(), ForeignKey('item.uid'), table=, 
primary_key=True, nullable=False),
schema=None)
}
all edges:
{
(
Table(
'item',
MetaData(bind=None),
Column('uid', Integer(), table=, primary_key=True, 
nullable=False),
Column('name', String(length=50), table=),
Column('item_type', String(length=50), table=),
Column('parent_id', Integer(), ForeignKey('category.uid'), 
table=),
schema=None),
Table(
'category',
MetaData(bind=None),
Column('uid', Integer(), ForeignKey('item.uid'), 
table=, primary_key=True, nullable=False),
schema=None)
),
(
Table(
'item',
MetaData(bind=None),
Column('uid', Integer(), table=, primary_key=True, 
nullable=False),
Column('name', String(length=50), table=),
Column('item_type', String(length=50), table=),
Column('parent_id', Integer(), ForeignKey('category.uid'), 
table=),
schema=None),
Table(
'video',
MetaData(bind=None),
Column('uid', Integer(), ForeignKey('item.uid'), table=, 
primary_key=True, nullable=False),
schema=None)
),
(
Table(
'item',
MetaData(bind=None),
Column('uid', Integer(), table=, primary_key=True, 
nullable=False),
Column('name', String(length=50), table=),
Column('item_type', String(length=50), table=),
Column('parent_id', Integer(), ForeignKey('category.uid'), 
table=),
schema=None),
Table(
'picture',
MetaData(bind=None),
Column('uid', Integer(), ForeignKey('item.uid'), 
table=, primary_key=True, nullable=False),
schema=None)
),
(
Table(
'category',
MetaData(bind=None),
Column('uid', Integer(), ForeignKey('item.uid'), 
table=, primary_key=True, nullable=False),
schema=None),
Table(
'item',
MetaData(bind=None),
Column('uid', Integer(), table=, primary_key=True, 
nullable=False),
Column('name', String(length=50), table=),
Column('item_type', String(length=50), table=),
Column('parent_id',