烧瓶 SQAlchemy + SQLite NOT NULL 失败

Question

我有一个简单的注册表，它将插入到我的SQLite数据库中。但它失败了，因为它表明email列与NOT NULL约束相矛盾。即使来自email的输入正在提交和认可。

数据库模型(Flask SQLAlchemy(：

class Registration(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    email = db.Column(db.String(50))
    username = db.Column(db.String(20))
    password = db.Column(db.String(350))

注册码：

form = RegistrationForm()
    if request.method == "POST" and form.validate_on_submit():
        username = form.username.data
        email = form.email.data
        password = bcrypt.hashpw(form.password.data.encode('utf-8'), bcrypt.gensalt(14))
        # Query DB for existing username
        user = Registration.query.filter_by(username=username).first()
        # Query DB for existing email
        email = Registration.query.filter_by(email=email).first()
        # If username exits
        if user:
            flash("Sorry that username is already taken, please choose another!")
            return render_template('register.html', form=form)
        # If email exists
        elif email:
            flash('That email is already associated with another account, please use another!')
            return render_template('register.html', form=form)
        else:
            new_user = Registration(email=email, username=username, password=password)
            db.session.add(new_user)
            db.session.commit()
            flash("Thanks for registering, {u}!".format(u=username))
            return redirect(url_for('home'))

错误：

(sqlite3.IntegrityError) NOT NULL constraint failed: registration.email [SQL: 'INSERT INTO registration (email, username, password) VALUES (?, ?, ?)'] [parameters: (None, 'test_user', b'$2b$14$fiL14w35s.kckAY5timb9uE0d9pQ/K9RZpRuNPBlnsKl3FHaQfFoG')]

但它显示找到了电子邮件地址：

2017-03-20 13:35:19,082 INFO sqlalchemy.engine.base.Engine ('[e-mail]', 1, 0)

但随后显示后的 2 行：

2017-03-20 13:35:19,084 INFO sqlalchemy.engine.base.Engine INSERT INTO registration (email, username, password) VALUES (?, ?, ?)
2017-03-20 13:35:19,084 INFO sqlalchemy.engine.base.Engine (None, 'test_user', b'$2b$14$fiL14w35s.kckAY5timb9uE0d9pQ/K9RZpRuNPBlnsKl3FHaQfFoG')

为什么它会尝试插入None email即使它显示了输入的实际电子邮件？

Answer 1

因为您运行此查询：

email = Registration.query.filter_by(email=email).first()

您正在用该查询的结果覆盖email变量。如果未找到匹配的条目，则将email替换为 None ，这会导致您的问题。

同样有趣的是，我实际上不明白为什么您的email列具有NOT NULL约束 - 您对该列的定义是：

email = db.Column(db.String(50))

它不为列指定任何约束。如果要确保此列永远不会为 NULL，则应将其更改为：

email = db.Column(db.String(50), nullable=False)