[FIXED] Making a flask button that updates my SQL db file

Issue

I'm trying to make an index file that displays two tables:

1. users account details (done)
2. equipment the user has borrowed.

In the second table, I'm trying to add a button that allows the users to return the borrowed equipment (by updating the data file in my SQL file).

Python (app.py file):

@app.route("/", methods=["GET", "POST"])
@login_required
def index():
#Table 1 (Done)
    rows1 = db.execute("SELECT name AS n FROM users WHERE id = ?", session["user_id"])
    row1 = rows1[0]
    name = (row1["n"])
    rows2 = db.execute("SELECT user_id AS i FROM users WHERE id = ?", session["user_id"])
    row2 = rows2[0]
    user_id = (row2["i"])
    rows3 = db.execute("SELECT phone AS p FROM users WHERE id = ?", session["user_id"])
    row3 = rows3[0]
    phone = (row3["p"])

#Table 2
    borrowed = db.execute("SELECT equipments.eqp_name, timestamp, purpose, transactions.details, transactions.eqp_serial, return_date FROM transactions JOIN equipments ON equipments.eqp_serial = transactions.eqp_serial WHERE user_id = ? AND availability = 2", session["user_id"])

**    if request.method == "POST":
        # TODO**
    else:
        return render_template("index.html", name = name, user_id = user_id, phone = phone, borrowed = borrowed)

HTML (index.html file)

    </table>
    <br>
    <table>
        <tr style="background-color: #D6DBDF">
            <th>Items borrowed</th>
            <th>Serial</th>
            <th>Borrow Date</th>
            <th>Expected Return Date</th>
            <th>Purpose</th>
            <th>Details</th>
            <th>Return</th>

        </tr>
        <!--equipment borrowed under user for loop-->
        {% for borrow in borrowed %}
        <tr>
            <td>{{ borrow.eqp_name }}</td>
            <td>{{ borrow.eqp_serial }}</td>
            <td>{{ borrow.timestamp }}</td>
            <td>{{ borrow.return_date }}</td>
            <td>{{ borrow.purpose }}</td>
            <td>{{ borrow.details }}</td>
            <form method = "post">
            <td><button name="return">Return</button></td>
            </form>
        </tr>
        {% endfor %}
    </table>

I'd want it to look something like this. Once the button at the return column is clicked, the said equipment's SQL data will be updated to available, allowing other users to borrow it after.

How can I go about this?

Solution

Yes, it is possible to add a button to the second table that allows users to return borrowed equipment by updating the data file in your SQL file.

in index.html

<tr>
    <td>{{ borrow.eqp_name }}</td>
    <td>{{ borrow.eqp_serial }}</td>
    <td>{{ borrow.timestamp }}</td>
    <td>{{ borrow.return_date }}</td>
    <td>{{ borrow.purpose }}</td>
    <td>{{ borrow.details }}</td>
    <td>
        <form method="post" action="/return_equipment">
            <input type="hidden" name="eqp_serial" value="{{ borrow.eqp_serial }}">
            <button type="submit" name="return">Return</button>
        </form>
    </td>
</tr>

now Update app.py

from flask import request, redirect, url_for

@app.route("/return_equipment", methods=["POST"])
@login_required
def return_equipment():
    if request.method == "POST":
        eqp_serial = request.form.get("eqp_serial")

        # Update equipment availability to "available"
        db.execute("UPDATE equipments SET availability = 1 WHERE eqp_serial = ?", eqp_serial)
    # You may want to update the transaction or perform additional actions here.

return redirect(url_for("index"))

now, When a user clicks the "Return" button, a POST request to the /return_equipment endpoint with the equipment serial is sent. The server will then update the availability of the equipment in the database.

Answered By - Tusher