Python - Simple CRUD Web App with Python ,Flask and Mysql

In this article, we will learn how to build Web Application with FLASK and MYSQL that have the capabilities to create, read, update, and delete data from the MYSQL database.
Technologies used:
  • Python 3
  • Flask library
  • pymysql library
  • flask-sqlalchemy library
  • Bootstrap
  • Javacsript
Please go through the following steps in order to implement Python web application CRUD example using Flask and MySQL:

Step 1. Create MySQL database table – 'employee' with the following structure.
CREATE TABLE IF NOT EXISTS `employee` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `uname` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `email` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `salary` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=2343 ;


Step 2.Create the below app.py script(py is the extension to indicate Python script) where we import the flask module.
app.py:
from flask import Flask
app = Flask(__name__)

Step 3.create the below db_config.py Python script o setup the MySQL database configurations for connecting to the database.
db_config.py:
from app import app
from flaskext.mysql import MySQL
mysql = MySQL()
# MySQL configurations
app.config['MYSQL_DATABASE_USER'] = 'root'
app.config['MYSQL_DATABASE_PASSWORD'] = ''
app.config['MYSQL_DATABASE_DB'] = 'knf'
app.config['MYSQL_DATABASE_HOST'] = 'localhost'
mysql.init_app(app)

Step 4.Next we need 'webcontroller.py' script. This script is the perfect instance of Python Web API CRUD Example using Flask and MySQL. It defines all functions for performing CRUD operations.
import pymysql
from app import app
from db_config import mysql
from flask import render_template
from flask import flash, request

@app.route('/', methods=['GET'])
def add_employee():
   return render_template("view.html",datas=fetchListOfEmployees())
   
@app.route("/add", methods=["POST"])
def home():
 try:
   _id=request.form.get('id')
   _name=request.form.get('uname')
   _email=request.form.get('email')
   _salary=request.form.get('salary')
   sql = "INSERT INTO employee(id, uname, email,salary) VALUES(%s, %s, %s ,  %s)"
   data = (_id, _name, _email,_salary)
   conn = mysql.connect()
   cursor = conn.cursor()
   cursor.execute(sql, data)
   conn.commit()
   return render_template('view.html',datas=fetchListOfEmployees())
 except Exception as e:
   print(e)
 finally:
   cursor.close() 
   conn.close()

@app.route('/deleteEmployee', methods=['POST'])
def deleteEmployee():
 try:
   _id=request.form.get('id')
   print(_id)
   conn = mysql.connect()
   cursor = conn.cursor()
   cursor.execute("DELETE FROM employee WHERE id=%s", (_id))
   conn.commit()
   return render_template('view.html',datas=fetchListOfEmployees())
 except Exception as e:
  print(e)
 finally:
  cursor.close() 
  conn.close()
def fetchListOfEmployees():
 try:
   conn = mysql.connect()
   cursor = conn.cursor(pymysql.cursors.DictCursor)
   cursor.execute("SELECT * FROM employee")
   rows = cursor.fetchall()
   return rows
 except Exception as e:
   print(e)
 finally:
   cursor.close() 
   conn.close()

@app.route('/employeeUpdate', methods=['POST'])
def update_employee():
 try:
   _id=request.form.get('id')
   _name=request.form.get('uname')
   _email=request.form.get('email')
   _salary=request.form.get('salary')
  
   sql = "UPDATE employee SET uname=%s, email=%s, salary=%s WHERE id=%s"
   data = (_name, _email, _salary, _id,)
   conn = mysql.connect()
   cursor = conn.cursor()
   cursor.execute(sql, data)
   conn.commit()
   return render_template('view.html',datas=fetchListOfEmployees())
 except Exception as e:
  print(e)
 finally:
  cursor.close() 
  conn.close()

@app.errorhandler(404)
def page_not_found(e):
    return render_template("404.html")
@app.errorhandler(500)
def page_not_found(e):
    return render_template("500.html")

if __name__ == "__main__":
    app.run(host='0.0.0.0',port='8080')

Step 5. A HTML file 'view.html'
view.html:
<html>
<head>
  <title>User Registration System</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">
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script>
</head>
  <body>
  <div class="container">
  <h2 id="registerEmp">Register New Employee</h2>
  <h2 hidden id="updateEmployee">Update Employee</h2>
    <form id="actionForm"name="actionForm"method="POST" action="/add">
    <div class="col-xs-4">
  ID:
      <input  onkeypress="return isNumber(event)" class="form-control" type="text" name="id">
    </div>
     <div class="col-xs-4">
  User Name
  <input  class="form-control" type="text" name="uname">
