【已解决】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

之后,又遇到,看起来类似的错误:

Traceback (most recent call last):
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/gunicorn/workers/sync.py", line 135, in handle
    self.handle_request(listener, req, client, addr)
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/gunicorn/workers/sync.py", line 176, in handle_request
    respiter = self.wsgi(environ, resp.start_response)
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/flask/app.py", line 2000, in __call__
    return self.wsgi_app(environ, start_response)
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/flask/app.py", line 1991, in wsgi_app
    response = self.make_response(self.handle_exception(e))
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/flask_restful/__init__.py", line 271, in error_router
    return original_handler(e)
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/flask/app.py", line 1567, in handle_exception
    reraise(exc_type, exc_value, tb)
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/flask_restful/__init__.py", line 268, in error_router
    return self.handle_error(e)
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/flask/app.py", line 1988, in wsgi_app
    response = self.full_dispatch_request()
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/flask/app.py", line 1641, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/flask_restful/__init__.py", line 271, in error_router
    return original_handler(e)
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/flask/app.py", line 1544, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/flask_restful/__init__.py", line 268, in error_router
    return self.handle_error(e)
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/flask/app.py", line 1639, in full_dispatch_request
    rv = self.dispatch_request()
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/flask/app.py", line 1625, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/flask_restful/__init__.py", line 477, in wrapper
    resp = resource(*args, **kwargs)
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/flask/views.py", line 84, in view
    return self.dispatch_request(*args, **kwargs)
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/flask_restful/__init__.py", line 587, in dispatch_request
    resp = meth(*args, **kwargs)
  File "/root/RunningFast/staging/runningfast/resources/Accesstoken.py", line 193, in post
    foundUser = User.query.filter_by(phone=phone).first()
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/flask_sqlalchemy/__init__.py", line 498, in __get__
    mapper = orm.class_mapper(type)
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/sqlalchemy/orm/base.py", line 421, in class_mapper
    mapper = _inspect_mapped_class(class_, configure=configure)
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/sqlalchemy/orm/base.py", line 400, in _inspect_mapped_class
    mapper._configure_all()
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py", line 1222, in _configure_all
    configure_mappers()
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py", line 2840, in configure_mappers
    mapper._post_configure_properties()
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py", line 1765, in _post_configure_properties
    prop.init()
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/sqlalchemy/orm/interfaces.py", line 183, in init
    self.do_init()
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/sqlalchemy/orm/relationships.py", line 1653, in do_init
    self._setup_join_conditions()
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/sqlalchemy/orm/relationships.py", line 1728, in _setup_join_conditions
    can_be_synced_fn=self._columns_are_mapped
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/sqlalchemy/orm/relationships.py", line 1986, in __init__
    self._determine_joins()
  File "/root/Envs/RunningFast/lib/python2.7/site-packages/sqlalchemy/orm/relationships.py", line 2113, in _determine_joins
    % self.prop)
AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship User.tasks – 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.

对应代码:

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

class User(db.Model):
    __tablename__ = ‘users’
    id = db.Column(db.String(64), primary_key=True, default = generateUUID("user-"), nullable=False)
    
    tasks = db.relationship(‘Task’, back_populates=’initiator”)
    orders = db.relationship(‘Task’, back_populates=’errandor’)
class Task(db.Model):
    __tablename__ = ‘tasks’
    id = db.Column(db.String(64), primary_key=True, default = generateUUID("task-"), nullable=False)
    
    initiatorId = db.Column(db.String(64), db.ForeignKey("users.id"))
    errandorId = db.Column(db.String(64), db.ForeignKey("users.id"))
    initiator = db.relationship(‘User’, back_populates="tasks", foreign_keys=[initiatorId])
    errandor = db.relationship(‘User’, back_populates="orders", foreign_keys=[errandorId])

其中上面的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

而想要去写:

tasks = db.relationship(‘Task’, back_populates=’initiator’, foreign_keys=[initiatorId])

但是很明显,此处找不到:

initiatorId

所以,此处需要知道:

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

而旧的别人的回答:

python – sqlalchemy foreign key relationship attributes – Stack Overflow

class Friend(Base):
    __tablename__ = ‘friend’
    user_id = Column(Integer, ForeignKey(User.id), primary_key=True)
    friend_id = Column(Integer, ForeignKey(User.id), primary_key=True)
    request_status = Column(Boolean)
    user = relationship(‘User’, foreign_keys=’Friend.user_id’)
    friend = relationship(‘User’, foreign_keys=’Friend.friend_id’)

用:

字符串,内容是: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]

    tasks = db.relationship(‘Task’, back_populates=’initiator’, foreign_keys=[Task.initiatorId])
    orders = db.relationship(‘Task’, back_populates=’errandor’, foreign_keys=[Task.errandorId])

不过要注意:

Task的定义要在User之前

否则会报错:

NameError: name ‘Task’ is not defined

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

【总结】

Flask中SQLAlchemy出现AmbiguousForeignKeysError的话,此处,如果想要引用别的类(表)中的字段作为foreign key的话,则可以写成:

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

tasks = db.relationship(‘Task’, back_populates=’initiator’, foreign_keys=[Task.initiatorId])

完整代码:

class Task(db.Model):
    __tablename__ = ‘tasks’
    id = db.Column(db.String(64), primary_key=True, default = generateUUID("task-"), nullable=False)
    initiatorId = db.Column(db.String(64), db.ForeignKey("users.id"))
    errandorId = db.Column(db.String(64), db.ForeignKey("users.id"))
    initiator = db.relationship(‘User’, back_populates="tasks", foreign_keys=[initiatorId])
    errandor = db.relationship(‘User’, back_populates="orders", foreign_keys=[errandorId])
class User(db.Model):
    __tablename__ = ‘users’
    id = db.Column(db.String(64), primary_key=True, default = generateUUID("user-"), nullable=False)
    tasks = db.relationship(‘Task’, back_populates=’initiator’, foreign_keys=[Task.initiatorId])
    orders = db.relationship(‘Task’, back_populates=’errandor’, foreign_keys=[Task.errandorId])

注:

此处User中用到了Task

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



发表评论

电子邮件地址不会被公开。 必填项已用*标注

无觅相关文章插件,快速提升流量