Update many-to-many relationships using Flask, SQLAlchemy and WTForms?

Question

I want to create a form for many-to-many relations using Flask, SQLAlchemy and WTForms that represents these models:

personaddress = db.Table('personaddress',
    db.Column('person', db.Integer, db.ForeignKey('person.id')),
    db.Column('address', db.Integer, db.ForeignKey('address.id'))
)

class Person(db.Model):
    __tablename__ = "person"
    id = db.Column(Integer, primary_key=True)
    name = db.Column(String, nullable=False)
    addresses = db.relationship('Address', secondary=personaddress, backref=db.backref('person', lazy='dynamic'))

class Address(db.Model):
    __tablename__ = "address"
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String, nullable=False)

Requirements

Now I want to create a single page that contains forms to achieve the following:

add/edit/delete a person
add/edit/delete a address
add/edit/delete a relation between a person and an address

Important requirement: Using QuerySelectField, I can choose existing addresses for a person. But I want to add new addresses in the same form.

I've played around with model_form for the main models and subforms using FormField for the junction table but I just can't figure out how to update everything including the foreign key relations. The page should have a single submit button for all forms and subforms displayed.

Questions

How are the above requirements typically implemented in Flask?
Is this many-to-many scenario something that Django can handle more easily through its admin interface?

Follow the Flask-SQLAlchemy example on how to build a proper many-to-many relationship ( pythonhosted.org/Flask-SQLAlchemy/… ) as at the moment you have no relationships defined. Then using wtforms/flask-wtf create a QuerySelectField fed from a query_factory to pull the objects you want (stackoverflow.com/questions/17887519/…) from your objects into your form. — Doobeh
I added the relationships, but QuerySelectField alone won't meet the requirements. — boadescriptor
You might try doing this with PonyORM instead of sqlalchemy. Here is their editor so you can get all the proper outputs you want PonyOrm — nadermx

krekle krekle · Accepted Answer · 2016-03-22T09:27:19

I have also encountered something similar earlier. I tried to solve it by using model_form, but it doesn't quite solve the problem of adding new entries dynamically, and I was having a hard time using it when dealing with relations.

Using the QuerySelectField in WTForms will only help you populating eg. an < select > with id, value pairs corresponding to the existing addresses. But it still renders to a regular html form in the template.

By using some sort of multiselect with the possibility to dynamically add new options in the frontend you can send additional addresses in the same form. The the endpoint will take care of creating new Addresses if they don't exist in the db.

The WTForm form would be:

from app import db

class PersonAddressForm(Form):
    id = HiddenField('id')
    name = StringField('Name')
    addresses = QuerySelectField('Addresses', 
            query_factory=lambda: db.session.query(Address), 
            get_pk=lambda a: a.id, get_label=lambda a: a.name)

    # Custom validate
    def validate(self):
        # ... custom validation
        return True

And the route something like:

# ... this will be used to create and update a user
@route('create/<userid>', methods=["GET"])
def get_user_form(userid):
    # ... Get the Person
    user = Person()
    if userid:
        # ... if userid supplied, use existing Person object
        user = Person.query.get(userid)

    # ... Populate the form
    person_form = PersonAddressForm(obj=user)

    # ... return form
    return render_template('somepage.html', form=person_form)

@route('create/<userid>', methods=["POST"])
def post_person_form(userid):
    person_form = PersonAddressForm(request.form)

    if person_form.validate():
        # ... Get db object
        person = db.session.query(Person).get(form.id)

        # ... Add changes to the object from the form
        person_form.populate_obj(obj=person_address)

        # ... Get addresses
        addresses = form.addresses.raw_data

        # ... loop over and add to person
        for address in addresses:
            # Add or create an address
            actual_address = db.session.query(Address).get(address.id)

            # ... check if address is existing
            if not actual_address:
                # ... if address not existing, create new one
                actual_address = Address(address.name)
                db.session.add(actual_address)

            # ... Append new or created address to person
            person.addresses.append(actual_address)

        # ... save changes to the db
        db.session.commit()

        # ... Update/Create complete
        return redirect(url_for('get_users'))

    else:
        # ... form not valid, notify user
        # ...

This will handle edit/create user and create Address. As well as create the relation between. To make it also support delete Address, change

person.addresses.append(actual_address)

to

person.addresses = list_of_actual_addresses

and change this in the person model (cascade='delete-orphan')

addresses = db.relationship('Address', secondary=personaddress, cascade='delete-orphan' backref=db.backref('person', lazy='dynamic'))

This will make the form update the entire address relation each time and the cascade will delete orphaned addresses. So the entire addresses list for a person would be updated each time the form is submitted.

When dealing with WTForms in templates i highly recommend using macros if you don't already. You would have to rewrite it to some degree, but check this out.

Hope this helps

Update many-to-many relationships using Flask, SQLAlchemy and WTForms?

1 Answers