Advertisement
  1. Code
  2. Coding Fundamentals
  3. Databases & SQL

Creating a Web App From Scratch Using Python Flask and MySQL

Scroll to top
This post is part of a series called Creating a Web App From Scratch Using Python Flask and MySQL.
Creating a Web App From Scratch Using Python Flask and MySQL: Part 2

In this series, we'll be using Python, Flask, and MySQL to create a simple web application from scratch. It will be a simple bucket list application where users can register, sign in, and create their bucket list.

This tutorial assumes that you have some basic knowledge of the Python programming language. We'll be using Flask, a Python web application framework, to create our application, with MySQL as the back end.

Introduction to Python Flask

Flask is a Python framework for creating web applications. From the official site:

Flask is a microframework for Python based on Werkzeug, Jinja 2 and good intentions.

When we think about Python, the de facto framework that comes to our mind is the Django framework. But from a Python beginner's perspective, Flask is easier to get started with, when compared to Django.

1. Setting Up Flask

Setting up Flask is pretty simple and quick. With pip package manager, all we need to do is:

1
pip install flask

Once you're done with installing Flask, create a folder called FlaskApp. Navigate to the FlaskApp folder and create a file called app.py. Import the flask module and create an app using Flask as shown:

1
from flask import Flask
2
app = Flask(__name__)

Now define the basic route / and its corresponding request handler:

1
@app.route("/")
2
def main():
3
    return "Welcome!"

Next, check if the executed file is the main program and run the app:

1
if __name__ == "__main__":
2
    app.run()

Save the changes and execute app.py:

1
python app.py

Point your browser to http://localhost:5000/ and you should have the welcome message.

2. Creating a Home Page

First, when the application runs, we should show a home page with the latest bucket list items added by users. So let's add our home page to our application folder.

Flask looks for template files inside the templates folder. So navigate to the FlaskApp folder and create a folder called templates. Inside templates, create a file called index.html. Open up index.html and add the following HTML:

1
<!DOCTYPE html>
2
<html lang="en">
3
    <head>
4
		    
5
		<title>Python Flask Bucket List App</title>
6
		    
7
		<link
8
			href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css"
9
			rel="stylesheet"
10
			integrity="sha384-1BmE4kWBq78iYhFldvKuhfTAU6auU8tT94WrHftjDbrCEXSU1oBoqyl2QvZ6jIW3"
11
			crossorigin="anonymous"
12
		/>
13
	</head>
14
15
	<body>
16
		    
17
		<div class="container">
18
			        
19
			<header class="header">
20
				            
21
				<nav
22
					class="d-flex flex-wrap justify-content-center py-3 mb-4 border-bottom"
23
				>
24
					<a
25
						href="/"
26
						class="d-flex align-items-center mb-3 mb-md-0 me-md-auto text-dark text-decoration-none"
27
					>
28
						<span class="fs-4">Python Flask App</span>
29
					</a>
30
31
					<ul class="nav nav-pills">
32
						<li class="nav-item">
33
							<a href="/" class="nav-link active" aria-current="page">Home</a>
34
						</li>
35
						<li class="nav-item">
36
							<a href="/signup" class="nav-link">Signup</a>
37
						</li>
38
					</ul>
39
					                        
40
				</nav>
41
				                     
42
			</header>
43
			<div class="p-5 mb-4 bg-light rounded-3">
44
				<div class="container-fluid py-5 text-center">
45
					<h1 class="display-5 fw-bold">Bucket List App</h1>
46
					       
47
					<p>
48
						<a class="btn btn-lg btn-success" href="signup" role="button"
49
							>Sign up today</a
50
						>
51
						            
52
					</p>
53
				</div>
54
			</div>
55
56
			        
57
			<div class="row marketing">
58
				            
59
				<div class="col-lg-6">
60
					                
61
					<h4>Bucket List</h4>
62
					                
63
					<p>
64
						Donec id elit non mi porta gravida at eget metus. Maecenas faucibus
65
						mollis interdum.
66
					</p>
67
					                                 
68
					<h4>Bucket List</h4>
69
					                
70
					<p>
71
						Morbi leo risus, porta ac consectetur ac, vestibulum at eros. Cras
72
						mattis consectetur purus sit amet fermentum.
73
					</p>
74
75
					                
76
					<h4>Bucket List</h4>
77
					                
78
					<p>Maecenas sed diam eget risus varius blandit sit amet non magna.</p>
79
80
					                
