Flask and MySQL – How to Fill Table Data in a Dropdown

Web applications are made user friendly by displaying appropriate data to users. The existing data can be fetched from database/tables and displayed in HTML tables, text boxes, list boxes or drop downs. Users can select options, click radio buttons, mark checkboxes for better user experience.

The basic technique of fetching data and displaying is same for all HTML form controls. For your understanding we will explain How to Fill Table Data in a Dropdown using Flask and MySQL.

This will help you in creating web applications that allow the user to select values from given options. It prevents typographical errors and maintains integrity of your database.  

Imagine an organization which is inviting applications for various job positions. For an applicant to choose a valid job, instead of typing the job position, she is made to select one from a dropdown/Combo displaying all jobs.  For this there will be a date table “jobs” that stores all the jobs and their details in the organization.   

Read along and create the sample application as described in following steps. You will understand how to Fill a Dropdown/Combo in a Flask and MySQL web application.

Step 1- Create the required Database and Table in MySQL

Create a Database “JobsDB” in MySQL and create a table called “jobs” with following columns. Fill some data by executing insert commands in MySQL.

CREATE TABLE `jobs` (
  `JobID` decimal(10,0) NOT NULL,
  `JobName` varchar(50) NOT NULL,
  `Salary` decimal(10,0) NOT NULL,
  `Commission` decimal(10,0) NOT NULL
)

INSERT into jobs (JobID, JobName, Salary, Commission) values (1, 'Sales Manager',4000,5);
INSERT into jobs (JobID, JobName, Salary, Commission) values (2, 'Sales Officer',3000,5);
INSERT into jobs (JobID, JobName, Salary, Commission) values (3, 'HR Manager',4000,0);
INSERT into jobs (JobID, JobName, Salary, Commission) values (4, 'Prod Manager',4000,0);
INSERT into jobs (JobID, JobName, Salary, Commission) values (5, 'General Manager',4000,1);

Step 2- Create Flask Web Application and a Template

Next is to create a Flask Web Application using the steps described in the post – Create Python Website- Get Started with Flask  .

Step 3- Install Flask MySQL

Now your application is running as explained in step 2. Switch to the command window and Press CTRL+C to exit Flask application prompt. Type and run the following command to install Flask MySQL.

pip install flask-mysql

Step 4- Import MySQL in Flask application.py and Create Connection

After installing Flask-mysql, add the following statements in application.py file (stored in your application folder) to import MySQL in your Flask Web Application.

from flaskext.mysql import MySQL

Now move ahead to create database connection with the MySQL database created in step 1.

mysql = MySQL()
 
# configuring MySQL for the web application
app.config['MYSQL_DATABASE_USER'] = 'root'    # default user of MySQL to be replaced with appropriate username
app.config['MYSQL_DATABASE_PASSWORD'] = '' # default passwrod of MySQL to be replaced with appropriate password
app.config['MYSQL_DATABASE_DB'] = 'jobsdb'  # Database name to be replaced with appropriate database name
app.config['MYSQL_DATABASE_HOST'] = 'localhost' # default database host of MySQL to be replaced with appropriate database host
#initialise mySQL
mysql.init_app(app)
#create connection to access data
conn = mysql.connect()

Step 5- Add route in application.py

Add the route in application.py  to fetch the data from the jobs table and send it as a set of tuples with render_template

@app.route('/') 
def index(): 
    #create a cursor
    cursor = conn.cursor() 
    #execute select statement to fetch data to be displayed in combo/dropdown
    cursor.execute('SELECT JobID,JobName FROM jobs') 
    #fetch all rows ans store as a set of tuples 
    joblist = cursor.fetchall() 
    #render template and send the set of tuples to the HTML file for displaying
    return render_template("input.html",joblist=joblist )

Step 6- Fill the dropdown using Jinja for loop

The last step will fill the data in the dropdown or combo using Jinja for loop. This is done in input.html which must be saved  in the templates folder of your application folder. This loop is preceded by the SELECT HTML tag with name of SELECT dropdown. This name is referred when POST method returns the form data filled by a user. In Jinja for loop OPTION is created using the name as the display value and id as the value part visible to the user when she clicks on the drop down.

This input.html file is passed as the argument value in render_template in the last line of the route. The following code is to be used to create the combo box/ dropdown with list of cities form the table cities.

<p style="font-family:verdana">
Choose Job Position Applying for:<SELECT name="jobid" style="font-family:verdana">
{% for j in joblist %}
	<OPTION value={{j[0]}}>{{j[1]}}</OPTION>
{% endfor %}
</SELECT>
</p>
Flask and MySQL combo example

Complete application.py

# import the flask class
from flask import Flask, session, render_template, request,make_response,redirect,flash
from flaskext.mysql import MySQL

# instatiating flask class 
app=Flask(__name__)

mysql = MySQL()
 
# configuring MySQL for the web application
app.config['MYSQL_DATABASE_USER'] = 'root'    # default user of MySQL to be replaced with appropriate username
app.config['MYSQL_DATABASE_PASSWORD'] = '' # default passwrod of MySQL to be replaced with appropriate password
app.config['MYSQL_DATABASE_DB'] = 'jobsdb'  # Database name to be replaced with appropriate database name
app.config['MYSQL_DATABASE_HOST'] = 'localhost' # default database host of MySQL to be replaced with appropriate database host
#initialise mySQL
mysql.init_app(app)
#create connection to access data
conn = mysql.connect()


@app.route('/') 

def index(): 
    #create a cursor
    cursor = conn.cursor() 
    #execute select statement to fetch data to be displayed in combo/dropdown
    cursor.execute('SELECT JobID,JobName FROM jobs') 
    #fetch all rows ans store as a set of tuples 
    joblist = cursor.fetchall() 
    #render template and send the set of tuples to the HTML file for displaying
    return render_template("input.html",joblist=joblist ) 
   

5 Comments

  1. DAKSHA DINESH SHENOY said:

    Thank You So much, Helped me a lot!!!

    October 30, 2020
    Reply
  2. Rosalía Contreras said:

    Thanks for the article! It helped me a lot!

    March 10, 2021
    Reply
  3. Alex said:

    Mate your code doesn’t even work. The drop down list shows {{j[1]}}. Did you test this out before you wrote the article?

    May 19, 2021
    Reply
    • admin said:

      Hi Alex. It’s tested code. Its working. Check these things-
      -MySQL database is created with required table ‘jobs’.
      -MySQL and Apache Servers are running.

      May 20, 2021
      Reply
  4. tesh said:

    Thanks, this also worked very well with SQL Server using pyodbc.

    July 30, 2021
    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *