Building a Python Flask application — Tutorial 04

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 pymysql

Step 2: Import MySQLdb package.

import pymysql

Step 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')

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store