</div>
<div class="col-xs-4">
  Email
  <input  class="form-control" type="text" name="email">
</div>
  <div class="col-xs-4">
  Salary
  <input  onkeypress="return isNumber(event)" class="form-control" type="text" name="salary">
</div>
   </br></br></br></br></br></br>
      <center><input id="add-emp"class="btn btn-primary" type="submit" value="Register">
      <input style="display:none" id="update-user" class="btn btn-primary" type="submit" value="Update">
      </center>
    </form>
    <h2>List of Registered Employee</h2>
    <table class="table">
    <thead>
    <tr>
      <th>User Name</th>
      <th>Email</th>
      <th>Salary</th>
    </tr>
  </thead>
  <tbody>
{% for item in datas %}
<tr>
    <td>{{ item.uname}}</td>
    <td>{{ item.email}}</td>
    <td>{{ item.salary}}</td>
    <td><input class="btn btn-warning" type="submit" value="Edit" 
    onclick="update('{{ item.id}}','{{ item.uname}}','{{ item.email}}','{{ item.salary}}')"></td>
    <td> <form method="POST" action="/deleteEmployee">
        <input type="text" hidden value="{{item.id}}" name="id"> 
    <input  class="btn btn-danger" type="submit" value="Delete"></form></td>
</tr>
{% endfor %}
</tbody>
</table>
</div></div>
  </body>
  <script>
  function isNumber(evt) {
    evt = (evt) ? evt : window.event;
    var charCode = (evt.which) ? evt.which : evt.keyCode;
    if (charCode > 31 && (charCode < 48 || charCode > 57)) {
        return false;
    }
    return true;
}
function update(id,name,email,salary) {
   document.getElementById("add-emp").style.display = "none"
   document.getElementById("registerEmp").style.display = "none"
   document.getElementById("update-user").style.display = "block"
   document.getElementById("updateEmployee").style.display = "block"
   document.getElementsByName('id')[0].value = id;
   document.getElementsByName('uname')[0].value = name;
   document.getElementsByName('email')[0].value = email;
   document.getElementsByName('salary')[0].value = salary;
   document.getElementById('actionForm').action = "/employeeUpdate";
}
  </script>
</html>

Step 6. 
404.html:
<html>
<head>
<title> 404 Page Not Found </title>
<link href="https://fonts.googleapis.com/css?family=Roboto:700" rel="stylesheet">
<style>
h1{
font-size:80px;
font-weight:800;
text-align:center;
font-family: 'Roboto', sans-serif;
}
h2
{
font-size:25px;
text-align:center;
font-family: 'Roboto', sans-serif;
margin-top:-40px;
}
p{
text-align:center;
font-family: 'Roboto', sans-serif;
font-size:12px;
}
.container
{
width:300px;
margin: 0 auto;
margin-top:15%;
}
</style>
</head>
<body>
<div class="container">
<h1>404</h1>
<h2>Page Not Found</h2>
<p>The Page you are looking for doesn't exist or an other error occured. Go to <a href="https://www.knowledgefactory.net/">Home Page.</a></p>
</div>
</body>
</html>

Step 7. 
500.html:
<html>
<head>
<title> 500 Internal Server </title>
<link href="https://fonts.googleapis.com/css?family=Roboto:700" rel="stylesheet">
<style>
h1{
font-size:80px;
font-weight:800;
text-align:center;
font-family: 'Roboto', sans-serif;
}
h2
{
font-size:25px;
text-align:center;
font-family: 'Roboto', sans-serif;
margin-top:-40px;
}
p{
text-align:center;
font-family: 'Roboto', sans-serif;
font-size:12px;
}
.container
{
width:300px;
margin: 0 auto;
margin-top:15%;
}
</style>
</head>
<body>
<div class="container">
<h1>500</h1>
<h2>Internal Server Error</h2>
<p>Please check the logs <a href="https://www.knowledgefactory.net/">Home Page.</a></p>
</div>
</body>
</html>

Step 8. Run the Application
Now navigate to the directory and execute the command python webcontroller.py as shown in the below image, your server will start on 8080.



Comments