Python3 Flask & Sqlite3 - Can't insert into database

Question

I can't make my code work. I've been looking around for tutorials and guides but to no avail. If anyone see the mistake I'm doing, it would be very helpful.

Here is the folder structure:

/
 application.py
 database.db

/routes
     __init__.py
     signup.py
    
/templates
     signup.html

Here is my signup.py:

import sqlite3
from flask import Blueprint, render_template, redirect, session, request, flash
from werkzeug.exceptions import default_exceptions, HTTPException, InternalServerError
from werkzeug.security import check_password_hash, generate_password_hash

# Set Blueprints
signup = Blueprint('signup', __name__,)

@signup.route("/signup", methods=["GET", "POST"])
def signupFunction():

    # Forget any user_id
    session.clear()

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

        try:
            email = request.form.get("email")
            username = request.form.get("username")
            password = request.form.get("password")
            confirmPassword  = request.form.get("confirm-password")

            with sqlite3.connect("./database") as connection:
                print("Opened database successfully")
                current = connection.cursor()

                # Ensure email was submitted
                if not email:
                    return flash("must provide email")
                # Ensure username was submitted
                if not username:
                    return flash("must provide username")

                # Ensure password was submitted
                if not password:
                    return flash("must provide password")

                # Ensure confirm password is correct
                if password != confirmPassword:
                    return flash("The passwords don't match")

                # Query database for username if already exists
                current.execute("SELECT * FROM users WHERE username = :username", username=username)
                if current.fetchall() == username: 
                    return flash("Username already taken")

                # Insert user and hash of the password into the table
                current.execute("INSERT INTO users(username, hash) VALUES (:username, :hash)", username=username, hash=generate_password_hash(password))
                current.commit()

                # Query database for username
                current.execute("SELECT * FROM users WHERE username = :username", username=username)
                rows = current.fetchall()

                # Remember which user has logged in
                session["user_id"] = rows[0]["id"]

                # Commit to databse
                connection.commit()

                print("Database operation succesful")
            
        except:
            connection.rollback()
            print("Error in sign up operation")
        
        finally:
            # Close database connection
            connection.close()
            # Redirect user to home page
            return redirect("/")

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

here is my signup.html

{% extends "layout.html" %}

{% block title %}
    Sign Up
{% endblock %}

{% block main %}
    <p>Register or <a href='/signin'>login</a></p><br>
    <form action="/signup" method="post">
        <div>
            <p>Username (6 characters min).</p>
            <input id="username" autocomplete="off" autofocus class="form-control" name="username" placeholder="Username" type="text">
        </div>
        <br>
        <div>
            <p>Email.</p>
            <input id="email" autocomplete="off" autofocus class="form-control" name="email" placeholder="Email" type="text">
        </div>
        <br>
        <div>
            <p>Password (7 to 15 characters, 1 numeric, 1 special).</p>
            <input id="password" name="password" placeholder="Password" type="password">
        </div>
        <div>
            <input id="confirm-pass" name="confirm-password" placeholder="Confirm Password" type="password">
        </div>
        <br>
        <button id="submit" type="submit">Sign Up</button>
        <br>
    </form>

    <script>
        // Password must be between 7 to 15 characters which contain at least one numeric digit and a special character
        document.querySelector("#submit").disabled = true;

        document.querySelector("#confirm-pass").onkeyup = function() {
            let password = document.querySelector("#password").value;
            let username = document.querySelector("#username").value;
            let email = document.querySelector("#email").value;
            let confirmation = document.querySelector("#confirm-pass").value;

            if (password.match(/^^(?=.*[0-9])(?=.*[!@#$%^&*])[a-zA-Z0-9!@#$%^&*]{7,15}$/i)) {
                document.querySelector("#submit").disabled = true;
                if (email.match(/^(([^<>()\[\]\.,;:\s@\"]+(\.[^<>()\[\]\.,;:\s@\"]+)*)|(\".+\"))@(([^<>()[\]\.,;:\s@\"]+\.)+[^<>()[\]\.,;:\s@\"]{2,})$/i)) {
                    document.querySelector("#submit").disabled = true;
                    if (username.length > 5) {
                        document.querySelector("#submit").disabled = true;
                        if (confirmation === password) {
                            document.querySelector("#submit").disabled = false;
                        }
                    }
                }
            }
            else {
                document.querySelector('#submit').disabled = true;
            }
        }
    </script>
{% endblock %}

Here is the output of my console:

* Serving Flask app "application.py"
 * Environment: production
   WARNING: This is a development server. Do not use it in a production deployment.
   Use a production WSGI server instead.
 * Debug mode: off
 * Running on http://127.0.0.1:5000/ (Press CTRL+C to quit)
127.0.0.1 - - [13/Dec/2020 19:23:44] "GET /signup HTTP/1.1" 200 -
127.0.0.1 - - [13/Dec/2020 19:23:44] "GET /static/styles.css HTTP/1.1" 200 -
Opened database successfully
Error in sign up operation

So the problem lies in signup.py

Answer 1

The try/except block is not doing you any favors. It can error on any line and all you'll know is "Error in signup operation". There will be problems because of invalid execute syntax. execute takes at most 2 arguments, the first is the sql to be executed. If it contains placeholders, the second argument is a set. Since these sqls are using named style, the second argument should be a dictionary, as per this example from the doc.

# And this is the named style:
cur.execute("select * from people where name_last=:who and age=:age", {"who": who, "age": age})

You might consider "catching" the exception and adding more info to the message, something like:

except Exception as e:
     print("Error in sign up operation: ",e)