Flask SQLAlchemy数据库迁移报NoReferencedTableError解决方法

参照教材学习Flask与SQLAlchemy框架过程中，执行数据库迁移命令flask db migrate时持续抛出以下错误：

sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'recipe.user_id' could not find table 'user' with which to generate a foreign key to target column 'id'

查阅同类问题的常见解决方案后，已按要求配置模型的__tablename__属性，但问题仍然存在。

recipe.py 文件代码 #

from extensions import db

recipe_list = []


def get_last_id():
    if recipe_list:
        last_recipe = recipe_list[-1]
    else:
        return 1
    return last_recipe.id + 1


class Recipe(db.Model):
    __tablename__ = 'recipe'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    description = db.Column(db.String(200))
    num_of_servings = db.Column(db.Integer)
    cook_time = db.Column(db.Integer)
    directions = db.Column(db.String(1000))
    is_publish = db.Column(db.Boolean(), default=False)
    created_at = db.Column(db.DateTime(), nullable=False, server_default=db.func.now())
    updated_at = db.Column(db.DateTime(), nullable=False, server_default=db.func.now(), onupdate=db.func.now())

    user_id = db.Column(db.Integer(), db.ForeignKey("user.id"))

users.py 文件代码 #

from extensions import db


class User(db.Model):
    __tablename__ = 'user'

    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), nullable=False, unique=True)
    email = db.Column(db.String(200), nullable=False, unique=True)
    password = db.Column(db.String(200))
    is_active = db.Column(db.Boolean(), default=False)
    created_at = db.Column(db.DateTime(), nullable=False, server_default=db.func.now())
    updated_at = db.Column(db.DateTime(), nullable=False, server_default=db.func.now(), onupdate=db.func.now())

    recipes = db.relationship('Recipe', backref='user')

    @classmethod
    def get_by_username(cls, username):
        return cls.query.filter_by(username=username).first()

    @classmethod
    def get_by_email(cls, email):
        return cls.query.filter_by(email=email).first()

    def save(self):
        db.session.add(self)
        db.session.commit()

故障原因 #

两个模型类拆分存储在不同文件中，SQLAlchemy未成功加载关联的User模型，生成外键时无法找到对应的user表。

修复方案 #

在Flask应用入口文件app.py中同时导入两个模型类即可修复问题，修正后的app.py代码如下：

from flask import Flask
from flask_migrate import Migrate
from flask_restful import Api

from Config import Config
from extensions import db
from models.recipe import Recipe
from models.user import User
from resources.recipe import RecipeListResource, RecipeResource, RecipePublic

def register_extensions(app):
    db.init_app(app)
    migrate = Migrate(app, db)

def register_resources(app):
    api = Api(app)
    api.add_resource(RecipeListResource, '/recipes')
    api.add_resource(RecipeResource, '/recipes/<int:id>')
    api.add_resource(RecipePublic, '/recipes/<int:id>/publish')

def create_app():
    app = Flask(__name__)
    app.config.from_object(Config)

    register_extensions(app)
    register_resources(app)

    return app


if __name__ == '__main__':
    app = create_app()
    app.run(port=5000, debug=True)

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