Flask — HTML Template with MySQL

5 min readApr 18, 2020

In this article, we will look into how you can render your webpage from an HTML file. and then connect your application to the MySQL DB for database operations.

If you are just starting with a flask, I would recommend the below article to understand it very easily.

Flask — Hello World

This is the first article in the Hello World of Everything series. #HelloWorldOfEverything

medium.com

The first step is to create a directory named templates, we need to store all the HTML files in this directory. Flask will automatically look for the HTML files in this directory.

I have created a file named index.html in this directory with the following code.

<!DOCTYPE html>
<html lang="en">
<head>
  <title>Flask</title>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
</head>
<body>
</body>
</html>

Currently, we don’t have anything on this page to print. we will create a flask application and then will print something on the page.

Ok, now we need to create a python file which will be our flask application file. I have created a file named application.py in our parent directory. add the following code to the file.

from flask import Flask, request, render_template
app = Flask(__name__)
@app.route("/")
def helloworld():
    return render_template('index.html', text="hello world 1")
if __name__ == "__main__":
    app.run()

So when we go to “/” in our browser, it will call helloworld() function. when we are rendering an HTML page from the flask, we need to call render_template with our HTML filename, this will look for the file in the /template directory.

In render_template, the first parameter is the file name and then we can define some variables which we will be used in the HTML page. Here I have assigned “hello world 1” to variable text.

Now let's do some modifications to our HTML file to print this variable value.

<!DOCTYPE html>
<html lang="en">
<head>
  <title>Flask</title>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
</head>
<body>
{{ text }}
</body>
</html>

You see!! when we need to render variable values from flask to HTML, we use
{{ VARIABLE_NAME }}, this will render the actual value held by that variable.

Let's hit our flask server and see if this works.
Go to your terminal and run the python application.py command

Here is the directory to give you a better picture of where all the files should be.

Now, let's move to the MySQL part. I will create a DB table that will store the text value. From the flask application, we will write to a table with the help of a form, Show all the text in the HTML list.

For the MySQL server, I am using phpMyAdmin (This was already installed in my system, but if you want to use other MySQL servers. definitely go ahead.)

This is the table.

Now we need to create an HTML form to allow users to enter text and store it in DB.

add the following form code to the HTML file which will render a form.

<form action="/add_text" method="post">
    <label for="textv">Value:</label><br>
    <input type="text" id="textv" name="textv" value=""><br>
    <input type="submit" value="Submit">
</form>

here in the action param, we are calling “/add_text” which means instead of “/” in the flask we are calling a new URL which we need to define in our flask application. but first, let's create one more file which will have all our database operations code in it.

I am creating a file named DB_Operations.py,

import pymysql
#database connection
connection = pymysql.connect(host="localhost", user="root", passwd="", database="smart_home")
cursor = connection.cursor()
#inserting data to db
def add_text(text_value):
    cursor.execute("INSERT INTO mytable(ID, text_value) VALUES (DEFAULT, %s)", (text_value))
    connection.commit()
    return 1

To connect flask with MySQL, we need to install pymysql by

pip install PyMySQL

In the above code, first, we are connecting with MySQL by passing host, user, passwd, database value. Make sure you pass correct values here otherwise there may problems in connecting to the DB.

We will now modify application.py and add a new function for add_text

from flask import Flask, request, render_template, redirect, url_for
from DB_Operations import add_text
app = Flask(__name__)
@app.route("/")
def helloworld():
    return render_template('index.html', text="hello world 1")
@app.route("/add_text", methods=["POST", "GET"])
def AddText():
    if request.method == "POST":
        text_value = request.form["textv"]
        #saving all the values to db
        add_new = add_text(text_value)
        return redirect(url_for('helloworld'))
    else:
        return render_template('index.html')
if __name__ == "__main__":
    app.run(debug=True)

Now if we go to 127.0.0.1/ we should see a form and on submit it should be able to save data to the database.

I have submitted this form with the value “next text”, let's check if it worked.

Now we will show all the data from the DB table to the HTML page in the list.

add the following code to DB_Operations.py file

def get_data():
    cursor.execute("SELECT * FROM mytable")
    rows = cursor.fetchall()    
    return rows

This will fetch all the rows from mytable and return the result.

from flask import Flask, request, render_template, redirect, url_for
from DB_Operations import add_text, get_data
app = Flask(__name__)
@app.route("/")
def helloworld():
    all_text = get_data()
    return render_template('index.html', all_text = all_text)
@app.route("/add_text", methods=["POST", "GET"])
def AddText():
    if request.method == "POST":
        text_value = request.form["textv"]
        #saving all the values to db
        add_new = add_text(text_value)
return redirect(url_for('helloworld'))
    else:
        return render_template('index.html')
if __name__ == "__main__":
    app.run(debug=True)

I have made some changes to helloworld() function. here I am calling get_data() from DB_Operations file to fetch all the rows and passing this data to index.html

<!DOCTYPE html>
<html lang="en">
    <head>
        <title>Flask</title>
        <meta charset="utf-8">
        <meta name="viewport" content="width=device-width, initial-scale=1">
        <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
    </head>
    <body style="padding: 20px;">
        <form action="/add_text" method="post">
            <label for="textv">Value:</label><br>
            <input type="text" id="textv" name="textv" value=""><br>
            <input type="submit" value="Submit">
       </form>
       <br><br><hr>
       <ul>
            {% for text in all_text %}
            <li> {{ text[1] }} </li>
            {% endfor %}
       </ul>
</body>
</html>

When we want to use python properties in HTML, we use {% PYTHON %}

This should work and should display a list of rows from the database.

I hope this article helped you with a basic understanding of using templates and databases in the flask.

If you have any questions, comment below I will answer those.

Flask — HTML Template with MySQL

Flask — Hello World

This is the first article in the Hello World of Everything series. #HelloWorldOfEverything

Written by Manoj Ahirwar