黄兵的个人博客

Specify the 'foreign_keys' argument

在处理SQLAlchemy模型的时候，出现了如下错误：

sqlalchemy.exc.InvalidRequestError: One or more mappers failed to initialize - can't proceed with initialization of other mappers. Triggering mapper: 'mapped class CoCode->co_code'. Original exception was: Could not determine join condition between parent/child tables on relationship CoCode.monitoring_server_lists - 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.

出现问题的原因：

一个或多个映射器初始化失败，无法继续进行其他映射器的初始化。

错误代码：

MonitoringServerList模型：

# 监控服务器列表
class MonitoringServerList(db.Model):
    __bind_key__ = 'ip_crawler_db'
    __tablename__ = 'monitoring_server_list'
    id = db.Column(db.Integer, primary_key=True)
    status_id = db.Column(db.Integer, db.ForeignKey('ip_crawler_monitoring_server_status.id'))
    country_id = db.Column(db.Integer, db.ForeignKey('co_code.id'))
    region_id = db.Column(db.Integer, db.ForeignKey('co_code.id'))
    ip_connection_id = db.Column(db.Integer, db.ForeignKey('ip_connection.id'))
    time_updated = db.Column(db.DateTime(timezone=True), onupdate=func.now())

CoCode模型：

class CoCode(db.Model):
    __bind_key__ = 'ip_crawler_db'
    __tablename__ = 'co_code'
    id = db.Column(db.Integer, primary_key=True)
    country_code = db.Column(db.String(8), unique=True)
    country_name_en = db.Column(db.String(128), unique=True)
    country_name_zh = db.Column(db.String(128), unique=True)
    country_flag_uri = db.Column(db.String(256))
    region_code = db.Column(db.String(8))
    region_name_en = db.Column(db.String(64))
    region_name_zh = db.Column(db.String(64))
    country_code_id = db.Column(db.Integer, db.ForeignKey('co_code.id'))
    monitoring_server_lists = db.relationship('MonitoringServerList', backref='co_code',
                                              lazy='dynamic')

可以看到MonitoringServerList模型里面有两个外键指向CoCode模型，但是CoCode模型里面只定义了一个到MonitoringServerList模型的反向引用。

解决方案：

修改CoCode模型：

class CoCode(db.Model):
    __bind_key__ = 'ip_crawler_db'
    __tablename__ = 'co_code'
    id = db.Column(db.Integer, primary_key=True)
    country_code = db.Column(db.String(8), unique=True)
    country_name_en = db.Column(db.String(128), unique=True)
    country_name_zh = db.Column(db.String(128), unique=True)
    country_flag_uri = db.Column(db.String(256))
    region_code = db.Column(db.String(8))
    region_name_en = db.Column(db.String(64))
    region_name_zh = db.Column(db.String(64))
    country_code_id = db.Column(db.Integer, db.ForeignKey('co_code.id'))
    monitoring_server_lists_country = db.relationship('MonitoringServerList', foreign_keys='country_id',
                                                      backref='country_region_code', lazy='dynamic')
    monitoring_server_lists_region = db.relationship('MonitoringServerList', foreign_keys='region_id',
                                                     backref='country_region_code', lazy='dynamic')

这里增加了foreign_keys，指定反向引用的外键。

参考资料：

1、SQLAlchemy multiple foreign keys in one mapped class to the same primary key