Flask SQL Alchemy - How do I update several records at once through a form

Question

I am struggling fundamentally with how to use forms with SQL Alchemy. Every tutorial and example that I do focuses on single record forms where each field is updatable to ultimately make a record.

I want to present my data as a table full of (c100 rows of) transactions updating each row on one column only. It's a list of expenditure and I want to identify which transactions are to be excluded from the analysis. I can present it as a table with one form field per row, but when I submit that back to my view, I have no way of obtaining the table row ID to write it to the database.

Does anyone know of an example, or a tutorial where the focus is on managing many records? Perhaps an admin function for managing all the entries in a database or something? e.g. select all those records to then delete them.

The code I have is as follows but if I'm honest I wonder whether I am even going about it the right way.

My Model:

class Transaction(db.Model):
__tablename__ = 'transactions'
id = db.Column(db.Integer, primary_key=True)
date = db.Column(db.Date, index=True)
description = db.Column(db.String(128), index=True)
amount = db.Column(db.Float, index=True)
balance = db.Column(db.Float)
excluded = db.Column(db.Integer, index=True)
source_id = db.Column(db.Integer, db.ForeignKey('sources.id'))
category_id = db.Column(db.Integer, db.ForeignKey('categories.id'))

My Form:

class TransExclude(FlaskForm):
excluded = SelectField('Exclude', choices = EXCLUDED_CHOICES, validators=[DataRequired()])
submit = SubmitField('Add')

My View:

@app.route('/exclude_transactions', methods=['GET', 'POST'])
    def exclude_transactions():
        form = TransExclude()
        if request.method == "POST":
           # This is where I need the ID of the record as well as the excluded return...
           # excluded = dict(EXCLUDED_CHOICES).get(form.excluded.data)
           req = request.form
           df = pd.DataFrame(list(req.items(1)))
           print(df)

           flash('Congratulations, you have updated the exclusions!')
           return redirect(url_for('process_new_month'))

    transactions = Transaction.query.all()
    return render_template('excludes.html', title= 'Monthly Excludes',transactions=transactions, form=form)

and my HTML:

{% extends "base.html" %}
{% import 'bootstrap/wtf.html' as wtf %}

{% block app_content %}

<div class="container">
    <div class="row">
        <div class="col-sm">
            <br>
            <h2>Exclude Transactions</h2>
            <hr>
            <form method="post">
            <table class="table table-striped">
                 <thead>
                    <tr>
                        <th>ID</th>
                        <th> Date</th>
                        <th>Description</th>
                        <th> Amount</th>
                        <th>Exclude?</th>
                        <th></th>

                    </tr>
                 </thead>

                 <tbody>
                 {% for transaction in transactions %}
                    <tr>
                       <td>{{transaction.id}}</td>
                       <td>{{transaction.date}}</td>
                       <td>{{transaction.description}}</td>
                       <td>{{transaction.amount}}</td>
                        <td>{{form.excluded}}</td>
                    </tr>

                 {% endfor %}
                 </tbody>
            </table>
            <button type="submit" class="btn btn-primary">Exclude Items</button>
            </form>
        </div>
    </div>
</div>

{% endblock %}

Answer 1

The following code shows you an example in which a field of the type FieldList is used in combination with FormFields. Inside each nested form there is a hidden field that contains the ID of the data record. To achieve this, a dictionary is filled in with the required data when the parent form is created.
I wrote comments in the code to make it easier to understand.

This solution is certainly not optimal, but it is a possibility.
I think you should be able to apply this variant to your code.

class Item(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String, nullable=False)

class _SubForm(Form):
    # The HiddenField later contains the id of the data record. 
    id = HiddenField('id')
    excluded = SelectField('Exclude', choices=EXCLUDED_CHOICES, validators=[DataRequired()])

    # The constructor is overwritten in order to bypass further fields for the csrf token. 
    def __init__(self, csrf_enabled=False, *args, **kwargs):
        super(_SubForm, self).__init__(csrf_enabled=csrf_enabled, *args, **kwargs)

class EditForm(FlaskForm):
    items = FieldList(FormField(_SubForm))

@app.route('/edit', methods=['GET', 'POST'])
def edit():
    # Fill in the form with the necessary data. 
    items = Item.query.all()
    form = EditForm(request.form, data={ 'items': items })
    if form.validate_on_submit():
        # Iterate over the FieldList here. 
        for field in form.items.data:
            id = item_field.get('id')
            excluded = item_field.get('excluded')
            print(id, excluded)
    return render_template('edit.html', **locals())

<form method="post">
  {{ form.csrf_token }}
  <table>
    {% for field in form.items -%}
    <tr>
      <td>
        {{ items[loop.index0].title }}
      </td>
      <td>
        {{ field.hidden_tag() }}
        {{ field.excluded }}
      </td>
    </tr>
    {% endfor -%}
  </table>
  <input type="submit" />
</form>