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>
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 )
Thank You So much, Helped me a lot!!!
Thanks for the article! It helped me a lot!
Mate your code doesn’t even work. The drop down list shows {{j[1]}}. Did you test this out before you wrote the article?
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.
Thanks, this also worked very well with SQL Server using pyodbc.
Dear Sir,
Am very much thank full to you.
just copied your code and done some alterations accordingly in project.
That’s it………….Successfully the dropdown shown me the data from the db table
sorry man but I host site on my pc so when I run your code I have an error message that tells me that my computer can’t connect to localhost. I managed to install all the mods. Could you help me?
Follow the steps given here https://csveda.com/create-python-website-get-started-with-flask/ to initiate localhost. If the port needed by FLASK localhost is being used by some other application like XAMPP or WAMP then your need to close that instance. You can also check the error you get in the flask command window.
Thank you!! This worked like a charm for my project.