Python Flask Employee Create, read, update and delete (CRUD) using pymysql and dataTables bootstrap
#app.py
from flask import Flask, render_template, request, redirect, url_for, flash
from flaskext.mysql import MySQL
import pymysql
app = Flask(__name__)
app.secret_key = "Cairocoders-Ednalan"
mysql = MySQL()
# MySQL configurations
app.config['MYSQL_DATABASE_USER'] = 'root'
app.config['MYSQL_DATABASE_PASSWORD'] = ''
app.config['MYSQL_DATABASE_DB'] = 'testingdb'
app.config['MYSQL_DATABASE_HOST'] = 'localhost'
mysql.init_app(app)
@app.route('/')
def Index():
conn = mysql.connect()
cur = conn.cursor(pymysql.cursors.DictCursor)
cur.execute('SELECT * FROM employee')
data = cur.fetchall()
cur.close()
return render_template('index.html', employee = data)
@app.route('/add_contact', methods=['POST'])
def add_employee():
conn = mysql.connect()
cur = conn.cursor(pymysql.cursors.DictCursor)
if request.method == 'POST':
fullname = request.form['fullname']
phone = request.form['phone']
email = request.form['email']
cur.execute("INSERT INTO employee (name, email, phone) VALUES (%s,%s,%s)", (fullname, email, phone))
conn.commit()
flash('Employee Added successfully')
return redirect(url_for('Index'))
@app.route('/edit/', methods = ['POST', 'GET'])
def get_employee(id):
conn = mysql.connect()
cur = conn.cursor(pymysql.cursors.DictCursor)
cur.execute('SELECT * FROM employee WHERE id = %s', (id))
data = cur.fetchall()
cur.close()
print(data[0])
return render_template('edit.html', employee = data[0])
@app.route('/update/', methods=['POST'])
def update_employee(id):
if request.method == 'POST':
fullname = request.form['fullname']
phone = request.form['phone']
email = request.form['email']
conn = mysql.connect()
cur = conn.cursor(pymysql.cursors.DictCursor)
cur.execute("""
UPDATE employee
SET name = %s,
email = %s,
phone = %s
WHERE id = %s
""", (fullname, email, phone, id))
flash('Employee Updated Successfully')
conn.commit()
return redirect(url_for('Index'))
@app.route('/delete/', methods = ['POST','GET'])
def delete_employee(id):
conn = mysql.connect()
cur = conn.cursor(pymysql.cursors.DictCursor)
cur.execute('DELETE FROM employee WHERE id = {0}'.format(id))
conn.commit()
flash('Employee Removed Successfully')
return redirect(url_for('Index'))
# starting the app
if __name__ == "__main__":
app.run(port=3000, debug=True)
//index.html
{% extends "layout.html" %}
{% block body %}
<div class="row"><h3>Employee</h3></div>
<div class="row">
<div class="col-md-4">
{% with messages = get_flashed_messages() %}
{% if messages %}
{% for message in messages %}
<div class="alert alert-success alert-dismissible fade show" role="alert">
{{ message }}
<button type="button" class="close" data-dismiss="alert" aria-label="Close">
<span aria-hidden="true">×</span>
</button>
</div>
{% endfor %}
{% endif %}
{% endwith %}
<div class="card card-body">
<form action="{{url_for('add_employee')}}" method="POST">
<div class="form-group">
<input type="text" class="form-control" name="fullname" placeholder="FullName">
</div>
<div class="form-group">
<input type="text" class="form-control" name="email" placeholder="Email">
</div>
<div class="form-group">
<input type="text" class="form-control" name="phone" placeholder="Phone">
</div>
<button class="btn btn-primary btn-block">
Save
</button>
</form>
</div>
</div>
<div class="col-md-8">
<table id="example" class="table table-striped table-bordered" style="width:100%">
<thead>
<tr>
<td>ID</td>
<td>fullname</td>
<td>email</td>
<td>phone</td>
<td>Action</td>
</tr>
</thead>
<tbody>
{% for data in employee %}
<tr>
<td>{{data.id}}</td>
<td>{{data.name}}</td>
<td>{{data.email}}</td>
<td>{{data.phone}}</td>
<td width="130">
<a href="/edit/{{data.id}}" class="btn btn-secondary btn-sm">edit</a>
<a href="/delete/{{data.id}}" class="btn btn-danger btn-delete btn-sm">delete</a>
</td>
</tr>
{% endfor %}
</tbody>
</table>
</div>
</div>
</div>
{% endblock %}
//layout.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Python Flask Employee Create, read, update and delete (CRUD) using pymysql</title>
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css" rel="stylesheet" id="bootstrap-css">
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/js/bootstrap.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
<script src="https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.10.16/js/dataTables.bootstrap4.min.js"></script>
</head>
<body>
<!-- NAVIGATION -->
<nav class="navbar navbar-dark bg-dark">
<a class="navbar-brand" href="/">Python Flask Employee Create, read, update and delete (CRUD) using pymysql and dataTables bootstrap</a>
</nav>
<div class="container pt-4">
{% block body %}
{% endblock %}
</div>
<script>
const btnDelete= document.querySelectorAll('.btn-delete');
if(btnDelete) {
const btnArray = Array.from(btnDelete);
btnArray.forEach((btn) => {
btn.addEventListener('click', (e) => {
if(!confirm('Are you sure you want to delete it?')){
e.preventDefault();
}
});
})
}
$(document).ready(function() {
$('#example').DataTable({
"aLengthMenu": [[3, 5, 10, 25, -1], [3, 5, 10, 25, "All"]],
"iDisplayLength": 3
}
);
} );
</script>
</body>
</html>
//templates/edit.html
{% extends "layout.html" %}
{% block body %}
<div class="row">
<div class="col-md-4 offset-md-4">
<div class="card card-body">
<form action="/update/{{employee.id}}" method="POST">
<div class="form-group">
<input type="text" name="fullname" value="{{employee.name}}" class="form-control">
</div>
<div class="form-group">
<input type="text" name="phone" value="{{employee.phone}}" class="form-control">
</div>
<div class="form-group">
<input type="text" name="email" value="{{employee.email}}" class="form-control">
</div>
<div class="form-group">
<button type="submit" class="btn btn-primary btn-block">
Update
</button>
</div>
</form>
</div>
</div>
</div>
{% endblock %}
