【已解决】Flask中SQLAlchemy再次出错：AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship User.tasks

在：

【已解决】Flask中SQLAlchemy出错：AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship

之后，又遇到，看起来类似的错误：

对应代码：

/Users/crifan/dev/dev_root/daryun/Projects/RunningFast/sourcecode/RunningFast-Server/runningfast/models/init.py

其中上面的initiator和errandor，都已经加了foreign_keys了。

但是此处报错的是：User.tasks

所以是出错的是：

User中的：

    tasks = db.relationship(‘Task’, back_populates=’initiator’)

此处和之前不一样的是：

之前的Task中的initiator，分别可以直接使用自己Task中的initiatorId和errandorId，传递给foreign_keys

此处的User中的tasks，其实是希望：

指向另外的表，Task中的initiatorId

而想要去写：

但是很明显，此处找不到：

initiatorId

所以，此处需要知道：

relationship中的参数foreign_keys，到底改如何传递

而旧的别人的回答：

python – sqlalchemy foreign key relationship attributes – Stack Overflow

用：

字符串，内容是：Friend.user_id

就可以了。

但是最新的教程：

Configuring how Relationship Joins — SQLAlchemy 1.1 Documentation

是

列表，内容是：当前表的类中的某个属性的名字

比如我上面的：

[initiatorId]

其中：

initiatorId是当前类Task中的字段

而此处需要知道：

对于User的tasks

此处到底是否应该使用foreign_keys

是否应该去引用外部的类，Task中的initiatorId字段

是否应该写成：

字符串”Task.initiatorId"

还是：列表：[Task.initiatorId]

所以搜：

sqlalchemy relationship

Relationships API — SQLAlchemy 1.1 Documentation

“

• foreign_keys¶ – 
  a list of columns which are to be used as “foreign key” columns, or columns which refer to the value in a remote column, within the context of this relationship() object’s primaryjoin condition. That is, if the primaryjoin condition of this relationship() is a.id == b.a_id, and the values in b.a_id are required to be present in a.id, then the “foreign key” column of this relationship() is b.a_id.

  In normal cases, the foreign_keys parameter is not required. relationship()will automatically determine which columns in the primaryjoin conditition are to be considered “foreign key” columns based on those Column objects that specify ForeignKey, or are otherwise listed as referencing columns in aForeignKeyConstraint construct. foreign_keys is only needed when:

  1. There is more than one way to construct a join from the local table to the remote table, as there are multiple foreign key references present. Setting foreign_keys will limit the relationship()to consider just those columns specified here as “foreign”.

     Changed in version 0.8: A multiple-foreign key join ambiguity can be resolved by setting the foreign_keys parameter alone, without the need to explicitly set primaryjoin as well.

  2. The Table being mapped does not actually have ForeignKey or ForeignKeyConstraint constructs present, often because the table was reflected from a database that does not support foreign key reflection (MySQL MyISAM).
  3. The primaryjoin argument is used to construct a non-standard join condition, which makes use of columns or expressions that do not normally refer to their “parent” column, such as a join condition expressed by a complex comparison using a SQL function.

  The relationship() construct will raise informative error messages that suggest the use of the foreign_keys parameter when presented with an ambiguous condition. In typical cases, if relationship() doesn’t raise any exceptions, theforeign_keys parameter is usually not needed.

  foreign_keys may also be passed as a callable function which is evaluated at mapper initialization time, and may be passed as a Python-evaluable string when using Declarative.

  See also

  Handling Multiple Join Paths

  Creating Custom Foreign Conditions

  foreign() – allows direct annotation of the “foreign” columns within a primaryjoincondition.

  New in version 0.8: The foreign() annotation can also be applied directly to the primaryjoin expression, which is an alternate, more specific system of describing which columns in a particular primaryjoin should be considered “foreign”.

”

-》

Defining Constraints and Indexes — SQLAlchemy 1.1 Documentation

Defining Constraints and Indexes — SQLAlchemy 1.1 Documentation

“

node = Table(

    ‘node’, metadata,

    Column(‘node_id’, Integer, primary_key=True),

    Column(

        ‘primary_element’, Integer,

        ForeignKey(‘element.element_id’)

    )

)

element = Table(

    ‘element’, metadata,

    Column(‘element_id’, Integer, primary_key=True),

    Column(‘parent_node_id’, Integer),

    ForeignKeyConstraint(

        [‘parent_node_id’], [‘node.node_id’],

        name=’fk_element_parent_node_id’

    )

)

”

-》

此处，估计可以写成：

[Task.initiatorId]

Configuring how Relationship Joins — SQLAlchemy 1.1 Documentation

“

billing_address = relationship("Address", foreign_keys="[Customer.billing_address_id]")

只有一个情况下，没必要使用list，所以可以写成：

billing_address = relationship("Address", foreign_keys="Customer.billing_address_id")

"

->

的确应该是可以写成：

[Task.initiatorId]

或：

“[Task.initiatorId]"

或：

“Task.initiatorId"

的。

先去试试：

[Task.initiatorId]

不过要注意：

Task的定义要在User之前

否则会报错：

NameError: name ‘Task’ is not defined

好像就解决了此处的错误了。

【总结】

Flask中SQLAlchemy出现AmbiguousForeignKeysError的话，此处，如果想要引用别的类（表）中的字段作为foreign key的话，则可以写成：

别的类名.别的类的外部字段

完整代码：

注：

此处User中用到了Task

所以此处Task的定义必须要在User之前才可以。

转载请注明：在路上 » 【已解决】Flask中SQLAlchemy再次出错：AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship User.tasks