81
					<h4>Bucket List</h4>
82
					                
83
					<p>
84
						Donec id elit non mi porta gravida at eget metus. Maecenas faucibus
85
						mollis interdum.
86
					</p>
87
88
					                
89
					<h4>Bucket List</h4>
90
					                
91
					<p>
92
						Morbi leo risus, porta ac consectetur ac, vestibulum at eros. Cras
93
						mattis consectetur purus sit amet fermentum.
94
					</p>
95
96
					                
97
					<h4>Bucket List</h4>
98
					                
99
					<p>Maecenas sed diam eget risus varius blandit sit amet non magna.</p>
100
					            
101
				</div>
102
				        
103
			</div>
104
105
			        
106
			<footer class="footer">
107
				            
108
				<p>&copy; Company 2022</p>
109
				        
110
			</footer>
111
112
			    
113
		</div>
114
	</body>
115
</html>

Open up app.py and import render_template, which we'll use to render the template files.

1
from flask import Flask, render_template

Modify the main method to return the rendered template file.

1
def main():
2
    return render_template('index.html')

Save the changes and restart the server. Point your browser to http://localhost:5000/ and you should have the below screen:

Flask App homepageFlask App homepageFlask App homepage

3. Creating a Signup Page

Step 1

Setting Up the Database

We'll be using MySQL as the back end. So log into MySQL from the command line, or if you prefer a GUI like MySQL Workbench, you can use that too. First, create a database called BucketList. From the command line:

1
mysql -u <username> -p

Enter the required password and, when logged in, execute the following command to create the database:

1
CREATE DATABASE BucketList;

Once the database has been created, create a table called tbl_user as shown:

1
CREATE TABLE `BucketList`.`tbl_user` (
2
  `user_id` BIGINT NULL AUTO_INCREMENT,
3
  `user_name` VARCHAR(45) NULL,
4
  `user_username` VARCHAR(45) NULL,
5
  `user_password` VARCHAR(45) NULL,
6
  PRIMARY KEY (`user_id`));

We'll be using Stored procedures for our Python application to interact with the MySQL database. So, once the table tbl_user has been created, create a stored procedure called sp_createUser to sign up a user.

When creating a stored procedure to create a user in the tbl_user table, first we need to check if a user with the same username already exists. If it exists, we need to throw an error to the user, otherwise we'll create the user in the user table. Here is how the stored procedure sp_createUser would look:

