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

Flask程序错误的log:

[2016-10-20 16:04:34 +0000] [20504] [ERROR] Error handling request /runningfast/api/v1.0/open/accesstoken
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 Task.initiator – 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.
[2016-10-20 16:04:46 +0000] [20488] [INFO] Handling signal: term

对应代码:

                foundUser = User.query.filter_by(phone=phone).first()
                gLog.debug(‘foundUser=%s’, foundUser)

对应的SQLAlchemy的数据模型定义:

/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’, foreign_keys=initiatorId)
    # errandor = db.relationship(‘User’, foreign_keys=errandorId)
    initiator = db.relationship(‘User’, back_populates="tasks")
    errandor = db.relationship(‘User’, back_populates="orders")

搜:

Flask SQLAlchemy AmbiguousForeignKeysError  Could not determine join condition between

python – sqlalchemy foreign key relationship attributes – Stack Overflow

Configuring how Relationship Joins — SQLAlchemy 1.1 Documentation

去改为:

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

好像是可以了。

不过又出现类似的问题:

[2016-10-20 16:49:12 +0000] [21430] [ERROR] Error handling request 
    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 Location.initiatorStartLocationTask – 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.

然后再去看看对应的Location.initiatorStartLocationTask:

class Location(db.Model):
    __tablename__ = ‘locations’
    id = db.Column(db.String(64), primary_key=True, default = generateUUID("location-"), nullable=False)
    longitude = db.Column(db.Float, nullable=False, default = 0.0)
    latitude = db.Column(db.Float, nullable=False, default=0.0)
    shortStr = db.Column(db.String(64), nullable=False, default = "")
    fullStr = db.Column(db.String(256), nullable=False, default="")
    initiatorStartLocationTaskId = db.Column(db.String(64), db.ForeignKey("tasks.id"))
    initiatorStartLocationTask = db.relationship("Task", back_populates="initiatorStartLocation")
    initiatorEndLocationTaskId = db.Column(db.String(64), db.ForeignKey("tasks.id"))
    initiatorEndLocationTask = db.relationship("Task", back_populates="initiatorEndLocation")
    errandorStartLocationTaskId = db.Column(db.String(64), db.ForeignKey("tasks.id"))
    errandorStartLocationTask = db.relationship("Task", back_populates="errandorStartLocation")
    errandorEndLocationTaskId = db.Column(db.String(64), db.ForeignKey("tasks.id"))
    errandorEndLocationTask = db.relationship("Task", back_populates="errandorEndLocation")
class Task(db.Model):
    __tablename__ = ‘tasks’
    initiatorStartLocation = db.relationship(‘Location’, uselist=False, back_populates="initiatorStartLocationTask")
    initiatorEndLocation = db.relationship(‘Location’, uselist=False, back_populates="initiatorEndLocationTask")

所以去改为:

    initiatorStartLocationTaskId = db.Column(db.String(64), db.ForeignKey("tasks.id"))
    initiatorStartLocationTask = db.relationship("Task", back_populates="initiatorStartLocation", foreign_keys=[initiatorStartLocationTaskId])
    initiatorEndLocationTaskId = db.Column(db.String(64), db.ForeignKey("tasks.id"))
    initiatorEndLocationTask = db.relationship("Task", back_populates="initiatorEndLocation", foreign_keys=[initiatorEndLocationTaskId])
    errandorStartLocationTaskId = db.Column(db.String(64), db.ForeignKey("tasks.id"))
    errandorStartLocationTask = db.relationship("Task", back_populates="errandorStartLocation", foreign_keys=[errandorStartLocationTaskId])
    errandorEndLocationTaskId = db.Column(db.String(64), db.ForeignKey("tasks.id"))
    errandorEndLocationTask = db.relationship("Task", back_populates="errandorEndLocation", foreign_keys=[errandorEndLocationTaskId])

【总结】

当一个表中出现多个:

ForeignKey

则SQLAlchemy无法识别,具体哪个属性,从哪个去找,所以报错:

AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship Task.initiator

解决办法:

给db.relationship添加foreign_keys,就可以指明具体是哪个属性了。

即,将:

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’, foreign_keys=initiatorId)
    # errandor = db.relationship(‘User’, foreign_keys=errandorId)
    initiator = db.relationship(‘User’, back_populates="tasks")
    errandor = db.relationship(‘User’, back_populates="orders")

改为:

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

即可。



发表评论

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

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