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:
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
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.
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.
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 .
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.
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.
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.
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With