[FIXED] Let user update any number of rows in a dynamic table using Flask

Issue

I'm trying to code a database powered web application with Flask/Python + SQLite back-end and HTML/CSS/JS front-end (Bootstrap). In one section, user should be able to change any number of fields at once — apart from the disabled ones — in a dynamically generated table showing book items, i.e., book title, author, publisher etc.

The code of the web is as follows (I'm posting only the generator part, the table is indeed whole and enclosed in form tags which submit the data no problem):

<tr>
    <td class="fw-bold number"></td>
    <td class="fst-italic">
       <input class="form-control" type="text" name="title.{{ book['title'] }}" value='{{ book["title"] }}'>
    </td>
    <td class="table-active">{{ book["translator"] }}</td>
    <td>
       <input class="form-control" type="text" name="author.{{ book['author'] }}" value='{{ book["author"] }}'>
    </td>
    <td>
       <input class="form-control" type="text" name="publisher.{{ book['publisher'] }}" value='{{ book["publisher"] }}'>
    </td>
    <td>
       <input class="form-control" type="text" name="year.{{ book['published'] }}" value='{{ book["published"] }}'>
    </td>
    <td>
       <input class="form-control" type="text" name="pages.{{ book['pages'] }}" value='{{ book["pages"] }}'>     
    </td>
</tr>

So far, I've managed to get only half way with two solutions. Using one all-encompassing for loop, my script changes all the instances of the same value, not just the desired one. However, with separate loops and breaks, the user is no longer able to update several fields of the same category in one go.

I've read quite a lot of other questions and searched the internet, but it seems my problem is quite specific. Or I'm not using the right keywords...

The Python code (Again, just the snippet showing the second solution on the example of two columns. The rest of the function seems to work fine, rendering resulting "bad" data in the template all right. Also, nevermind the flashes, they'll be done differently anyway.):

 my_books = db.execute("SELECT * FROM books_test")

    if request.method == "POST":
        for book in my_books:
            # Get book ID
            book_id = book["id"]
            
            # Check new titles
            old_title = book["title"]
            new_title = request.form.get(f"title.{old_title}")
            if new_title != old_title:
                db.execute("UPDATE books_test SET title=? WHERE id=?", new_title, book_id)
                flash("Titul knih/y byl změněn.")
                break
        
        for book in my_books:
            # Get book ID
            book_id = book["id"]
            
            # Check new authors
            old_author = book["author"]
            new_author = request.form.get(f"author.{old_author}")
            if new_author != old_author:
                db.execute("UPDATE books_test SET author=? WHERE id=?", new_author, book_id)
                flash("Autor knih/y byl změněn.")
                break

I have a feeling that this case just needs tweaking the logic behind the algorithm a little bit, but I can't wrap my head around it. Could someone point me in the right direction?

Solution

I believe your problem is that you use request.form.get(...) to find a field simply based on the name of the field. This doesn't work when multiple fields have the same value. You need to find a way to make each field unique so that you can find the single field and edit only that one. I would suggest adding the book's id to each field, something like this.

<input class="..." type="text" name="title.{{ book['title'] }}.{{ book['id'] }}" value='{{ book["title"] }}'>

This would then allow you to find the exact input that you want to edit with the following bit:

for book in my_books:
    # Get book ID
    book_id = book["id"]
    
    # Check new titles
    old_title = book["title"]
    new_title = request.form.get(f"title.{old_title}.{book_id}")
    if new_title != old_title:
        db.execute("UPDATE books_test SET title=? WHERE id=?", new_title, book_id)
        flash("Titul knih/y byl změněn.")
        break

Basically, we're just adding a unique identifier to each field so that you can find the right one to edit.

Answered By - Patrick Yoder