On Fri, Sep 22, 2017 at 8:18 AM, John Smith <johnsmith31...@gmail.com> wrote:
> I have the following database schema:
>
> Table "Parent":
> 1. id - primary key identifier.
> 2. type - polymorphic_identity.
> 3. name - string data column.
>
> Table "Child" - inherits Parent:
> 1. id - primary key identifier.
> 2. parent_id - foreignkey to Parent.
> 3. category - string data column.
>
> Summing up I have two tables. Table Child inherits from Parent and also have
> a foreignkey to it.
> I really need both inheritance and foreignkey. This example is only a short
> demo which reproduces the problem.
> My real database has 1000+ tables with complex inheritance.
>
> I used declarative_base to declare the schema:
>
>
>> # -*- coding: utf-8 -*-
>>
>> from sqlalchemy import Column, String, Integer, ForeignKey
>> from sqlalchemy.orm import relationship
>> from sqlalchemy.ext.declarative import declarative_base
>> from sqlalchemy import create_engine
>> from sqlalchemy.orm import sessionmaker
>>
>> Base = declarative_base()
>>
>> class Parent(Base):
>> __tablename__ = "Parent"
>> id = Column(Integer, primary_key=True)
>> type = Column(String(250))
>>
>> name = Column(String(250))
>>
>> __mapper_args__ = {
>> 'polymorphic_identity':'Parent',
>> 'polymorphic_on':type
>> }
>>
>> class Child(Parent):
>> __tablename__ = 'Child'
>> id = Column(Integer, ForeignKey('Parent.id'), primary_key=True)
>>
>> parent_id = Column(ForeignKey("Parent.id"), nullable=True)
>> category = Column(String(250))
>>
>> __mapper_args__ = {
>> 'polymorphic_identity':'Child',
>> }
>>
>> engine = create_engine('postgresql+psycopg2://joe:joe@localhost/alch')
>>
>> session = sessionmaker()
>> session.configure(bind=engine)
>> Base.metadata.create_all(engine)
>
>
> But when I run the code I get the following error:
>
>
>> sqlalchemy.exc.AmbiguousForeignKeysError: Can't determine join between
>> 'Parent' and 'Child'; tables have more than one foreign key constraint
>> relationship between them. Please specify the 'onclause' of this join
>> explicitly.
>
>
> I have tried to set relationship attribute for Parent or for Child
> separately and for both too. Tried to use primaryjoin and foreign_keys
> parameters of relationship. But the error was the same.
>
> I'm totally confused about this error.
> Please help. Thanks.
>
The docs at http://docs.sqlalchemy.org/en/latest/orm/inheritance.html say:
It is most common that the foreign key constraint
is established on the same column or columns
as the primary key itself, however this is not
required; a column distinct from the primary key
may also be made to refer to the parent via foreign
key. The way that a JOIN is constructed from the
base table to subclasses is also directly
customizable, however this is rarely necessary.
The last sentence is the important one. Normally there is only a
single foreign key from the child to the parent table, so SA can
automatically figure out the join condition. In your case, you've got
2 foreign keys, so SA is asking you to be explicit. However, it's the
*inheritance* join that is causing the problem, and the main docs
don't describe how to customize that.
However, if you look at the docs for the underlying mapper() function,
you'll find an "inherit_condition" parameter:
http://docs.sqlalchemy.org/en/latest/orm/mapping_api.html#sqlalchemy.orm.mapper.params.inherit_condition
Unfortunately it doesn't seem to accept strings, so you need to
construct your code such that you have direct access to the classes
themselves.
Here's a working version of your script:
# -*- coding: utf-8 -*-
from sqlalchemy import Column, String, Integer, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
Base = declarative_base()
class Parent(Base):
__tablename__ = "Parent"
id = Column(Integer, primary_key=True)
type = Column(String(250))
name = Column(String(250))
__mapper_args__ = {
'polymorphic_identity':'Parent',
'polymorphic_on':type
}
class Child(Parent):
__tablename__ = 'Child'
id = Column(Integer, ForeignKey('Parent.id'), primary_key=True)
parent_id = Column(ForeignKey("Parent.id"), nullable=True)
category = Column(String(250))
parent = relationship(Parent, foreign_keys=[parent_id])
__mapper_args__ = {
'polymorphic_identity':'Child',
'inherit_condition': id == Parent.id,
}
engine = create_engine('sqlite:///:memory:', echo=True)
Session = sessionmaker()
Session.configure(bind=engine)
Base.metadata.create_all(engine)
session = Session()
parent = Parent(name='Bob')
child = Child(name='Joe', category='cheese')
session.add_all([parent, child])
session.flush()
Hope that helps,
Simon
--
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.
