python - 自引用表 [SQLAlchemy/Alembic] 中的外键不明确

Question

我有一个这样定义的模型类。这个想法是 Person 将保存关于人的一般信息以及对其“子类”的引用:作为 parent 的女人和男人。女性和男性将持有特定于该性别的信息。

我正在使用 Alembic 生成迁移，但出现错误:

sqlalchemy.exc.AmbiguousForeignKeysError:无法确定“人”和“女人”之间的连接；表之间有多个外键约束关系。请明确指定此连接的“onclause”。

请注意，Woman 继承自 Person，因此也存在从 woman 到 person 的引用。

我试过都没用:

• 删除 mother = relationship('Woman') 行，认为 SQL alchemy 不理解这种关系
• 添加 mother_id = Column(Integer, ForeignKey('woman.id'), nullable=True)
• 添加 mother = relationship('Woman', primaryjoin='Person.mother_id==Mother.id')

• 指定 onclause 是不可能的(或者我不知道如何)，因为连接是自动生成的

  class Person(Base):
      __tablename__ = 'person'
  
      id = Column(Integer, primary_key=True)
      gender = Column(String(5))
      name = Column(String(32), nullable=False)
      surname = Column(String(32), nullable=False)
      age = Column(Float, primary_key=True)
  
      mother_id = Column(Integer, ForeignKey('woman.id'), nullable=True)
      mother = relationship('Woman')
  
      _mapper_args__ = {
          'polymorphic_on': gender
      }
  
  
  class Woman(Person):
      __tablename__ = 'woman'
      __mapper_args__ = {
          'polymorphic_identity': 'woman'
      }
  
      id = Column(Integer, ForeignKey('person.id'), primary_key=True)
  

完整跟踪:

$ alembic revision --autogenerate -m "mother"
Traceback (most recent call last):
  File "/Library/Frameworks/Python.framework/Versions/3.4/bin/alembic", line 11, in <module>
    sys.exit(main())
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/alembic/config.py", line 479, in main
    CommandLine(prog=prog).main(argv=argv)
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/alembic/config.py", line 473, in main
    self.run_cmd(cfg, options)
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/alembic/config.py", line 456, in run_cmd
    **dict((k, getattr(options, k)) for k in kwarg)
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/alembic/command.py", line 117, in revision
    script_directory.run_env()
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/alembic/script/base.py", line 416, in run_env
    util.load_python_file(self.dir, 'env.py')
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/alembic/util/pyfiles.py", line 93, in load_python_file
    module = load_module_py(module_id, path)
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/alembic/util/compat.py", line 68, in load_module_py
    module_id, path).load_module(module_id)
  File "<frozen importlib._bootstrap>", line 539, in _check_name_wrapper
  File "<frozen importlib._bootstrap>", line 1614, in load_module
  File "<frozen importlib._bootstrap>", line 596, in _load_module_shim
  File "<frozen importlib._bootstrap>", line 1220, in load
  File "<frozen importlib._bootstrap>", line 1200, in _load_unlocked
  File "<frozen importlib._bootstrap>", line 1129, in _exec
  File "<frozen importlib._bootstrap>", line 1471, in exec_module
  File "<frozen importlib._bootstrap>", line 321, in _call_with_frames_removed
  File "migrations/env.py", line 24, in <module>
    import api.model
  File "/Users/jakubt/Projects/hex/api/model.py", line 31, in <module>
    class Woman(Person):
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/sqlalchemy/ext/declarative/api.py", line 64, in __init__
    _as_declarative(cls, classname, cls.__dict__)
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/sqlalchemy/ext/declarative/base.py", line 88, in _as_declarative
    _MapperConfig.setup_mapping(cls, classname, dict_)
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/sqlalchemy/ext/declarative/base.py", line 103, in setup_mapping
    cfg_cls(cls_, classname, dict_)
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/sqlalchemy/ext/declarative/base.py", line 135, in __init__
    self._early_mapping()
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/sqlalchemy/ext/declarative/base.py", line 138, in _early_mapping
    self.map()
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/sqlalchemy/ext/declarative/base.py", line 530, in map
    **self.mapper_args
  File "<string>", line 2, in mapper
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/sqlalchemy/orm/mapper.py", line 671, in __init__
    self._configure_inheritance()
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/sqlalchemy/orm/mapper.py", line 978, in _configure_inheritance
    self.local_table)
  File "<string>", line 2, in join_condition
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/sqlalchemy/sql/selectable.py", line 965, in _join_condition
    a, b, constraints, consider_as_foreign_keys)
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/sqlalchemy/sql/selectable.py", line 1055, in _joincond_trim_constraints
    "join explicitly." % (a.description, b.description))
sqlalchemy.exc.AmbiguousForeignKeysError: Can't determine join between 'person' and 'woman'; tables have more than one foreign key constraint relationship between them. Please specify the 'onclause' of this join explicitly.

Answer 1

我在这里看到的问题是您将外键引用到外键。

mother_id -> foreign_key(woman.id)
woman.id -> foreign_key(person.id)

然后你创建了循环依赖。您不能在任何 SQL 方言的任何单个创建语句中创建该依赖项。您首先需要创建 person 表，然后是 woman 表，然后从第一个表到第二个表添加 外键 (mother_id) (请注意，您不能首先创建 women 表，因为它具有指向 person 表的外键)。

我认为 alembic 不够聪明，无法做到这一点，而且我认为您想要实现的目标并不是一件好事。

这是我的建议:

woman.id 对于一条记录总是与 person.id 具有相同的值，因此第一个外键应该引用 person 表而不是 mother 表。

改变 mother_id 和 mother 的定义:

mother_id = Column(Integer, ForeignKey('person.id'), nullable=True)
mother = relationship('Person', primaryjoin='Person.mother_id==Person.id')

提示:如果您只存储 id，则不需要第二个表(只需从第二个类中删除 __tablename__)

并在添加母亲/父亲时检查性别代码。