Flask web application - struggling to import user submitted information to MySQL database

Question

I'm using Flask to build a basic web application and am attempting to import information into a locally hosted MySQL database. Currently I have a table which has a single column (email) and I'm trying to capture information submitted on the register page on the application.

I feel like I'm almost there, but am unable to get it to work due to the following error:

[2020-10-04 16:58:16,448] ERROR in app: Exception on /register [POST]
Traceback (most recent call last):
  File "c:\users\tjmce\appdata\local\programs\python\python38-32\lib\site-packages\MySQLdb\cursors.py", line 204, in execute
    query = query % args
TypeError: not all arguments converted during bytes formatting

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "c:\users\tjmce\appdata\local\programs\python\python38-32\lib\site-packages\flask\app.py", line 2447, in wsgi_app
    response = self.full_dispatch_request()
  File "c:\users\tjmce\appdata\local\programs\python\python38-32\lib\site-packages\flask\app.py", line 1952, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "c:\users\tjmce\appdata\local\programs\python\python38-32\lib\site-packages\flask\app.py", line 1821, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "c:\users\tjmce\appdata\local\programs\python\python38-32\lib\site-packages\flask\_compat.py", line 39, in reraise
    raise value
  File "c:\users\tjmce\appdata\local\programs\python\python38-32\lib\site-packages\flask\app.py", line 1950, in full_dispatch_request
    rv = self.dispatch_request()
  File "c:\users\tjmce\appdata\local\programs\python\python38-32\lib\site-packages\flask\app.py", line 1936, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "C:\Users\tjmce\Desktop\Final Project\app.py", line 137, in register
    cur.execute("INSERT INTO users (email) VALUES (%s)", email)
  File "c:\users\tjmce\appdata\local\programs\python\python38-32\lib\site-packages\MySQLdb\cursors.py", line 206, in execute
    raise ProgrammingError(str(m))
MySQLdb._exceptions.ProgrammingError: not all arguments converted during bytes formatting

Of course, I have tried searching around online and while I have found a few very similar examples, none of the solutions provided seemed to work for me.

Here is the full code from my application (excluding HTML page templates and a few locally imported files). I would be super grateful if somebody could help me understand what's going wrong here.

import os
from datetime import datetime, timedelta
from flask import Flask, flash, jsonify, redirect, render_template, request, session
from flask_session import Session
from werkzeug.exceptions import default_exceptions, HTTPException, InternalServerError
from werkzeug.security import check_password_hash, generate_password_hash
from tempfile import mkdtemp
from support import apology, login_required
from flask_mysqldb import MySQL

# Configure application
app = Flask(__name__)

# Ensure templates are auto-reloaded
app.config["TEMPLATES_AUTO_RELOAD"] = True

# Ensure responses aren't cached
@app.after_request
def after_request(response):
    response.headers["Cache-Control"] = "no-cache, no-store, must-revalidate"
    response.headers["Expires"] = 0
    response.headers["Pragma"] = "no-cache"
    return response

# Custom filter
#app.jinja_env.filters["usd"] = usd

# Configure session to use filesystem (instead of signed cookies)
app.config["SESSION_FILE_DIR"] = mkdtemp()
app.config["SESSION_PERMANENT"] = False
app.config["SESSION_TYPE"] = "filesystem"
Session(app)

# MySQL Configuration
app.config['MYSQL_HOST'] = "localhost"
app.config['MYSQL_USER'] = "root"
app.config['MYSQL_PASSWORD'] = ""
app.config['MYSQL_DB'] = "test"

mysql = MySQL(app)

@app.route("/register", methods=["GET", "POST"])
def register():
    """Register user"""

    # User reached route via POST (as by submitting a form via POST)
    if request.method == "POST":

        # Ensure username was submitted
        if not request.form.get("email"):
            return apology("must provide email", 403)

        # Ensure password was submitted
        elif not request.form.get("password"):
            return apology("must provide password", 403)

        # Ensure confirmation password was submitted
        elif not request.form.get("confirm"):
            return apology("must confirm password", 403)

        # Ensure password confirmation matches
        elif (request.form.get("confirm") != request.form.get("password")):
            return apology("confirmation password must match", 403)

        # Store email in a variable
        email = request.form.get("email")

        # Store password in a variable
        password = request.form.get("password")

        # Store first name in a variable
        firstname = request.form.get("firstname")

        # Store last name in a variable
        lastname = request.form.get("lastname")

        # Hash the password
        hashedpassword = generate_password_hash(password=password, method='pbkdf2:sha256', salt_length=8)

        # Create a cursor
        cur = mysql.connection.cursor()

        # Insert email into database
        cur.execute("INSERT INTO users (email) VALUES (%s)", email)
        mysql.connection.commit()
        cur.close()

        # Pass the username and password via post to the register template
        return render_template("registered.html", email=email, password=password)

    # User reached route via GET (as by clicking a link or via redirect)
    else:
        return render_template("register.html")

Thank you.

Answer 1

python and the connector don't accept single arguments. So use

    # Insert email into database
    cur.execute("INSERT INTO users (email) VALUES (%s)", (email,))
    mysql.connection.commit()
    cur.close()