Finance: UNIQUE constraint failed: users.username

Question

I've been struggling with this for days. Ignore the '1234' hardcoded into the "insert into" line, just using that to isolate problems. The problem I'm facing is with the username being inserted it seems.

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

    if request.method == "GET":
        return render_template("register.html")
    else:
        # Take input for username
        username = request.form.get("username")

        #If username is blank return apology
        if not username:
            return apology("You must enter a username.")

        # Checks to see if username exists already, if so return apology
 
        usernamecheck = db.execute("SELECT * FROM users WHERE username = '" + username + "'")
        if len(usernamecheck) == 1:
            return apology("Username already exists.")



        # Take input for password
        password = request.form.get("password")

        # if password is blank, return apology
        if not password:
            return apology("You must enter a password.")

        # Take input for confirmation
        confirmation = request.form.get("confirmation")

        # if confirmation is blank, return apology
        if not confirmation:
            return apology("You must confirm your password.")

        # if password and confirmation don't match, return apology
        if password != confirmation:
            return apology("Your password confirmation does not match the password.")

        # hash the password with generate_password_hash
        pwhash = generate_password_hash(request.form.get("password"))

        # insert the username and hash into the users table
        sql = "INSERT INTO users (username, hash) VALUES (':username', '1234')"
     
        print(sql)
        db.execute(sql)
        return redirect("/")

And here's the terminal stuff:

INSERT INTO users (username, hash) VALUES (':username', '1234')
DEBUG:cs50:INSERT INTO users (username, hash) VALUES ('\:username', '1234')
ERROR:application:Exception on /register [POST]
Traceback (most recent call last):
  File "/usr/local/lib/python3.7/site-packages/flask/app.py", line 2446, in wsgi_app
    response = self.full_dispatch_request()
  File "/usr/local/lib/python3.7/site-packages/flask/app.py", line 1951, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/usr/local/lib/python3.7/site-packages/flask/app.py", line 1820, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/usr/local/lib/python3.7/site-packages/flask/_compat.py", line 39, in reraise
    raise value
  File "/usr/local/lib/python3.7/site-packages/flask/app.py", line 1949, in full_dispatch_request
    rv = self.dispatch_request()
  File "/usr/local/lib/python3.7/site-packages/flask/app.py", line 1935, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/home/ubuntu/finance/application.py", line 191, in register
    db.execute(sql)
  File "/usr/local/lib/python3.7/site-packages/cs50/sql.py", line 21, in decorator
    return f(*args, **kwargs)
  File "/usr/local/lib/python3.7/site-packages/cs50/sql.py", line 365, in execute
    raise e
RuntimeError: UNIQUE constraint failed: users.username

Answer 1

Have you looked at the data in the users table with sqlite3 or phpliteadmin? Have you noticed a user record with username value ":username"? The insert takes this ':username' as a string literal because it is enclosed in '.

Answer 2

Double check if the table has any users in it , the unique username constraint will fail if you are trying to register a user with the same username when testing your register form. I had the same problem, deleting the table worked for me.