How to show flask foreign key field in modelform with readable display dropdown?

Question

I have a one to many and many to many relationship in my models. I'm using wtforms_alchemy ModelForm to create the forms, but the ForeignKey field is not showing up as a drop down field, also it's showing the integers as values in them. I tried referencing similar questions and tried the answers, like, I put __str__() as well as __repr__() functions in them so that they return some readable and meaningful string in the drop down but that didn't happen. Does anyone have any idea of how else can I do it?

Models.py-


class Product(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(20), nullable=False)
    location_id = db.Column(db.Integer, db.ForeignKey('location.id'))

    def __init__(self, name, location=None):
        self.name = name
        if location:
            self.location_id = location.id

    def __repr__(self):
        warehouse = Location.query.filter_by(id = self.location_id).first()
        qty = warehouse.get_product_quantity(self.name)
        return '{0}-{1}-{2}'.format(self.name, warehouse.name, qty)

class Location(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(20), unique=True, nullable=False)
    products = db.relationship('Product', backref='warehouse')

    def __init__(self, name):
        self.name = name

    def __repr__(self):
        return 'Location-{0}'.format(self.name)

class Movement(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    timestamp = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
    from_location_id = db.Column(db.Integer, db.ForeignKey("location.id"))
    to_location_id = db.Column(db.Integer, db.ForeignKey("location.id"))
    from_location = db.relationship(Location, lazy='joined', foreign_keys=[from_location_id], backref='from_location')
    to_location = db.relationship(Location, lazy='joined', foreign_keys=[to_location_id], backref='to_location')
    product_id = db.Column(db.Integer, db.ForeignKey('product.id'))
    product = db.relationship(Product, backref='movements')
    qty = db.Column(db.Integer)

forms.py -

class ProductForm(ModelForm, Form):
    class Meta:
        model = Product
        include = ['name']

class LocationForm(ModelForm, Form):
    class Meta:
        model = Location
        include = ['name']

class MovementForm(ModelForm, Form):
    class Meta:
        model = Movement
        include = ['from_location_id', 'to_location_id', 'product_id', 'qty']

score 0 · Answer 1 · answered Jan 29 '20 at 17:36

As the Id's are integers the WTForm-ALchemy will convert the form field to integer field you can see this here

So, you can force to use select field as explained here

If do you want some more readable you can use the QuerySelectField.

class MovementForm(ModelForm, Form):
    class Meta:
        model = Movement

    product = QuerySelectField(
        query_factory=lambda: Product.query.all(),
        get_pk=lambda a: a.id,
        get_label=lambda a: a.name,
        allow_blank=False,
    )

If you prefer, you can use the view/route to query and pass to the form the options.

def movement_new():
    movement = Movement()
    form = MovementForm(obj=movement)

    products = [(c.id, c.name) for c in Product.query.all()]
    form.product_id.choices = products
    ...

Then on MovementForm

class MovementForm(ModelForm, Form):
    class Meta:
        model = Movement    ---> note tha the include was removed

    product_id = SelectField('Product', coerce=int)
  ...

score 0 · Answer 2 · answered Jun 05 '21 at 14:06

You need to pass the select options from your route function

Example using your models and forms:

@app.route('/movement/<movement_id>')
def edit_movement(movement_id):
    products = db.engine.execute("select id, product_name from products")
    form.product.choices = [(p.id, p.product_name) for p in products]
    return render_template('movement.html', movement_id=movement_id, form=form)

How to show flask foreign key field in modelform with readable display dropdown?

2 Answers2