SQLAlchemy: How do I create a 1:n relationship?

Question

I am about to create a certain 1:n-realtionship with SQLAlchey.

I have brought two pictures for you, so you can see what this is about. First you'll see a picture of the EER model. In this EER model, I created a 1: n relationship between the person table and the family table. Any person is stored in the person table. And we all know that people can be in a relationship with one another (mother, father, daughter, son, cousin, cousin, nephew, etc ...). For this reason I have set up the family table.

In order to play the whole time exemplary, I have filled the tables with fictitious data. With a look at the family table, we see in the first column, which is the central person, namely Hans Schmidt (with the ID 1). In the Family table the following persons are saved by their foreign key: Kurt Schmidt (son), Gerda Schmidt (wife), Gisela Schmidt (Hans' mother), and Julia Schmidt (daughter). I would like to point out Hans Schmidt's relationship.

And in my source code, I have mapped this ORM model as follows:

class FAMILY(Base):

    __tablename__ = "family"

    id = Column(Integer, primary_key=True, unique=True, autoincrement=True)
    status = Column(String(255), nullable=False)

    person_id = Column(Integer, ForeignKey('person.id'))
    person = relationship("PERSON", backref='family', lazy='dynamic')

    family_person_id = Column(Integer, ForeignKey('person.id'))
    family_person = relationship("PERSON", backref='family', lazy='dynamic')


class PERSON(Base):

    __tablename__ = "person"

    id = Column(Integer, primary_key=True, unique=True, autoincrement=True)
    nickname = Column(String(255))
    alias_name  = Column(String (255))
    name_normally_used = Column(String(50), nullable=False)
    first_middle_name = Column(String(255))
    last_name = Column(String(100))

When creating the model, SQLAlchemy does not cause any problems (echo is true, so I could track it there were no problems.). However, SQLAlchemy throws an exception when I start the user interface, where you can manage the data of a person.

Traceback (most recent call last):
File "D:\Dan\Python\Xarphus\xarphus\subclass_master_data_load_data_item.py", line 140, in populate_item
self.populate_item_signal.emit(next(self._element))
File "D:\Dan\Python\Xarphus\xarphus\core\manage_data_manipulation_master_data.py", line 205, in select_all
for record in dict_store_session_query[category]():
File "D:\Dan\Python\Xarphus\xarphus\core\manage_data_manipulation_master_data.py", line 191, in <lambda>
'person_gender': lambda: self._session.query(PERSON_GENDER),
File "C:\Python27\lib\site-packages\sqlalchemy\orm\session.py", line 1362, in query
return self._query_cls(entities, self, **kwargs)
File "C:\Python27\lib\site-packages\sqlalchemy\orm\query.py", line 139, in __init__
self._set_entities(entities)
File "C:\Python27\lib\site-packages\sqlalchemy\orm\query.py", line 150, in _set_entities
self._set_entity_selectables(self._entities)
File "C:\Python27\lib\site-packages\sqlalchemy\orm\query.py", line 180, in _set_entity_selectables
ent.setup_entity(*d[entity])
File "C:\Python27\lib\site-packages\sqlalchemy\orm\query.py", line 3585, in setup_entity
self._with_polymorphic = ext_info.with_polymorphic_mappers
File "C:\Python27\lib\site-packages\sqlalchemy\util\langhelpers.py", line 764, in __get__
obj.__dict__[self.__name__] = result = self.fget(obj)
File "C:\Python27\lib\site-packages\sqlalchemy\orm\mapper.py", line 1948, in _with_polymorphic_mappers
configure_mappers()
File "C:\Python27\lib\site-packages\sqlalchemy\orm\mapper.py", line 2869, in configure_mappers
raise e
InvalidRequestError: One or more mappers failed to initialize - can't proceed with initialization of other mappers. Triggering mapper: 'Mapper|FAMILY|family'. Original exception was: Could not determine join condition between parent/child tables on relationship FAMILY.person - there are multiple foreign key paths linking the tables. Specify the 'foreign_keys' argument, providing a list of those columns which should be counted as containing a foreign key reference to the parent table.

Traceback (most recent call last):
File "D:\Dan\Python\Xarphus\xarphus\subclass_master_data_load_data_item.py", line 140, in populate_item
self.populate_item_signal.emit(next(self._element))
File "D:\Dan\Python\Xarphus\xarphus\core\manage_data_manipulation_master_data.py", line 205, in select_all
for record in dict_store_session_query[category]():
File "D:\Dan\Python\Xarphus\xarphus\core\manage_data_manipulation_master_data.py", line 194, in <lambda>
'person_title': lambda: self._session.query(PERSON_TITLE),
File "C:\Python27\lib\site-packages\sqlalchemy\orm\session.py", line 1362, in query
return self._query_cls(entities, self, **kwargs)
File "C:\Python27\lib\site-packages\sqlalchemy\orm\query.py", line 139, in __init__
self._set_entities(entities)
File "C:\Python27\lib\site-packages\sqlalchemy\orm\query.py", line 150, in _set_entities
self._set_entity_selectables(self._entities)
File "C:\Python27\lib\site-packages\sqlalchemy\orm\query.py", line 180, in _set_entity_selectables
ent.setup_entity(*d[entity])
File "C:\Python27\lib\site-packages\sqlalchemy\orm\query.py", line 3585, in setup_entity
self._with_polymorphic = ext_info.with_polymorphic_mappers
File "C:\Python27\lib\site-packages\sqlalchemy\util\langhelpers.py", line 764, in __get__
obj.__dict__[self.__name__] = result = self.fget(obj)
File "C:\Python27\lib\site-packages\sqlalchemy\orm\mapper.py", line 1948, in _with_polymorphic_mappers
configure_mappers()
File "C:\Python27\lib\site-packages\sqlalchemy\orm\mapper.py", line 2872, in configure_mappers
mapper._post_configure_properties()
File "C:\Python27\lib\site-packages\sqlalchemy\orm\mapper.py", line 1765, in _post_configure_properties
prop.init()
File "C:\Python27\lib\site-packages\sqlalchemy\orm\interfaces.py", line 184, in init
self.do_init()
File "C:\Python27\lib\site-packages\sqlalchemy\orm\relationships.py", line 1654, in do_init
self._setup_join_conditions()
File "C:\Python27\lib\site-packages\sqlalchemy\orm\relationships.py", line 1729, in _setup_join_conditions
can_be_synced_fn=self._columns_are_mapped
File "C:\Python27\lib\site-packages\sqlalchemy\orm\relationships.py", line 1987, in __init__
self._determine_joins()
File "C:\Python27\lib\site-packages\sqlalchemy\orm\relationships.py", line 2114, in _determine_joins
% self.prop)
AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship FAMILY.person - there are multiple foreign key paths linking the tables. Specify the 'foreign_keys' argument, providing a list of those columns which should be counted as containing a foreign key reference to the parent table.

And there we would be. SQLALchey apparently has a problem with the two foreign keys that come from the same table? Question to you: How should modeling be done best to show the interrelationship between people? Because after my reasoning, I need at least two foreign keys from the person table, in order to establish a relationship among the persons.

When faced with an exeption, try searching with the exception text a bit. SQLA has no problems with 2 foreign keys pointing to the same key, but it's not psychic, so you have to define which foreign key to use in a relationship. — Ilja Everilä, Aug 22 '17 at 19:17
The duplicate helps with that problem, but I must admit I don't understand the model at all: your families really have 2 persons: "a person and a family person"? Or what is this supposed to mean? — Antti Haapala -- Слава Україні, Aug 22 '17 at 19:22

score 1 · Accepted Answer · answered Aug 22 '17 at 19:26

There are two main problems in your definition of mapper class FAMILY.

(1) You have referred PERSON as foreign key twice, so you have to explicitly tell SQLAlchemy how to join the PERSON, please set foreign_keys parameter for each relationship.

(2) You have used the same value as backref. The value of backref (FAMILY), would become the property name of PERSON class. Look in your FAMILY, there are two properties named person and family_person, then there should be two different properties for the PERSON class.

Also, in your code, the relationship between FAMILY and PERSON is MANY TO ONE, thus the dynamic value is not valid for the parameter lazy, you should use the default one. Here is the sample code:

class FAMILY(Base):

    __tablename__ = "family"

    id = Column(Integer, primary_key=True, unique=True, autoincrement=True)
    status = Column(String(255), nullable=False)

    person_id = Column(Integer, ForeignKey('person.id'))
    person = relationship("PERSON", backref='family_to',
                          foreign_keys=[person_id],
                          )

    family_person_id = Column(Integer, ForeignKey('person.id'))
    family_person = relationship("PERSON",
                                 foreign_keys=[family_person_id],
                                 backref='family_from')

Here are some useful links related to your question: Refer a table twice, Relationship configration

Thanks!

TA. Your solution works like a charm. – Sophus Aug 22 '17 at 21:03 — Sophus, Aug 22 '17 at 21:03

SQLAlchemy: How do I create a 1:n relationship?

1 Answers1