How to populate a html dropdown dynamically with values from a postgres database?

Question

I am making a website with Flask and I am trying to create a dropdown menu filled with all regions in the regions table from my postgres database. Currently I have hardcoded the dropdown like this:

<label for="regions">Choose a region:</label>

<select name="regions" id="regions">
  <option value="murray">Murray</option>
  <option value="darling">Darling</option>
  <option value="coast">Coast</option>
  <option value="central">Central</option>
</select>

However, I would like to change this so that the dropdown is populated based off of an sql query of the table with all regions. Is this possible?

The SQL Query for the names of all the regions is:

select region_name from regions

You can try to get values from database in your server and send those values to your front-end page. Inside your html, you can use Jinja to loop through your data and assign it inside the option tag. — Oscar916
– Oscar916, Commented Jan 24, 2021 at 4:48
How would you do this? Could sqlalchemy possibly be used to get the values? — Ben Harvey
– Ben Harvey, Commented Jan 24, 2021 at 4:50
You can use pycopg2 to connect to your postgres database. https://www.postgresqltutorial.com/postgresql-python/connect/ for reference. — Oscar916
– Oscar916, Commented Jan 24, 2021 at 4:54

Arpit Omprakash · Accepted Answer · 2021-01-24 08:39:28Z

2

In the backend (flask) you can fetch all the values from the Postgres database using the query:

# code to connect to db
...
# code to fetch from database
list_of_regions = db.execute("select region_name from regions")

Then pass on those values to the front end page:

@app.route("/")
def index():
    return render_template("index.html", list_of_regions = list_of_regions)

On the front end you can loop through the values with Jinja and add the items to the dropdown:

<label for="regions">Choose a region:</label>

<select name="regions" id="regions">
  {% for region in list_of_regions %}
    <option value={{region.lower()}}>{{region}}</option>
  {% endfor %}
</select>

Just replace your case-specific code with the general code that is provided above and it should be good to go. I was not sure about how to connect to postgres database as I have worked mostly with MySQL, so I guess this package might help you out better for that.

answered Jan 24, 2021 at 8:39

Arpit Omprakash

11810 bronze badges

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

3 Comments

Ben Harvey Over a year ago

Hi, I am getting the error: jinja2.exceptions.UndefinedError: 'tuple object' has no attribute 'lower'... do you know what error in your code might be causing this?

Arpit Omprakash Over a year ago

You are probably trying to pass on the .lower() method to a tuple object. Can you show the error you are getting along with the code that's causing it. That will help me to help you better.

Ben Harvey Over a year ago

It is solved now I just needed to remove the .lower method as I did not need it anyway

Collectives™ on Stack Overflow

How to populate a html dropdown dynamically with values from a postgres database?

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