代码之家 › 专栏 › 技术社区 › Sophus

SQLAlchemy:如何创建1:n关系?[副本]

sqlalchemy python-2.7 python

-1

Sophus · 技术社区 · 7 年前

我给你带来了两张照片,你可以看看这是怎么回事。首先,您将看到EER模型的图片。在这个EER模型中,我在人家庭人桌子我们都知道,人们可以彼此建立关系(母亲、父亲、女儿、儿子、堂兄弟、堂兄弟、侄子等等)。因此,我设置了桌子

在我的源代码中,我将这个ORM模型映射如下:

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))

创建模型时,SQLAlchemy不会引起任何问题(echo是真的,所以我可以跟踪它,没有问题)。然而,当我启动用户界面时,SQLAlchemy抛出了一个异常,您可以在其中管理个人的数据。

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.

1 回复 | 直到 7 年前

rojeeer 7 年前

映射器类的定义中有两个主要问题 FAMILY .

(1) 您已推荐 PERSON 作为外键两次,因此必须明确告诉SQLAlchemy如何加入 人 ,请设置 foreign_keys 每个关系的参数。

backref . 的价值 反向参考 ( ),将成为 人 person 和 family_person 班

此外,在您的代码中,家庭和个人之间的关系是多对一的,因此 dynamic 值对于参数无效 lazy

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

以下是一些与您的问题相关的有用链接: Refer a table twice Relationship configration

谢谢