Building a Python Flask application — Tutorial 04
5 min readDec 31, 2018
Hi all, welcome to the fourth tutorial of “Building a Python Flask application” tutorial series. In this tutorial, I will explain how to insert and retrieve data from the MySQL database.
I will use PyChram IDE and MySQL workbench in here.
Hope you are familiar with the above IDEs.
Ok, Let’s Start
Step 1: Create User Interfaces.
- Create the home page as “home.html”.
<!DOCTYPE html>
<html lang="en">
<head>
<title>Home Page</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.3.7/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
<link rel="stylesheet" href="../static/index.css">
<script src="../static/index.js"></script>
</head><body id="body">
<div class="wrapper">
<form class="form-signin" method="get" action="/login">
<button class="btn btn-lg btn-primary btn-block" type="submit" name="login" >Login</button>
</form>
<form class="form-signin" method="get" action="/register">
<button class="btn btn-lg btn-primary btn-block" type="submit" name="register">Register</button>
</form>
</div>
</body>
</html>
- Create the login page as “login.html”.
<!DOCTYPE html>
<html lang="en">
<head>
<title>Bootstrap Example</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.3.7/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
</head>
<body><div class="container">
<h2>Login form</h2>
<form class="form-horizontal" method="post" action="/login">
<div class="form-group">
<label class="control-label col-sm-2" for="email">Email:</label>
<div class="col-sm-10">
<input type="email" class="form-control" id="email" placeholder="Enter email" name="email">
</div>
</div>
<div class="form-group">
<label class="control-label col-sm-2" for="pwd">Password:</label>
<div class="col-sm-10">
<input type="password" class="form-control" id="pwd" placeholder="Enter password" name="pwd">
</div>
</div>
<div class="form-group">
<div class="col-sm-offset-2 col-sm-10">
<button type="submit" class="btn btn-default">Submit</button>
</div>
</div></form></div></body>
</html>
- Create the user registration page as “register.html”.
<!DOCTYPE html>
<html lang="en">
<head>
<title>Register Form</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.3.7/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
</head>
<body>
<div class="container">
<h2>Register form</h2>
<form class="form-horizontal" method="post" action="/register">
<div class="form-group">
<label class="control-label col-sm-2">First Name:</label>
<div class="col-sm-10">
<input type="text" class="form-control" id="fname" placeholder="Enter first name" name="fname">
</div>
</div>
<div class="form-group">
<label class="control-label col-sm-2">Last Name:</label>
<div class="col-sm-10">
<input type="text" class="form-control" id="lname" placeholder="Enter last name" name="lname">
</div>
</div>
<div class="form-group">
<label class="control-label col-sm-2" for="email">Email:</label>
<div class="col-sm-10">
<input type="email" class="form-control" id="email" placeholder="Enter email" name="email">
</div>
</div>
<div class="form-group">
<label class="control-label col-sm-2">Contact Number:</label>
<div class="col-sm-10">
<input type="text" class="form-control" id="cnumber" placeholder="Enter contact number" name="cnumber">
</div>
</div>
<div class="form-group">
<label class="control-label col-sm-2" for="pwd">Password:</label>
<div class="col-sm-10">
<input type="password" class="form-control" id="pwd" placeholder="Enter password" name="pwd">
</div>
</div>
<div class="form-group">
<div class="col-sm-offset-2 col-sm-10">
<button type="submit" class="btn btn-default">Submit</button>
</div>
</div>
</form>
</div>
</body>
</html>
- Create the user profile page as “user_pofile.html”.
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>user profile</title>
</head>
<body>
{% for row in data %}
{% for First_name in row %}
<h1>{{ First_name }}</h1>
{% endfor %}
{% endfor %}
</body>
</html>Now save HTML files inside the templates folder.
If you want to link CSS and js files, save them inside a static folder and link to the HTML files.
Let’s create a table to save user details by using MySQL workbench.
Create a table named users_details and with five columns as the following structure.
OK, Let’s begin to code app.py.
Step 1: Install MySQLdb
- If you're using pip version which is lesser than 18.1, upgrade it first.
pip install --upgrade pip- Execute the following command.
pip install pymysqlStep 2: Import MySQLdb package.
import pymysqlStep 3: Add database configurations to “app.py”.
conn = pymysql.connect(host="localhost",
user="root",
passwd="",
db="test123")Step 4: Create a connect cursor variable.
cur = conn.cursor()Step 5: Create an index route to display “home.html”.
@app.route('/', methods=['GET', 'POST'])
def display_home_page():
return render_template('home.html')Step 6: Create a register route to insert user details to the database and render “register.html” page.
@app.route('/register', methods=['POST', 'GET'])
def insert_user_details():
if request.method == 'POST':
first_name = request.form['fname']
last_name = request.form['lname']
email = request.form['email']
contact_number = request.form['cnumber']
password = request.form['pwd']
if first_name and last_name and email and contact_number and password:
cur.execute("INSERT INTO test123.users_details(First_name,Last_name,Email,Contact_number,Password)"
" VALUES (%s, %s, %s, %s, %s)", (first_name, last_name, email, contact_number, password))
conn.commit()
return render_template('login.html')
else:
return render_template('register.html')Step 7: Create a login route to return “login.html” page.
@app.route('/login', methods=['POST', 'GET'])
def login():
if request.method == 'POST':
email = request.form['email']
password = request.form['pwd']
if email and password:
cur.execute("SELECT First_name FROM test123.users_details where Email=%s and Password=%s", [email, password])
data = cur.fetchall()
print(data)
return render_template('user_profile.html',data=data)
else:
return render_template('login.html')
Well done, In this tutorial you have learnt how Flask application inserts and retrieve data from MySQL database.
Good Luck!
Bye.
For code:
For previous tutorials:
