Flask+SQLAlchemy实现多对多ORM模型时触发NoReferencedTableError

问题背景 #

参与本地社区小型项目时学习Python，首次使用Flask+SQLAlchemy（搭配MySQL数据库）。原本ORM运行正常，按照官方指南将两张表改为多对多模型后，Flask应用初始化时抛出NoReferencedTableError错误。

完整错误信息 #

sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'assoc_tbl_1_user_X_challenge.challengeId' could not find table 'challengesTbl' with which to generate a foreign key to target column 'id'
...
sqlalchemy.exc.InvalidRequestError: One or more mappers failed to initialize - can't proceed with initialization of other mappers. Triggering mapper: 'Mapper[User(usersTbl)]'. Original exception was: Foreign key associated with column 'assoc_tbl_1_user_X_challenge.challengeId' could not find table 'challengesTbl' with which to generate a foreign key to target column 'id'

排查过程 #

• 反复核对表名、类名拼写，确认无错误；
• 怀疑关联表association_table1在表类定义前实例化导致无法找到对应表，尝试将其移至文件末尾或先声明后初始化，均无效；
• 运行数据库初始化脚本后，仅生成challengesTbl、rideRecordingsTbl、usersTbl三张表，未创建关联表assoc_tbl_1_user_X_challenge。

原数据模型代码 #

from . import db
from flask_login import UserMixin
from sqlalchemy import text

class Base(db.DeclarativeBase):
    pass

association_table1 = db.Table(
    "assoc_tbl_1_user_X_challenge",
    Base.metadata,
    db.Column("userId", db.ForeignKey("usersTbl.id"), primary_key=True),
    db.Column("challengeId", db.ForeignKey("challengesTbl.id"), primary_key=True),
)

class User(UserMixin, db.Model):
    __tablename__ = "usersTbl"
    id = db.Column(db.Integer, primary_key=True) # primary keys are required by SQLAlchemy
    email = db.Column(db.String(100), unique=True)
    password = db.Column(db.String(1000))
    givenName = db.Column(db.String(100))
    sn = db.Column(db.String(100))
    totalDistanceKm = db.Column(db.Float, server_default=text("0.0"))
    groups = db.Column(db.JSON, nullable=False)
    isActive = db.Column(db.Boolean, default=True, nullable=False, index=True)
    challenges = db.relationship('Challenge', secondary=association_table1, back_populates="participants")
    rides = db.relationship('Ride', backref='usersTbl', lazy=True)

    def __repr__(self):
        return f'<User "{self.email}">'

class Ride(db.Model):
    __tablename__ = "rideRecordingsTbl"
    id = db.Column(db.Integer, primary_key=True)
    rideRecording = db.Column(db.LargeBinary)
    #rideRecording = db.Column(db.LONGBLOB, nullable=False)
    rideName = db.Column(db.String(150))
    distanceKm = db.Column(db.Float, server_default=text("0.0"))
    duration = db.Column(db.Time)
    uploadDateTime = db.Column(db.DateTime)
    stream_hash = db.Column(db.String(1000))
    ownerId = db.Column(db.Integer, db.ForeignKey('usersTbl.id'), nullable=False)

    def __repr__(self):
        return f'<Ride "{self.rideName[:20]}...">'

class Challenge(db.Model):
    __tablename__ = "challengesTbl"
    id = db.Column(db.Integer, primary_key=True)
    rideRecording = db.Column(db.LargeBinary)
    challengeName = db.Column(db.String(150))
    challengeTargetDistanceKm = db.Column(db.Float, server_default=text("0.0"))
    challengeCurrnetTotalDistanceKm = db.Column(db.Float, server_default=text("0.0"))
    challengeEnds = db.Column(db.DateTime)
    challengeStarts = db.Column(db.DateTime)
    createdByUserId = db.Column(db.Integer)
    isActive = db.Column(db.Boolean, default=True, nullable=False, index=True)
    participants = db.relationship('User', secondary=association_table1, back_populates="challenges")

    def __repr__(self):
        return f'<Challenge "{self.challengeName[:20]}...">'

解决方法 #

结合“引用未声明对象”的提示方向，调整关联表的外键引用方式，并将关系定义移至表类之后，最终修改后的代码如下：

association_table1 = db.Table(
    "assoc_tbl_1_user_X_challenge",
    Base.metadata,
    #db.Column("userId", db.ForeignKey("usersTbl.id"), primary_key=True),
    #db.Column("challengeId", db.ForeignKey("challengesTbl.id"), primary_key=True)
    db.Column("userId", db.ForeignKey(User.id), primary_key=True),
    db.Column("challengeId", db.ForeignKey(Challenge.id), primary_key=True)
)

User.challenges = db.relationship('Challenge', secondary=association_table1, back_populates="participants")
Challenge.participants = db.relationship('User', secondary=association_table1, back_populates="challenges")

内容的提问来源于stack exchange，提问作者tis