How to populate a dropdown box with a database query instead of a list in flask

Question

this could be a very easy question but as I novice I have to ask here sorry as I have not found the answer so far after a lot of playing with it.

I'm using flask with a python list of food types to allow users to input a food item into a form and have this verified with validators to make sure the item is in the list. The inputted food items from the form then gets stored in a db table.

I wish to replace this list approach with a sql query from a db of pre defined food types, whilst using the SQLAlchemy as this is used elsewhere in the app.

A simplified version of the code is as follows: -

#db connection is already setup and working OK in another part of the app
DB_URL = 'postgresql+psycopg2://{user}:{pw}@{url}:{port}/{db}'.format(user=POSTGRES_USER, pw=POSTGRES_PW, url=POSTGRES_URL, port=POSTGRES_PORT, db=POSTGRES_DB)
app.config['SQLALCHEMY_DATABASE_URI'] = DB_URL
db = SQLAlchemy(app)

#example of the list
eaten = ['bread', 'crackers', 'ham', 'bacon']

#where the 'food_word' variable gets defined from an input and validated
food_word_1 = StringField('add the food type you have eaten here:', validators=[Optional(), AnyOf(eaten)])

I've tried replacing the list with eaten = db.execute('SELECT food_name FROM food_type') (table & column) with no luck.

I'm not sure if I need to create some kind of class/methods in the model.py for this Select/GET operation or even use something like pandas (which I also have in the app) to do this job.

Any guidance appreciated!

thanks, md

In fact I get an error so no result unfortunately. I will reply to Naveneetha's suggestion with the error massage — micdoher
– micdoher, Commented May 4, 2020 at 12:33

NavaneethaKrishnan · Accepted Answer · 2020-05-04 16:52:48Z

1

SQLAlchemy is an Object Relational Mapper. It helps to interact with the database without SQL query. It is an abstraction to the database. So you should not write SQL query here. Rather you have to create an inherited class from db.Model. Like below.

class FoodType(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    food_name = db.Column(db.String(120), unique=True, nullable=False)

def __repr__(self):
    return '<User %r>' % self.food_name

Then for fetching data, you have to call the query function,

 db.query.all()
 # or
 db.query.filter_by()

The result will be a single list.

If you using postgres directly, without SQLAlchemy, then SQL query will be like,

>>> conn = psycopg2.connect(DATABASE_URL)
>>> cur = conn.cursor()
>>> cur.execute('SELECT food_name FROM food_type')
>>> cur.fetchall()
[['bread'], ['crackers'], ['ham'], ['bacon']]

If you want to convert as a single list,

eaten = [i[0] for i in db.fetchall()]

edited May 4, 2020 at 16:52

answered Apr 30, 2020 at 17:56

NavaneethaKrishnan

1,3561 gold badge10 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

micdoher Over a year ago

Thanks for the advice @Nathaneethta and I played around with this but still getting an error which I captured here ** File "/hold/hold/forms.py", line 21, in <module> db.execute('SELECT food_name FROM food_type') AttributeError: 'SQLAlchemy' object has no attribute ‘execute' **. I'm starting to think it could be something more basic so as I imported this database table (for the other tables I use in the app I created them), do I need to create a 'model' class for this to be referenced?

NavaneethaKrishnan Over a year ago

yeah. if you are using sqlAlchemy then you have to create Class. i thought you were using normal postgres module. I mean without SqlAchemy. Let me edit the answer then,

micdoher Over a year ago

Thanks a lot for the guidance @Navaneetha as it helped me a lot realizing I had not created a model for this new database table as I am still in leaning mode here. Once the new model code was added in the models.py, in the forms.py I used the code eaten = Food.query.order_by(Food.food_name) and it works well!

Collectives™ on Stack Overflow

How to populate a dropdown box with a database query instead of a list in flask

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related