Creating a Web App From Scratch Using Python Flask and MySQL

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. We'll be using Flask, a Python web application framework, to create our application, with MySQL as the back end.

Advertisement Advertisement Advertisement Advertisement Advertisement Feb 26, 2022 • 9 min read This post is part of a series called Creating a Web App From Scratch Using Python Flask and MySQL.

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:

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:

from flask import Flask 
app = Flask(__name__)

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

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

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

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

Save the changes and execute app.py:

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:

 lang="en"> 
 Python Flask Bucket List App 
href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" 
rel="stylesheet" 
integrity="sha384-1BmE4kWBq78iYhFldvKuhfTAU6auU8tT94WrHftjDbrCEXSU1oBoqyl2QvZ6jIW3" 
crossorigin="anonymous" 
 class="container"> 
 class="header"> 
class="d-flex flex-wrap justify-content-center py-3 mb-4 border-bottom" 
href="/" 
class="d-flex align-items-center mb-3 mb-md-0 me-md-auto text-dark text-decoration-none" 
 class="fs-4">Python Flask App 
 class="nav nav-pills"> 
 class="nav-item"> 
 href="/" class="nav-link active" aria-current="page">Home 
 class="nav-item"> 
 href="/signup" class="nav-link">Signup 
 class="p-5 mb-4 bg-light rounded-3"> 
 class="container-fluid py-5 text-center"> 
 class="display-5 fw-bold">Bucket List App 
 class="btn btn-lg btn-success" href="signup" role="button" 
>Sign up today 
 class="row marketing"> 
 class="col-lg-6"> 
 Bucket List 
Donec id elit non mi porta gravida at eget metus. Maecenas faucibus
mollis interdum.
 Bucket List 
Morbi leo risus, porta ac consectetur ac, vestibulum at eros. Cras
mattis consectetur purus sit amet fermentum.
 Bucket List 
 Maecenas sed diam eget risus varius blandit sit amet non magna. 
 Bucket List 
Donec id elit non mi porta gravida at eget metus. Maecenas faucibus
mollis interdum.
 Bucket List 
Morbi leo risus, porta ac consectetur ac, vestibulum at eros. Cras
mattis consectetur purus sit amet fermentum.
 Bucket List 
 Maecenas sed diam eget risus varius blandit sit amet non magna. 
 class="footer"> 
 © Company 2022

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

from flask import Flask, render_template

Modify the main method to return the rendered template file.

def main(): 
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 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:

mysql -u -p

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

CREATE DATABASE BucketList;

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

CREATE TABLE `BucketList`.`tbl_user` ( 
`user_id` BIGINT NULL AUTO_INCREMENT, 
`user_name` VARCHAR(45) NULL, 
`user_username` VARCHAR(45) NULL, 
`user_password` VARCHAR(45) NULL, 
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:

DELIMITER $$ 
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_createUser`( 
IN p_name VARCHAR(20), 
IN p_username VARCHAR(20), 
IN p_password VARCHAR(20) 
BEGIN 
if ( select exists (select 1 from tbl_user where user_username = p_username) ) THEN 
select 'Username Exists !!';