Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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

  1. How are the above requirements typically implemented in Flask?
  2. Is this many-to-many scenario something that Django can handle more easily through its admin interface?
like image 781
boadescriptor Avatar asked Aug 04 '14 11:08

boadescriptor


People also ask

How do you make a many to many relationship in Flask?

You add a tags class variable to the Post model. You use the db. relationship() method, passing it the name of the tags model ( Tag in this case). You pass the post_tag association table to the secondary parameter to establish a many-to-many relationship between posts and tags.

How do you update existing table rows in SQLAlchemy in Python?

Update table elements in SQLAlchemy. Get the books to table from the Metadata object initialized while connecting to the database. Pass the update query to the execute() function and get all the results using fetchall() function. Use a for loop to iterate through the results.

How do you create a one to many relationship in flask SQLAlchemy?

Flask Flask-SQLAlchemy Relationships: One to Many In order to achieve that we place a Foreign key on the child referencing the parent that is from our example we place a foreign key on Post class to reference the User class. We then use relationship() on the parent which we access via our SQLAlchemy object db .

What is flask-SQLAlchemy?

Flask-SQLAlchemy is a Flask extension that makes using SQLAlchemy with Flask easier, providing you tools and methods to interact with your database in your Flask applications through SQLAlchemy.

How to create many to many relationship between two tables?

Many to Many relationship between two tables is achieved by adding an association table such that it has two foreign keys - one from each table’s primary key. Moreover, classes mapping to the two tables have an attribute with a collection of objects of other association tables assigned as secondary attribute of relationship() function.

How do I add a many-to-many relationship to a Flask web application?

To demonstrate adding a many-to-many relationship to a Flask web application, you will use the previous tutorial’s application code, which is a to-do management web application built using Flask, SQLite, and the Bootstrap framework. With this application users can create new to-dos, modify and delete existing to-dos, and mark to-dos as complete.

Can flask and SQLite be used to build web applications?

Developer and author at DigitalOcean. The author selected the COVID-19 Relief Fund to receive a donation as part of the Write for DOnations program. Flask is a framework for building web applications using the Python language, and SQLite is a database engine that you can use with Python to store application data.


1 Answers

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

like image 191
krekle Avatar answered Sep 28 '22 10:09

krekle