On Fri, Sep 22, 2017 at 9:42 AM, Simon King <si...@simonking.org.uk> wrote:
> 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
Sorry, I meant for the child object at the end to be constructed like this:
child = Child(name='Joe', category='cheese', parent=parent)
...to prove that the relationship to the parent worked.
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.