1
DELIMITER $$
2
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_createUser`(
3
    IN p_name VARCHAR(20),
4
    IN p_username VARCHAR(20),
5
    IN p_password VARCHAR(20)
6
)
7
BEGIN
8
    if ( select exists (select 1 from tbl_user where user_username = p_username) ) THEN
9
     
10
        select 'Username Exists !!';
11
     
12
    ELSE
13
     
14
        insert into tbl_user
15
        (
16
            user_name,
17
            user_username,
18
            user_password
19
        )
20
        values
21
        (
22
            p_name,
23
            p_username,
24
            p_password
25
        );
26
     
27
    END IF;
28
END$$
29
DELIMITER ;

Step 2

Create a Signup Interface

Navigate to the FlaskApp/templates directory and create an HTML file called signup.html. Add the following HTML code to signup.html:

1
<!DOCTYPE html>
2
<html lang="en">
3
    <head>
4
		    
5
		<title>Python Flask Bucket List App - Signup</title>
6
		    
7
		<link
8
			href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css"
9
			rel="stylesheet"
10
			integrity="sha384-1BmE4kWBq78iYhFldvKuhfTAU6auU8tT94WrHftjDbrCEXSU1oBoqyl2QvZ6jIW3"
11
			crossorigin="anonymous"
12
		/>
13
		<link href="../static/signup.css" rel="stylesheet" />
14
	</head>
15
16
	<body>
17
		    
18
		<div class="container">
19
			        
20
			<div class="header">
21
				            
22
				<nav
23
					class="d-flex flex-wrap justify-content-center py-3 mb-4 border-bottom"
24
				>
25
					<a
26
						href="/"
27
						class="d-flex align-items-center mb-3 mb-md-0 me-md-auto text-dark text-decoration-none"
28
					>
29
						<span class="fs-4">Python Flask App</span>
30
					</a>
31
32
					<ul class="nav nav-pills">
33
						<li class="nav-item">
34
							<a href="/" class="nav-link">Home</a>
35
						</li>
36
						<li class="nav-item">
37
							<a href="/signup" class="nav-link active" aria-current="page"
38
								>Signup</a
39
							>
40
						</li>
41
					</ul>
42
					                        
43
				</nav>
44
				        
45
			</div>
46
			<div class="p-5 mb-4 bg-light rounded-3">
47
				<div class="container-fluid py-5">
48
					<h1 class="display-5 fw-bold text-center">Bucket List App</h1>
49
					<form id="form-signin" action="/api/signup" method="POST">
50
						        <label for="inputName" class="sr-only">Name</label>
51
						        <input
52
							type="name"
53
							name="inputName"
54
							id="inputName"
55
							class="form-control"
56
							placeholder="Name"
57
							required
58
							autofocus
59
						/>
60
						        <label for="inputEmail" class="sr-only"
61
							>Email address</label
62
						>
63
						        <input
64
							type="email"
65
							name="inputEmail"
66
							id="inputEmail"
67
							class="form-control"
68
							placeholder="Email address"
69
							required
70
							autofocus
71
						/>
72
						        <label for="inputPassword" class="sr-only">Password</label>
73
						        <input
74
							type="password"
75
							name="inputPassword"
76
							id="inputPassword"
77
							class="form-control"
78
							placeholder="Password"
79
							required
80
						/>
81
						                  <button
82
							class="btn btn-lg btn-primary btn-block"
83
							type="submit"
84
						>
85
							Sign up
86
						</button>
87
						      
88
					</form>
89
				</div>
90
			</div>
91
			        
92
			<footer class="footer">
93
				            
94
				<p>&copy; Company 2022</p>
95
				        
96
			</footer>
97
98
			    
99
		</div>
100
		<script>
101
			document
102
				.querySelector("#form-signin")
103
				.addEventListener("submit", async function (e) {
104
					e.preventDefault();
105
					console.log(new URLSearchParams(new FormData(e.target)).toString());
106
					const res = await fetch("/api/signup", {
107
						body: new URLSearchParams(new FormData(e.target)).toString(),
108
						method: "POST",
109
						headers: {
110
							"Content-Type": "application/x-www-form-urlencoded",
111
						},
112
					});
113
					console.log(await res.json());
114
				});
115
		</script>
116
	</body>
117
</html>

Also add the following CSS as signup.css to the static folder inside FlaskApp.

1
body {
2
      padding-top: 40px;
3
	  padding-bottom: 40px;
4
}
5
6
#form-signin {
7
	  max-width: 330px;
8
	  padding: 15px;
9
	  margin: 0 auto;
10
}
11
#form-signin .form-signin-heading,
12
#form-signin .checkbox {
13
	  margin-bottom: 10px;
14
}
15
#form-signin .checkbox {
16
	  font-weight: normal;
17
}
18
#form-signin .form-control {
19
	  position: relative;
20
	  height: auto;
21
	  -webkit-box-sizing: border-box;
22
	     -moz-box-sizing: border-box;
23
	          box-sizing: border-box;
24
	  padding: 10px;
25
	  font-size: 16px;
26
}
27
#form-signin .form-control:focus {
28
	  z-index: 2;
29
}
30
#form-signin input[type="email"] {
31
	  margin-bottom: -1px;
32
	  border-bottom-right-radius: 0;
33
	  border-bottom-left-radius: 0;
34
}
35
#form-signin input[type="password"] {
36
	  margin-bottom: 10px;
37
	  border-top-left-radius: 0;
38
	  border-top-right-radius: 0;
39
}

In app.py, add another method called signup to render the signup page once a request comes to /signup:

1
@app.route('/signup')
2
def signup():
3
    return render_template('signup.html')

Save the changes and restart the server. Click on the Sign Up button on the home page and you should have the signup page as shown:

Flask App signup pageFlask App signup pageFlask App signup page

Step 3

Implement a Signup Method

Next, we need a server-side method for the UI to interact with the MySQL database. So navigate to FlaskApp and open app.py. Create a new method called signUp and also add a route /api/signup. Here is how it looks:

1
@app.route('/api/signup',methods=['POST'])
2
def signUp():
3
    # create user code will be here !!

We'll be using AJAX to post our signup data to the signup method, so we need to specify the method in the route definition.

In order to read the posted values, we need to import request from Flask.

1
from flask import Flask, render_template, request

Using request, we can read the posted values as shown below:

1
@app.route('/api/signUp',methods=['POST'])
2
def signUp():
3
    # read the posted values from the UI

4
    _name = request.form['inputName']
5
    _email = request.form['inputEmail']
6
    _password = request.form['inputPassword']

Once the values are read, we'll simply check if they are valid, and for the time being let's just return a simple message:

1
@app.route('/api/signUp',methods=['POST'])
2
def signUp():
3
    # read the posted values from the UI
4
    _name = request.form['inputName']
5
    _email = request.form['inputEmail']
6
    _password = request.form['inputPassword']
7
8
    # validate the received values
9
    if _name and _email and _password:
10
        return json.dumps({'html':'<span>All fields good !!</span>'})
11
    else:
12
        return json.dumps({'html':'<span>Enter the required fields</span>'})

Also import json from Flask, since we are using it in the above code to return json data.

1
from flask import Flask, render_template, json, request

Step 4

Create a Signup Request

We'll be using the fetch() API to send the signup request to the Python method. So let's attach the signup button click event as shown:

1
document.querySelector('#form-signin').addEventListener("submit", async function (e) {
2
    e.preventDefault()
3
    console.log(new URLSearchParams(new FormData(e.target)).toString())
4
    const res = await fetch("/api/signup", {
5
        body: new URLSearchParams(new FormData(e.target)).toString(),
6
        method: "POST",
7
        headers: {
8
            'Content-Type': 'application/x-www-form-urlencoded',
9
        }
10
    })
11
    console.log(await res.json())
12
});

You should put this script inside a <script> tag at the bottom of the signup document. Save all the changes and restart the server. From the Sign Up page, fill in the details and click Sign Up. Check the browser console and you should have the below message:

1
{"html": "<span>All fields good !!</span>"}

Step 5

Call the MySQL Stored Procedure

Once we have the name, email address, and password, we can simply call the MySQL stored procedure to create the new user.

To connect with MySQL, we'll be using Flask-MySQL, which is a Flask extension. In order to get started with Flask-MySQL, install it using pip package manager:

1
pip install flask-mysql

Import MySQL inside app.py:

1
from flask.ext.mysql import MySQL

Earlier we defined our app as shown:

1
app = Flask(__name__)

Along with that, include the following MySQL configurations:

1
mysql = MySQL()
2
3
4
# MySQL configurations

5
app.config['MYSQL_DATABASE_USER'] = 'jay'
6
app.config['MYSQL_DATABASE_PASSWORD'] = 'jay'
7
app.config['MYSQL_DATABASE_DB'] = 'BucketList'
8
app.config['MYSQL_DATABASE_HOST'] = 'localhost'
9
mysql.init_app(app)

First, let's create the MySQL connection:

1
conn = mysql.connect()

Once the connection is created, we'll require a cursor to query our stored procedure. So, using conn connection, create a cursor.

1
cursor = conn.cursor()

Before calling the create user stored procedure, let's make our password salted using a helper provided by Werkzeug. Import the module into app.py:

1
from werkzeug import generate_password_hash, check_password_hash

Use the salting module to create the hashed password.

1
_hashed_password = generate_password_hash(_password)

Now, let's call the procedure sp_createUser:

1
cursor.callproc('sp_createUser',(_name,_email,_hashed_password))

If the procedure is executed successfully, then we'll commit the changes and return the success message.

1
data = cursor.fetchall()
2
3
4
if len(data) is 0:
5
    conn.commit()
6
    return json.dumps({'message':'User created successfully !'})
7
else:
8
    return json.dumps({'error':str(data[0])})

Save the changes and restart the server. Go to the signup page and enter the name, email address, and password, and click the Sign Up button. On successful user creation, you'll see a message in your browser console.

1
{"message": "User created successfully !"}

Wrapping It Up

In this tutorial, we saw how to get started with creating a web application using Python Flask, MySQL, and the Flask-MySQL extension. We created and designed the database tables and a stored procedure, and we implemented the signup functionality. In the next tutorial, we'll take this series to the next level by implementing sign-in functionality and some other features.

Source code for this tutorial is available on GitHub.

This post has been updated with contributions from Jacob Jackson. Jacob is a web developer, a technical writer, a freelancer, and an open-source contributor.

Advertisement
Did you find this post useful?
Want a weekly email summary?
Subscribe below and we’ll send you a weekly email summary of all new Code tutorials. Never miss out on learning about the next big thing.
Advertisement
Looking for something to help kick start your next project?
Envato Market has a range of items for sale to help get you started.