Flask+SQLAlchemy新增表行报错：users.result_id列不存在

错误详情 #

sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (1054, "Unknown column 'users.result_id' in 'field list'")
[SQL: SELECT users.id AS users_id, users.last_name AS users_last_name, users.first_name AS users_first_name, users.phone_number AS users_phone_number, users.email AS users_email, users.role AS users_role, users.result_id AS users_result_id FROM users LIMIT %(param_1)s]
[parameters: {'param_1': 1}

相关代码 #

from flask import Flask, render_template, request, redirect, url_for
from flask_sqlalchemy import SQLAlchemy
import datetime
from flask_migrate import Migrate

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = "mariadb+pymysql://admin:0152@127.0.0.1/flask_db?charset=utf8mb4"
db = SQLAlchemy(app)

migrate = Migrate(app, db)

class Users(db.Model):
    # 创建用户表
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    last_name = db.Column(db.String(128))
    first_name = db.Column(db.String(128))
    phone_number = db.Column(db.String(128))
    email = db.Column(db.String(128), unique=True)
    role = db.Column(db.String(128), default = "user")
    result_id = db.Column(db.String(128))

    def __init__(self, first_name, last_name, phone_number, email, result_id):
        self.last_name = last_name
        self.first_name = first_name
        self.phone_number = phone_number
        self.email = email
        self.result_id = result_id

class Objects(db.Model):
    __tablename__ = 'objects'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(128))
    img = db.Column(db.String(128), default = 'blank.img')
    description = db.Column(db.String(999))
    price = db.Column(db.String(128))
    
    def __init__(self, name, img, description, price):
        self.name = name 
        self.img = img
        self.description = description
        self.price = price

class Results(db.Model):
    __tablename__ = 'results'
    id = db.Column(db.Integer, primary_key=True)
    f1 = db.Column(db.String(128))
    f2 = db.Column(db.String(128))
    f3 = db.Column(db.String(128))
    f4 = db.Column(db.String(128))
    f5 = db.Column(db.String(128))
    f6 = db.Column(db.String(128))
    f7 = db.Column(db.String(128))
    f8 = db.Column(db.String(128))
    f9 = db.Column(db.String(128))
    f10 = db.Column(db.String(128))
    f11 = db.Column(db.String(128))
    f12 = db.Column(db.String(128))
    f13 = db.Column(db.String(128))
    f14 = db.Column(db.String(128))
    f15 = db.Column(db.String(128))

    date = db.Column(db.DateTime, default = datetime.datetime.now())

    def __init__(self, results):
        self.f1 = results[1]
        self.f2 = results[2]
        self.f3 = results[3]
        self.f4 = results[4]
        self.f5 = results[5]
        self.f6 = results[6]
        self.f7 = results[7]
        self.f8 = results[8]
        self.f9 = results[9]
        self.f10 =  results[10]
        self.f11 =  results[11]
        self.f12 =  results[12]
        self.f13 =  results[13]
        self.f14 =  results[14]
        self.f15 =  results[15]

@app.route('/')
def index():
    objects = Objects.query.all()
    len_obj = len(objects)
    return render_template('./index.html', objects = objects, len_obj = len_obj)

@app.route('/create_object/', methods=['POST', 'GET'])
def create_object():
    if request.method == "POST":
        name = request.form['name']
        desc = request.form['desc']
        price = request.form['price']
        img = request.form['img']

        object = Objects(name = name, description= desc, price=price, img=img)

        try:
            db.session.add(object)
            db.session.commit()
            return redirect('/')
        except:
            db.session.rollback()
            return redirect('/')
        finally:
            db.session.close()
    else:
        return render_template('./create_object.html')

@app.route('/vote/', methods=['POST', 'GET'])
def vote():
    if request.method == "POST":
        name = request.form['name']
        lastname = request.form['lastname']
        email = request.form['email']
        tel = request.form['phonenumber']

        places = {}
        for i in range(1, 16):
            try:
                if request.form[str(i)] != '':
                    places.update({i: request.form[i]})
            except:
                places.update({i: ' '})

        result = Results(places)
        user = Users(first_name=name, last_name=lastname, phone_number=tel, email=email, result_id='1')

        try:
            db.session.add(user, result)
            db.session.commit()
            return redirect('/vote/')
        except:
            db.session.rollback()
            return redirect('/vote/')
        finally:
            db.session.close()
    
    user = Users.query.first()
    return user.id

解决方案 #

• 模型里已经添加了result_id字段，但数据库表未同步变更。用Flask-Migrate执行以下命令即可：

  1. 生成迁移脚本：flask db migrate -m "Add result_id column to users table"
  2. 应用迁移到数据库：flask db upgrade

• db.session.add()一次只能传入一个对象，当前代码里的db.session.add(user, result)会报错，改成：

  db.session.add(result)
  db.session.add(user)
  

  或者使用批量添加：db.session.add_all([user, result])

• Results模型的date字段默认值不要直接调用函数，否则所有记录的时间都会是服务器启动时间，修改为：

  date = db.Column(db.DateTime, default=datetime.datetime.now)
  

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