Fill a Dropdown/Combo in Flask and PostgreSQL

In web applications it is very essential to fill a dropdown or a combo box with data stored in another table.  This is important thing to learn while creating web applications to maintain data consistency. For example you don’t want your users to punch in a city name since they might err in spellings. So, it will be better to have a table storing all the cities with their city codes and fill the combo with data from this table. Whenever a user selects a city name from dropdown its city id is fetched and passed on with POST method. This way there is the least chance of storing different spellings of the same city.  Another advantage is that a user has to type lesser.  So, in this post we will explain the steps and the code to Fill a Dropdown/Combo in Flask and PostgreSQL web application.

Step 1- Get the dataset from a Master Table

To fill a Dropdown/Combo in Flask and PostgreSQL web application the first step is done in the application.py or app.py file. The code has to be added in the route procedure that will render the HTML file with web form containing the dropdown. You need to execute a SELECT command on database connection and get the list with tuples ( city_id ,city_name) values

Step 2- Pass this Dataset as a parameter

In the return statement of the route procedure that returns render_template  pass the created list of  ( city_id ,city_name)  tuples as parameter. It is used to fill the drop down/ combo box/ Option box.

Step 3- Fill the dropdown using Jinja for loop

The last step will fill a Dropdown/Combo in Flask and PostgreSQL using the Jinja for loop. This loop is preceded by the SELECT HTML tag with a name of SELECT dropdown to be referred when POST method returns the form data . In  Jinja for loop OPTION is created using the name as the display value and id as the value part.

The example code uses the table cities having two columns city_id and city_name.

We want to display city name and when a user selects city name, the city_id must be returned

In application.py add this route

@app.route("/input")
def input():
    cityList=db.execute("SELECT * FROM cities order by city_name")
    return render_template("input.html",cityList=cityList )

In the input.html stored in the templates folder, add the following code to create the combo box/ dropdown with list of cities form the table cities.

Choose a City<SELECT name="city">
{% for c in cityList %}
	<OPTION value={{c.city_id}}>{{c.city_name}}</option>
{% endfor %}
</SELECT>

admin