I have a form which has two multiselects in, one generated from a database query (Available) and the other is empty (Selected). I use javascript to move items from the 'Available' multiselect to the 'Selected'.

In flask, I want to store all the items placed into the 'Selected' multiselect into a many to many database associated with each formula.

Here is my models.py:

formula_hazards = db.Table(
    'formula_hazards',
    db.Column('formula_id', db.Integer, db.ForeignKey('formula.id')),
    db.Column('hazard_id', db.Integer, db.ForeignKey('hazard_statements.id'))
    )

class HazardStatements(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    h_code = db.Column(db.String(20), index=True, unique=True)
    h_phrase = db.Column(db.String(256))
    full_details = column_property(h_code   " "   h_phrase)

    def __repr__(self):
        return f"{self.h_code} {self.h_phrase}"

class Formula(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    number = db.Column(db.Integer, index=True, unique=True)
    signal_word = db.Column(db.String(16), nullable=True)
    
    h_rel = db.relationship('HazardStatements', secondary=formula_hazards, backref='h_rel')

Here is my app.py:

@admin.route('/admin/formulas/add', methods=['GET', 'POST'])
@login_required
def formulaadd():
    if is_super():
        formula_form = FormulaForm()
        hazard_list = HazardStatements.query.order_by(HazardStatements.h_code).all()
        if request.method == 'POST' and formula_form.validate_on_submit():
            new_formula = Formula(
                number = formula_form.number.data,
                signal_word = formula_form.signal_word.data,
                )
            selected_hazards = request.form.getlist('hazard-selected')
            #This is where I'm struggling to figure out how to write each 'selected hazards' to the database.
            db.session.add(new_formula)
            db.session.commit()
            flash('Formula has been added', category='success')
            return redirect(url_for('admin.formulaview'))
       return render_template('/admin/formulas/formula_add.html', title="Add Formula", formula_form=formula_form, hazard_list=hazard_list)

Here is my HTML/Jinja for good measure:

<div >
            <label for="hazard-options">Available Hazard Statements</label>
            <select id="hazard-options" name="hazard-options"  multiple="multiple">
                {% for hazard in hazard_list %}
                <option value="{{ hazard.id }}">{{ hazard.h_code }} {{ hazard.h_phrase }}</option>
                {% endfor %}
            </select>
            <button type="button" onclick="leftHazard()">&gt;&gt;</button>
        </div>
        <div >
            <label for="hazard-selected">Selected Hazard Statements</label>
            <select id="hazard-selected" name="hazard-selected"  multiple="multiple"></select>
            <button type="button" onclick="rightHazard()">&lt;&lt;</button>
        </div>

I'm able to write the 'new_formula' data to it's table fine, but I'm not very experienced in writing to many-to-many so I'm asking for help because I just keep running around in circles trying to figure it out.

CodePudding user response：

So after 2 days of playing around I stumbled across .extend() which allowed me to write the many-to-many values in my app.py file:

selected_hazards = request.form.getlist('hazard-selected') # HAZARD SELECT LOGIC
individual_hazards = HazardStatements.query.filter(HazardStatements.id.in_(selected_hazards))
new_formula.h_rel.extend(individual_hazards)

Hopefully this will help someone else with the same problem as I couldn't find any clear answers with countless hours of searches.