sqlalchemy foreignkey relation with inheritance

[John Smith]

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.

[John Smith]

Btw, I'm libraries versions are

SQLAlchemy==1.1.14
psycopg2==2.7.1

[Simon King]

On Fri, Sep 22, 2017 at 9:42 AM, Simon King <si...@simonking.org.uk> wrote:

> 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

[Simon King]

On Fri, Sep 22, 2017 at 8:18 AM, John Smith <johnsmi...@gmail.com> wrote:

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',

[John Smith]

Whoa! Fantastic! Thank you very much!!!