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

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

  •  -1
  • Sophus  · 技术社区  · 7 年前

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

    enter image description here

    enter image description here

    在我的源代码中,我将这个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 年前
        1
  •  1
  •   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

    谢谢