MySQL connection not available and werkzeug key error

Question

I have a simple Flask app with a web form. When I submit the web form, and then upload the data to mysql, sometimes it works, but sometimes it doesn't. This is happening both locally and when I deploy to google cloud on a public url (example.com).

The form, located at templates/auth/signup.html, is

    <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="[email protected]" name="email" id="email" required></input>
            <br /> <br/>
            <button type="submit">Submit</button>
    </form>

and the server logic within main.py is

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")

An example of what just happened.. I ran the app locally with python main.py, submitted the form, it uploaded to mysql, no errors from the browser. The terminal printed

127.0.0.1 - - [24/Jan/2024 09:27:54] "GET /auth/signup HTTP/1.1" 200 -
test1
first name there

However, I went to postman, with the server still running, ran a post request to http://localhost:8080/signupSubmit with keys firstname lastname phone email and it returned this error

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

while my terminal concurrently printed

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

Then, with server still running, I went back to the browser to http://localhost:8080/auth/signup and submitted the form with different info and received this error from the browser

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

with my terminal printing

127.0.0.1 - - [24/Jan/2024 09:29:29] "GET /auth/signup HTTP/1.1" 200 -
test2
first name there

I went to postman, again server still running, did post request and received same error as before

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

with terminal printing same KeyError: 'firstname'.

On the live version (example.com) I am able to submit the form one time with no errors, but every successive attempt the browser returns with

Internal Server Error
The server encountered an internal error and was unable to complete your request. Either the server is overloaded or there is an error in the application.

Dev tools says 500.

At one point in doing a different test, after submitting the form, my terminal printed (with full print statements):

raise exceptions.BadRequestKeyError(key)
werkzeug.exceptions.BadRequestKeyError: 400 Bad Request: The browser (or proxy) sent a request that this server could not understand.        
KeyError: 'firstname'
first name there
last name there
email there
phone there
INSERT INTO users (firstname, lastname, email, phone) VALUES (%s, %s, %s, %s)
('somefirst', 'somelast', '[email protected]', '3333')
<mysql.connector.connection_cext.CMySQLConnection object at ***********>
<class 'mysql.connector.connection_cext.CMySQLConnection'>
127.0.0.1 - - [24/Jan/2024 08:39:30] "POST /signupSubmit HTTP/1.1" 200 -

directly contradicting that I did not have a firstname.

I ran SHOW PROCESSLIST; for mysql and it returned

Id    | User           | Host     | db   | Command  | Time  |  State                | Info 
-------------------------------------------------------------------------------------------
5      event_scheduler  localhost  null   Daemon      6941326  Waiting on empty que  null
26362  rdsadmin         localhost  null   Sleep       7                              null

along with stuff from my internet company.

I'm confused about why mysql is not connected after one form submission, as I think that is what is ultimately causing the error.

Directory structure

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

Answer 1

I realized that I needed to put dataBase into saveSQLthenStripe. When I initially ran the app with python main.py it was creating the connection, but after that it was not.

So

def saveSQLthenStripe(firstName, lastName, email, phone):
    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
    )
    query = "INSERT INTO users (firstname, lastname, email, phone) VALUES (%s, %s, %s, %s)" 
    ...

I still have not figured out the postman error, but my app is nonetheless working locally and in production (example.com).