Final Project: Problem to UPDATE table (Flask and SQLite) - RuntimeError ("unsupported value: {}".format(value))

Question

I'm working on a project tracking web app, in which users can create and categorise their own projects. There are more options, but a project can mainly be categorised as active or paid/finished. Any active project can be updated by changing its status from a status dropdown menu and by clicking on an update button (to save the change and refresh the page). If the update is to paid/finished status, the project should automatically disappear from active and be listed in paid/finished projects instead.

When an user updates a project, flask updates the database with the following SQLite code:

db.execute("UPDATE projects SET status = :status \
            WHERE userId = :userId AND projectId = :projectId",
            userId = session["id"],
            projectId = db.execute("SELECT projectId FROM projects WHERE userId = :userId", userId = session["id"]),
            status = request.form.get("status"))

but projectId runs into a RuntimeError: unsupported value error.

If I don't specify any projectId when updating, like:

db.execute("UPDATE projects SET status = :status \
            WHERE userId = :userId",
            userId = session["id"],
            status = request.form.get("status"))

the change of status works, but it updates all projects of the current user, instead of just the project where the change of status was intended to happen.

Any idea of what am I missing / failing into? I feel like I am very close to find the solution but I've been stuck in this for a few days now. Any help is greatly appreciated!

==========================================================================

The complete flask route I've implemented for the active projects page (/active.html) is the following:

@app.route("/active", methods = ["GET", "POST"])
@login_required
def active():

    """List of active projects of logged user"""

    if request.method == "GET":

        rows = db.execute("SELECT projectId, projectName, clientName, year, month, status, price \
                           FROM projects \
                           WHERE userId = :userId AND status != 'Paid'",
                           userId=session["id"])

        projects = []
        for row in rows:
            projects.append({"ID":row["projectId"],"Project":row["projectName"], "Client":row["clientName"],
                             "Year":row["year"], "Month":row["month"], "Status":row["status"],
                             "Price":row["price"]})

        return render_template("active.html", projects = projects)

    elif request.method == "POST":

        db.execute("UPDATE projects SET status = :status \
                    WHERE userId = :userId AND projectId = :projectId",
                    userId = session["id"],
                    projectId = db.execute("SELECT projectId FROM projects WHERE userId = :userId", userId = session["id"]),
                    status = request.form.get("status"))

        flash("Project updated successfully!")
        return redirect("/active")

And this is the error:

RuntimeError("unsupported value: {}".format(value))
RuntimeError: unsupported value: {'projectId': 12}

Finally, this is my HTML for the active.html page:

{% extends "layout.html" %}

{% block title %}
    Active projects
{% endblock %}

{% block main %}
    <table class="table table-striped">
        <thead>
            <tr>
                <th>ID</th>
                <th>Project</th>
                <th>Client </th>
                <th>Year</th>
                <th>Month</th>
                <th>Price</th>
                <th>Status</th>
                <th>Update</th>
            </tr>
        </thead>

        <tbody>
            {% for project in projects %}
            <tr>
                <td>{{ project["ID"] }}</td>
                <td>{{ project["Project"] }}</td>
                <td>{{ project["Client"] }}</td>
                <td>{{ project["Year"] }}</td>
                <td>{{ project["Month"] }}</td>
                <td>{{ project["Price"] }}</td>

                <form action="/active" method="post">
                    <td>
                        <div class="form-group">
                            <select class="form-control" name="status">
                                <option disabled="" selected="" value="">{{ project["Status"] }}</option>
                                <option value="Waiting for client's action">Waiting for client's action</option>
                                <option value="Waiting for my action">Waiting for my action</option>
                                <option value="Waiting for payment">Waiting for payment</option>
                                <option value="Paid">Paid</option>
                            </select>
                        </div>
                        <td>
                            <button class="btn btn-primary" type="submit">Update status</button>
                        </td>
                    </td>
                </form>
            </tr>
            {% endfor %}
        </tbody>
    </table>
{% endblock %}

Answer 1

There are a lot of problems that are visible. Firstly, when update is clicked, the projectId is not returned. This can be solved simply be adding the following statement in the form

<input type="hidden" name="projectId" value="{{ project["ID"] }}">

and

projectId = request.form.get("projectId")

in the POST part of route.

Also, I would suggest that instead of use of a button for submitting, you should use an input statement of type of submit. This will be a safer option.

If this helps, please check the tickmark.