Flask提交HTML表单至SQL数据库出现空值行问题求助

使用HTML表单向Flask后端提交数据，点击提交按钮后，SQL数据库中会生成两行数据：一行是正确的表单信息，另一行全部为NULL值。按官方文档实现的逻辑，对此问题感到困惑，需要排查原因并获取解决方案。

相关代码 #

app.py 代码 #

@app.route ('/feedback', methods = ["GET","POST"])
def feedback():
        username = request.form.get("username")
        public = request.form.get("public")
        contact = request.form.get("contact")
        feedback_desc = request.form.get("feedback_desc")
        email = request.form.get("email")
        timestamp = datetime.now().strftime('%Y-%m-%d %H:%M:%S')

        conn = db_connect()
        cur = conn.cursor()
        cur.execute(
            "INSERT INTO users (id, username, contact, email, feedback_desc, created_at, is_public) VALUES (DEFAULT,%s,%s,%s,%s,%s,%s)", (username,contact,email,feedback_desc,timestamp,public)
            )
        conn.commit()

        return render_template("feedback.html")

HTML表单代码 #

<div class="content">
    <div class="feedback-content">
        <div>
            <h1>Let's Gather Some Info</h1>
            <br>
            <form action="{{ url_for('feedback')}}" method="post">
                <label for="name">Your Discord Name</label><br>
                    <input type="text" class="name" name="username">
                <br><br>

                
                <label for="is-public">Would you like to be your submission to be public or private?</label><br>
                    <input type="radio" name="public" id="yes_public" value="Public"> Yes </input><br>
                    <input type="radio" name="public" id="no_public" value="Not Public"> No </input><br>
                <br><br>

                    <label for="email">Write your email below</label>
                    <input type="text" class="email" name="email">
                <br><br>

                <label for="should-contact">Would you like to be contacted by our team regarding your submission?</label><br>
                    <input type="radio" name="contact" id="yes_contact" value="Contact"> Yes </input><br>
                    <input type="radio" name="contact" id="no_contact" value="Don't Contact"> No </input><br>
                <br><br>

                <label for="form-text">Please write your thoughts below:</label><br>
                    <input type="text" class="form-text" name="feedback_desc"><br><br>
                        <button type="submit">Submit Your Feedback</button>
            </form>
        </div>
    </div>  
</div>

原因分析 #

当前的Flask视图函数没有区分请求方法：

• 当用户第一次访问/feedback页面时，发送的是GET请求，此时request.form中所有字段的值都是None，执行INSERT语句就会插入一行全NULL的记录。
• 当用户填写表单并提交时，发送的是POST请求，此时request.form能获取到表单数据，执行INSERT插入正确的记录。
  两次请求触发两次插入，最终数据库中出现两行数据。

解决方案 #

在视图函数中添加请求方法判断，仅在POST请求时执行数据库插入操作，GET请求仅渲染页面：

修改后的app.py代码：

@app.route('/feedback', methods = ["GET","POST"])
def feedback():
    if request.method == 'POST':
        # 仅在POST请求时处理表单数据并插入数据库
        username = request.form.get("username")
        public = request.form.get("public")
        contact = request.form.get("contact")
        feedback_desc = request.form.get("feedback_desc")
        email = request.form.get("email")
        timestamp = datetime.now().strftime('%Y-%m-%d %H:%M:%S')

        conn = db_connect()
        cur = conn.cursor()
        cur.execute(
            "INSERT INTO users (id, username, contact, email, feedback_desc, created_at, is_public) VALUES (DEFAULT,%s,%s,%s,%s,%s,%s)", 
            (username,contact,email,feedback_desc,timestamp,public)
        )
        conn.commit()
    # GET请求或POST请求处理完成后，渲染页面
    return render_template("feedback.html")

额外优化建议 #

1. 遵循Post/Redirect/Get模式：在POST请求处理完成后，使用redirect重定向到页面，避免用户刷新页面时重复提交表单：

   from flask import redirect, url_for
   # ...
   if request.method == 'POST':
       # ... 数据库插入操作 ...
       return redirect(url_for('feedback'))
   

2. 添加表单验证：给HTML表单的必填字段添加required属性，确保用户提交时填写必要信息：

   <input type="text" class="name" name="username" required>
   

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