flask-sqlalchemy 字段关联问题（sqlalchemy.exc.NoForeignKeysError）

Question

错误信息：

sqlalchemy.exc.NoForeignKeysError: Could not determine join condition between parent/child tables on relationship version_patch.patchcode - there are no foreign keys linking these tables. Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or specify a 'primaryjoin' expression.

代码：

class trans_inst(db.Model):

    __tablename__ = 'TRANS_INST'
    __table_args__ = SCHEMA         # 查询带上库名前缀

    TRANS_ID = db.Column(db.INTEGER, primary_key=True)
    TITLE = db.Column(db.VARCHAR(255), nullable=False)
    VERSION_ID = db.Column(db.INTEGER)
    PROJECT_ID = db.Column(db.INTEGER, nullable=False)
    TEMPLET_ID = db.Column(db.INTEGER)
    STAFF_ID = db.Column(db.INTEGER, nullable=False)
    CREATE_DATE = db.Column(db.DATE)
    STATE = db.Column(db.CHAR(1))
    PATCH_ID = db.Column(db.INTEGER,db.ForeignKey('version_patch.PATCH_ID'))
    UP_TRANS_ID = db.Column(db.INTEGER)    # 子单/关联单ID
    TYPE = db.Column(db.CHAR(1))           # 子单关联单类型
    SUBMITTER = db.Column(db.INTEGER)

class version_patch(db.Model):
    """
    补丁（版本）定义表
    """
    __tablename__ = 'VERSION_PATCH'
    __table_args__ = SCHEMA         # 查询带上库名前缀

    PATCH_ID = db.Column(db.INTEGER, primary_key=True)   
    VERSION_ID = db.Column(db.INTEGER)                   # 版本标识
    UP_PATCH_ID = db.Column(db.INTEGER)                  
    PATCH_CODE = db.Column(db.VARCHAR(255))              
    patchcode = db.relationship("trans_inst")

Answer 1

不知道你的数据库里面的version_patch是大写还是小写的，如果看文档的话你会发现db.ForeignKey中的"表名.字段名"，"表名"是数据库中的表名，而不是flask-sqlalchemy中的model名。按照你贴出的代码，如果你数据库中version_path不区分大小写，可以考虑换一下Model名，如：VersionPatch试试。

Answer 2

自己解决：

如下写法

class trans_inst(db.Model):

__tablename__ = 'TRANS_INST'
__table_args__ = SCHEMA         # 查询带上库名前缀
TRANS_ID = db.Column(db.INTEGER, primary_key=True)
TITLE = db.Column(db.VARCHAR(255), nullable=False)
VERSION_ID = db.Column(db.INTEGER)
PROJECT_ID = db.Column(db.INTEGER, nullable=False)
TEMPLET_ID = db.Column(db.INTEGER)
STAFF_ID = db.Column(db.INTEGER, nullable=False)
CREATE_DATE = db.Column(db.DATE)
STATE = db.Column(db.CHAR(1))
PATCH_ID = db.Column(db.INTEGER,db.ForeignKey(version_patch.PATCH_ID))
UP_TRANS_ID = db.Column(db.INTEGER)    # 子单/关联单ID
TYPE = db.Column(db.CHAR(1))           # 子单关联单类型
SUBMITTER = db.Column(db.INTEGER)