Flask WTForms MySQL query question

by: queuefive, 7 years ago


In a couple of the Flask web dev tutorials, you used the following code for querying the MySQL database for to find a specific username:
x = c.execute("SELECT * FROM users WHERE username = (%s)", (thwart(username)))

However, when filling out the registration form, an error is produced that says "not all arguments converted during string formatting." A similar error is encountered when creating the Login form. From reading the YouTube comments, it seems that this is due to an update to WTForms making it so the TextField alias for StringField is depricated. People posted a variety of solutions to this problem including:
x = c.execute("SELECT * FROM users WHERE username = %s", (username,))

x = c.execute("SELECT * FROM users WHERE username='%s'"%(thart(username)))

x = c.execute("SELECT * FROM users WHERE username = (%s)", [thwart(request.form['username'])])


May I ask what you think is the best solution and why?


You must be logged in to post. Please login or register an account.



youre probably using MySQL-db package. which doesnt work with python3, you dont want it.  you want to use PyMySQL -  pip install pymysql.  recommend using virtual environment command with python 3.5 =  'pyvenv venv'  then pip install your packages there after activating with 'source venv/bin/activate.  heres what your functions should look like for login and register system (same as in tutorial, but with pymysql and argon2 for pass hashing (need latest passlib1.7) - need to pip install argon2_cffi).

#dbconnect.py   

import pymysql

def connection():
    conn = pymysql.connect(host = "localhost",
                           #unix_socket = "/var/run/mysqld/mysqld.sock",
                           user = "root",
                           passwd = "yoursqlpassword",
                           db = "dbname")
    c = conn.cursor()
    return c, conn


#init.py imports and functions


from flask import Flask, render_template, flash, request, url_for, redirect, 
     session, send_file, send_from_directory, jsonify, abort
from .dbconnect import connection
from flask_wtf import FlaskForm
from wtforms import BooleanField, TextField, TextAreaField, PasswordField, 
     SubmitField, validators
from passlib.hash import argon2
from pymysql import escape_string as thwart
from functools import wraps


class RegisterForm(FlaskForm):
    username = TextField('Username', [validators.Length(min=5, max=20)])
    email = TextField('Email Address', [validators.Length(min=8, max=50), validators.Email()])
    password = PasswordField('Password', [validators.Length(min=6, max=20, message='Password must be at least 6 characters'),
        validators.Required(), validators.EqualTo('confirm', message='Passwords must match')])
    confirm = PasswordField('Confirm Password')
    accept_tos = BooleanField('By registering, I accept the <br> <a href="/termsofservice/"> Terms </a> and <a href="/privacy/"> 
                                                Privacy </a> (12-1-16)', [validators.Required()])
    #recaptcha = RecaptchaField()


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

    form = RegisterForm()

    if request.method == "POST" and form.validate():
        username = form.username.data
        email = form.email.data
        password = argon2.using(rounds=8).hash((str(form.password.data)))
        c, conn = connection()

        x = c.execute("SELECT * FROM users WHERE username = (%s)",
                      (thwart(username)))

        if int(x) > 0:
            flash("That username is already taken, please choose another.")
            return render_template("register.html", form=form)

        else:
            c.execute("INSERT INTO users (username, password, email, tracking) VALUES (%s, %s, %s, %s)",
                      (thwart(username), thwart(password), thwart(email), thwart("/dashboard/")))

            conn.commit()
            flash("Thanks for registering!")
            c.close()
            conn.close()

            session['logged_in'] = True
            session['username'] = username

            return redirect(url_for("dashboard"))

    return render_template("register.html", form=form)



@app.route("/logout/")
@login_required
def logout():
    session.clear()
    flash("You have been logged out.")
    return redirect(url_for('homepage'))

@app.route("/login/", methods=["GET","POST"])
def login_page():
    error = ''
    c, conn = connection()
    if request.method == "POST":

        data = c.execute("SELECT * FROM users WHERE username = (%s)",
                         thwart(request.form['username']))
        data = c.fetchone()[2]

        if argon2.verify(request.form['password'], data):
            session['logged_in'] = True
            session['username'] = request.form['username']

            flash("You are now logged in.")
            return redirect(url_for("dashboard"))

        else:
            error = "Invalid credentials, try again."

    return render_template("login.html", error = error)


-kingfitz 7 years ago
Last edited 7 years ago

You must be logged in to post. Please login or register an account.


Thanks for the detailed response!

-queuefive 7 years ago

You must be logged in to post. Please login or register an account.