Flask表单提交偶现MySQL连接失效与Werkzeug KeyError问题求助

问题概述 #

开发了一个包含表单的简单Flask应用，提交表单后将数据写入MySQL时，有时成功有时失败，该问题在本地运行及部署到Google Cloud的公网域名example.com时均会出现。

表单代码（templates/auth/signup.html） #

<form method="POST" action="/signupSubmit">
        <label for="firstname">First name: </label>
        <input type="text" placeholder="John" name="firstname" id="firstname" required></input>
        <br /> <br />
        <label for="lastname">Last name: </label>
        <input type="text" placeholder="Smith" name="lastname" id="lastname" required></input>
        <br /> <br />
        <label for="phone">Phone number: </label>
        <input type="tel" placeholder="412-555-1234" name="phone" id="phone" required></input>
        <br /> <br />
        <label type="text" name="email" id="email">Email: </label>
        <input type="email" placeholder="me@outlook.com" name="email" id="email" required></input>
        <br /> <br/>
        <button type="submit">Submit</button>
</form>

Flask服务器逻辑（main.py） #

from flask import Flask, render_template, request
from jinja2 import Environment, FileSystemLoader
from dotenv import load_dotenv
import os, mysql.connector, stripe

load_dotenv()

...

@app.route("/auth/signup", methods=['GET']) 
def signup():
    return render_template('auth/signup.html')

dataBase = mysql.connector.connect(
    host=os.environ['DB_HOST'],
    password=os.environ['DB_PASSWORD'],
    user=os.environ['DB_USER'],
    port=os.environ['DB_PORT'],
    database=os.environ['DB_DATABASE'],
    connection_timeout=3600
)

def saveSQLthenStripe(firstName, lastName, email, phone):
    query = "INSERT INTO users (firstname, lastname, email, phone) VALUES (%s, %s, %s, %s)" 
    vals = (firstName, lastName, email, phone) 
    cursorObject = dataBase.cursor()
    cursorObject.execute(query, vals) 
    dataBase.commit() 
    dataBase.close()
    thenStripe(firstName, lastName, email, phone)

@app.route("/signupSubmit", methods=['POST']) 
def collectForm():

    print(request.form['firstname'])
    if "firstname" in request.form:
        print("first name there")
    else:
        print("first name not there")
    fname = request.form['firstname'] 
    lname = request.form['lastname']
    email = request.form['email']
    phone = request.form['phone']
    saveSQLthenStripe(fname, lname, email, phone) 
    return render_template('auth/success.html', title="Submit successful")

测试场景及错误表现 #

• 本地运行python main.py，首次提交表单可正常写入MySQL，终端日志正常；
• 服务器运行状态下，使用Postman向http://localhost:8080/signupSubmit发送POST请求（携带firstname、lastname、phone、email参数），返回错误：

  werkzeug.exceptions.BadRequestKeyError: 400 Bad Request: The browser (or proxy) sent a request that this
          server could not understand.
          KeyError: 'firstname'
  

  终端同时打印对应的KeyError；
• 再次通过浏览器提交表单，出现错误：

  OperationalError
  mysql.connector.errors.OperationalError: MySQL Connection not available.
  

  终端日志显示已检测到firstname字段；
• 线上版本example.com中，首次提交正常，后续所有提交均返回500内部服务器错误；
• 某次测试中，终端日志显示已检测到所有表单字段，却仍抛出KeyError，同时数据成功写入MySQL；
• 执行SHOW PROCESSLIST;查看MySQL进程列表未发现异常。

核心困惑 #

首次表单提交后MySQL连接就不可用，这似乎是问题的根源，请求排查并解决该问题。

项目目录结构 #

myproject
|
|--env
|   |
|   +--Include
|   +--Lib         
|   +--Scripts
|   +--pyvenv.cfg
|
|--static
|--templates
|--.env 
|--.flaskenv 
|--.gitignore
|--app.yaml
|--main.py
|--requirements.txt

解决方案 #

1. 修复MySQL连接复用问题 #

当前代码在全局初始化一次MySQL连接，首次调用saveSQLthenStripe时执行了dataBase.close()，后续请求再使用该连接就会报错MySQL Connection not available。正确的做法是每次请求时创建新连接，推荐使用上下文管理器确保连接正确关闭：

修改saveSQLthenStripe函数：

def saveSQLthenStripe(firstName, lastName, email, phone):
    query = "INSERT INTO users (firstname, lastname, email, phone) VALUES (%s, %s, %s, %s)" 
    vals = (firstName, lastName, email, phone) 
    # 每次函数调用时创建新连接
    with mysql.connector.connect(
        host=os.environ['DB_HOST'],
        password=os.environ['DB_PASSWORD'],
        user=os.environ['DB_USER'],
        port=os.environ['DB_PORT'],
        database=os.environ['DB_DATABASE'],
        connection_timeout=3600
    ) as dataBase:
        with dataBase.cursor() as cursorObject:
            cursorObject.execute(query, vals) 
            dataBase.commit() 
    thenStripe(firstName, lastName, email, phone)

同时删除全局的dataBase = mysql.connector.connect(...)语句。

2. 修复表单参数取值的KeyError问题 #

当前代码先执行print(request.form['firstname'])，这会直接触发KeyError，后续的存在性判断根本不会执行。应该先判断参数是否存在，或者使用request.form.get()方法安全取值：

修改collectForm函数：

@app.route("/signupSubmit", methods=['POST']) 
def collectForm():
    # 安全获取表单参数，不存在时返回None
    fname = request.form.get('firstname')
    lname = request.form.get('lastname')
    email = request.form.get('email')
    phone = request.form.get('phone')

    # 校验参数完整性
    if not all([fname, lname, email, phone]):
        return render_template('auth/error.html', title="Missing required fields"), 400

    saveSQLthenStripe(fname, lname, email, phone) 
    return render_template('auth/success.html', title="Submit successful")

另外，使用Postman测试时，确保请求的Content-Type设置为application/x-www-form-urlencoded，否则request.form无法正确解析参数。

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