Populate a WTForms SelectField with an SQL query

Question

I'm want to populate a WTForms SelectField with the rows returned by this query:

cur.execute("SELECT length FROM skipakke_alpin_ski WHERE stock > 0")

The query returns rows with the ski length of different types of skis. cur.fetchall() returns the following tuple:

[(70,), (75,), (82,), (88,), (105,), (115,), (125,), (132,), (140,), (150,), (160,), (170,)]

How would I go about to do add these numbers to a SelectField, so that each ski length would be its own selectable choice? If I had done this manually, I would have done the following:

ski_size = SelectField('Ski size', choices=['70', '70', '75', '75'])

... And so on for all of the different lengths.

Answer 1

In one of the projects I have used like below:

models

class PropertyType(db.Model):
    id = db.Column(db.Integer, primary_key=True)

    title = db.Column(db.String(255), nullable=False)

    def __repr__(self):
        return str(self.id)

and in forms

from wtforms.ext.sqlalchemy.fields import QuerySelectField

class PropertyEditor(Form):
    property_type = QuerySelectField(
        'Property Type',
        query_factory=lambda: models.PropertyType.query,
        allow_blank=False
    )
    //Other remaining fields

Hope this helps.

Answer 2

The solution might look like the code below.

Let's assume you have two files: routes.py and views.py

In routes.py file you put this

from flask_wtf import FlaskForm
from wtforms import SelectField

# Here we have class to render ski
class SkiForm(FlaskForm):
    ski = SelectField('Ski size')

In views.py file you put this

# Import your SkiForm class from `routes.py` file
from routes import SkiForm

# Here you define `cur`
cur = ...

# Now let's define a method to return rendered page with SkiForm
def show_ski_to_user():
    # List of skies
    cur.execute("SELECT length FROM skipakke_alpin_ski WHERE stock > 0")
    skies = cur.fetchall()

    # create form instance
    form = SkiForm()
    # Now add ski length to the options of select field
    # it must be list with options with (key, value) data
    form.ski.choices = [(ski, ski) for ski in skies]
    # If you want to use id or other data as `key` you can change it in list generator
    if form.validate():
        # your code goes here

    return render_template('any_file.html', form=form)

Remember that by default key value is unicode. If you want to use int or other data type use coerce argument in SkiForm class, like this

class SkiForm(FlaskForm):
    ski = SelectField('Ski size', coerce=int)