Skip to main content

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

Popular posts from this blog

Spring boot video streaming example-HTML5

Hello everyone, Today we will learn how to stream MP4 video using Spring. You can download the source code of this sample application from our GitHub repository. Download Link is provided at the end of this post.
User Interface

Project Structure


Home Controller
@Controller
public class HomeController {
 @Autowired
 private MyResourceHttpRequestHandler handler;
 private final static File MP4_FILE = new File("D:\\videofiles\\video1.mp4");
 // supports byte-range requests
 @GetMapping("/index")
 public String home() {
  return "index";
 }
 // supports byte-range requests
 @GetMapping("/byterange")
 public void byterange( HttpServletRequest request, HttpServletResponse response)
   throws ServletException, IOException {
  request.setAttribute(MyResourceHttpRequestHandler.ATTR_FILE, MP4_FILE);
  handler.handleRequest(request, response);
 }}
Spring Boot 
@SpringBootApplication
@ComponentScan({ "com" })
public class KnowledgefactorydemoApplication {
 public static vo…

DataTable-Pagination example with Spring boot,jQuery and ajax free download

This article shows you DataTable-Pagination example with Spring boot, jQuery and ajax. 

Following technologies stack being used:
Spring Boot 2.1.1.RELEASESpring 5.1.3.RELEASE Maven 3DatatablejQueryFree Markerjavascriptajax Project Structure
View(index.ftl) <!DOCTYPE html> <html> <head> <meta charset="utf-8"> <meta http-equiv="X-UA-Compatible" content="IE=edge"> <title>Knf|Pagination-Datatables</title> <!-- Tell the browser to be responsive to screen width --> <meta name="viewport" content="width=device-width, initial-scale=1"> <link rel="stylesheet" href="../../css/jquery.dataTables.min.css"> <body>  <section class="content">   <div class="container-fluid">    <table id="example" class="display" style="width: 100%">     <thead>      <tr>       <th>First name</th>       <th>La…

9 Best Job Posting Sites for Employee 2020-2022

One of the most common ways today’s job seekers uncover employment opportunities are by using online sources. There are hundreds of job boards, both generic and niche, as well as aggregators, social media channels, networking groups, and staffing company websites to choose from. The good news is they provide plenty of listings. The bad news is you can be easily overwhelmed by so many options. Here, knowledge factory offers 9 of what is generally considered the best job search websites around. There are many others, of course, but this list of best job search websites should give you a good starting point for your next job hunt: 1. LinkedIn
This top networking site allows you to find jobs through your extended network. User profiles serve as resumes. You can easily share career-related content and solicit or supply recommendations. Additionally, you can join groups, participate in conversations, and follow companies you find interesting and relevant to your job search. Premium pai…