What is the best way to create a dynamic selection list from database column with Python and Flask WFForms?

Spent several hours today trying to get the unique values in my database into a dropdown selection with Flask WTForms.

The Database is MySQL, and I’m actually using SQLAlchemy for the majority of this app, but I’ve been unable to get unique values into the dropdown with SQLAlchemy (only seem to manage to get the string representaion in or the query itself)

other options use QuerySelectField, but this is from WTForms.ext which will be depreciated from WTForms3 so I’d rather avoid it.

The solution I have seems clunky and I feel there should be a better, more pythonic way. My current solution is with the mysql.connector

cur = cnx.cursor()
cur.execute("SELECT markets.id AS markets_id, markets.market AS markets_market FROM markets")
r = cur.fetchall()
markets = [] # this gets passed into the SelectField() choices
for el in r:
    markets.append(el[1])

#create the forms to create and update Competitor model
class CompetitorForm(FlaskForm):
    competitor_name = StringField('Competitor Name', validators=[DataRequired()])
    country = SelectField('Active Market(s)', validators=[DataRequired()],
                choices=markets)
    headquarters = StringField('HQ Country', validators=[DataRequired()])
    submit = SubmitField('Add Competitor')

While this works and I can get a unique dropdown (the query itself isn’t unique, but this table is just a list of countries, no duplicates), I don’t feel like this is very scalable as my forms get more complex. Also the way in which I’ve setup the models, (Market, Competitor and then a lookup table), I need to map this back to a market ID and add to the lookup table (code for this isn’t shown here as I’m only concerned with the selection dropdown for now, but i thought the context would help understand the models:

### create the competitor model ###
class Competitor(db.Model):
        __tablename__ = "competitors"
        id = db.Column(db.Integer,primary_key=True)
        competitor_name = db.Column(db.String(64))
        headquarters = db.Column(db.String(64))
        active_markets = db.relationship('CompMarketLK',
                                        backref = db.backref('active_market_id',lazy='joined'),
                                        lazy='dynamic')

        #initialise the model
        def __init__(self,competitor_name,country,products,
                        workflow_id, headquarters,comp_type,employees):
                ...

        def __repr__(self):
                return f"Company Name: {self.competitor_name}"

### create the market model object ###
class Market(db.Model):
        __tablename__ = 'markets'
        id = db.Column(db.Integer,primary_key=True)
        market = db.Column(db.String(64),nullable=False)
        #define relationships
        active_competitors = db.relationship('CompMarketLK',
                                                backref = db.backref('competitor_id',lazy='joined'),
                                                lazy='dynamic')

        #construct model object
        def __init__(self,id,market):
                ...

        def __repr__(self):
                return f"Market: {self.market}"

### create the competitor <--> market lookup table ###
class CompMarketLK(db.Model):
        __tablename__ = 'comp_market_lk'
        market_id = db.Column(db.Integer,
                                ForeignKey('markets.id'),
                                primary_key=True)
        comp_id = db.Column(db.Integer,
                        ForeignKey('competitors.id'),
                        primary_key=True)

        #construct model object
        def __init__(self,market_id,comp_id):
                ...

Answer

this answer has most of the groundwork, with this I used the following to create the distinct list:

    form.country.choices = [country.market for country in db.session.query(
                                                      Market.market).distinct()]

this gave:

def create():
form = CompetitorForm()
form.country.choices = [country.market for country in db.session.query(Market.market).distinct()]
if form.validate_on_submit():
    competitor = Competitor(competitor_name=form.competitor_name.data,
                            country = form.country.data,
                            headquarters = form.headquarters.data,
                            )
    #add to the db
    db.session.add(competitor)
    db.session.commit()
    return redirect(url_for('core.index'))
return render_template('add_competitor.html',form=form)