Flask应用添加用户时触发SQL IntegrityError问题求助

问题描述 #

我在用MySQL开发Flask应用时，创建用户功能一直无法正常运行。明明已经传入了user_id，却始终报错提示值缺失，这个问题已经困扰我5天以上。以下是我的代码及完整错误回溯：

代码片段 #

db = SQLAlchemy(app)

# 数据库模型
class friends(db.Model):
    __tablename__ = 'user'
    user_id = db.Column(db.String, primary_key=True)
    user_name = db.Column(db.String)
    age = db.Column(db.Integer)
    email = db.Column(db.String)
    gender = db.Column(db.String)

    def __init__(self, user_id, user_name, age, email,gender):
        self.id = user_id
        self.name = user_name
        self.age = age
        self.email = email
        self.gender = gender

# 路由
@app.route('/add', methods=['GET', 'POST'])
def add():
    if request.method == 'POST':
        add_usr_id = request.form['new_usr_id']
        add_usr_name = request.form['new_usr_name']
        add_usr_age = request.form['new_usr_age']
        add_usr_email = request.form['new_usr_email']
        add_usr_gender = request.form['new_usr_gender']
        commit_user = friends(user_id = add_usr_id, user_name = add_usr_name, age = add_usr_age, email = add_usr_email, gender = add_usr_gender)
        db.session.add(commit_user)
        db.session.commit()
        message = "The record was added."
        return render_template('add.html', message=message)
    else:
        return render_template('add.html')

@app.route('/show')
def read():
    show_friend = friends.query.all()
    return render_template('show.html', show_friend = show_friend)   

错误回溯 #

sqlalchemy.exc.IntegrityError: (pymysql.err.IntegrityError) (1048, "Column 'user_name' cannot be null")
[SQL: INSERT INTO user (user_name, age, email, gender) VALUES (%(user_name)s, %(age)s, %(email)s, %(gender)s)]
[parameters: {'user_name': None, 'age': '32', 'email': 'dfak@ekfl.com', 'gender': 'male'}]
(Background on this error at: https://sqlalche.me/e/14/gkpj)

问题原因与修复 #

核心问题出在friends模型的__init__方法里，属性映射错误：

• 模型类定义的数据库字段是user_id和user_name，但初始化方法里却把参数赋值给了self.id和self.name，导致SQLAlchemy无法将参数正确关联到数据库字段。
• 这直接导致user_name的值没有被正确传入，变成null，触发了数据库的非空约束错误；同时user_id也没有被正确存入数据库。

修复后的__init__方法如下：

def __init__(self, user_id, user_name, age, email, gender):
    self.user_id = user_id
    self.user_name = user_name
    self.age = age
    self.email = email
    self.gender = gender

另外需要确认：如果数据库中的user_name字段设置了非空约束，要确保前端表单的new_usr_name确实有值提交。从错误参数来看，前端已经传入了值，所以核心修复点就是修正模型初始化方法的属性映射。

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