Python Flask add edit delete Datatable Row Using Jquery Ajax and mysqldb database
#app.py
from flask import Flask, render_template, redirect, request, flash, jsonify
from flask_mysqldb import MySQL,MySQLdb #pip install flask-mysqldb https://github.com/alexferl/flask-mysqldb
app = Flask(__name__)
app.secret_key = "caircocoders-ednalan"
app.config['MYSQL_HOST'] = 'localhost'
app.config['MYSQL_USER'] = 'root'
app.config['MYSQL_PASSWORD'] = ''
app.config['MYSQL_DB'] = 'testingdb'
app.config['MYSQL_CURSORCLASS'] = 'DictCursor'
mysql = MySQL(app)
@app.route('/')
def index():
cur = mysql.connection.cursor(MySQLdb.cursors.DictCursor)
result = cur.execute("SELECT * FROM tblemployee ORDER BY id")
employee = cur.fetchall()
return render_template('index.html', employee=employee)
@app.route("/ajax_add",methods=["POST","GET"])
def ajax_add():
cursor = mysql.connection.cursor()
cur = mysql.connection.cursor(MySQLdb.cursors.DictCursor)
if request.method == 'POST':
txtname = request.form['txtname']
txtdepartment = request.form['txtdepartment']
txtphone = request.form['txtphone']
print(txtname)
if txtname == '':
msg = 'Please Input name'
elif txtdepartment == '':
msg = 'Please Input Department'
elif txtphone == '':
msg = 'Please Input Phone'
else:
cur.execute("INSERT INTO tblemployee (name,department,phone) VALUES (%s,%s,%s)",[txtname,txtdepartment,txtphone])
mysql.connection.commit()
cur.close()
msg = 'New record created successfully'
return jsonify(msg)
@app.route("/ajax_update",methods=["POST","GET"])
def ajax_update():
cursor = mysql.connection.cursor()
cur = mysql.connection.cursor(MySQLdb.cursors.DictCursor)
if request.method == 'POST':
string = request.form['string']
txtname = request.form['txtname']
txtdepartment = request.form['txtdepartment']
txtphone = request.form['txtphone']
print(string)
cur.execute("UPDATE tblemployee SET name = %s, department = %s, phone = %s WHERE id = %s ", [txtname, txtdepartment, txtphone, string])
mysql.connection.commit()
cur.close()
msg = 'Record successfully Updated'
return jsonify(msg)
@app.route("/ajax_delete",methods=["POST","GET"])
def ajax_delete():
cursor = mysql.connection.cursor()
cur = mysql.connection.cursor(MySQLdb.cursors.DictCursor)
if request.method == 'POST':
getid = request.form['string']
print(getid)
cur.execute('DELETE FROM tblemployee WHERE id = {0}'.format(getid))
mysql.connection.commit()
cur.close()
msg = 'Record deleted successfully'
return jsonify(msg)
if __name__ == "__main__":
app.run(debug=True)
index.html
//index.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>Python Flask add edit delete Row Using Jquery Ajax and mysqldb database</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
<style type="text/css">
body {
color: #404E67;
background: #F5F7FA;
font-family: 'Open Sans', sans-serif;
}
.table-wrapper {
width: 700px;
margin: 30px auto;
background: #fff;
padding: 20px;
box-shadow: 0 1px 1px rgba(0,0,0,.05);
}
.table-title {
padding-bottom: 10px;
margin: 0 0 10px;
}
.table-title h2 {
margin: 6px 0 0;
font-size: 22px;
}
.table-title .add-new {
float: right;
height: 30px;
font-weight: bold;
font-size: 12px;
text-shadow: none;
min-width: 100px;
border-radius: 50px;
line-height: 13px;
}
.table-title .add-new i {
margin-right: 4px;
}
table.table {
table-layout: fixed;
}
table.table tr th, table.table tr td {
border-color: #e9e9e9;
}
table.table th i {
font-size: 13px;
margin: 0 5px;
cursor: pointer;
}
table.table th:last-child {
width: 100px;
}
table.table td a {
cursor: pointer;
display: inline-block;
margin: 0 5px;
min-width: 24px;
}
table.table td a.add {
color: #27C46B;
}
table.table td a.edit {
color: #FFC107;
}
table.table td a.delete {
color: #E34724;
}
table.table td i {
font-size: 19px;
}
table.table td a.add i {
font-size: 24px;
margin-right: -1px;
position: relative;
top: 3px;
}
table.table .form-control {
height: 32px;
line-height: 32px;
box-shadow: none;
border-radius: 2px;
}
table.table .form-control.error {
border-color: #f50000;
}
table.table td .add {
display: none;
}
</style>
<script type="text/javascript">
$(document).ready(function(){
$('[data-toggle="tooltip"]').tooltip();
var actions = $("table td:last-child").html();
// Append table with add row form on add new button click
$(".add-new").click(function(){
$(this).attr("disabled", "disabled");
var index = $("table tbody tr:last-child").index();
var row = '<tr>' +
'<td><input type="text" class="form-control" name="name" id="txtname"></td>' +
'<td><input type="text" class="form-control" name="department" id="txtdepartment"></td>' +
'<td><input type="text" class="form-control" name="phone" id="txtphone"></td>' +
'<td>' + actions + '</td>' +
'</tr>';
$("table").append(row);
$("table tbody tr").eq(index + 1).find(".add, .edit, .delete").toggle();
$('[data-toggle="tooltip"]').tooltip();
});
// Add row on add button click
$(document).on("click", ".add", function(){
var empty = false;
var input = $(this).parents("tr").find('input[type="text"]');
input.each(function(){
if(!$(this).val()){
$(this).addClass("error");
empty = true;
} else{
$(this).removeClass("error");
}
});
var txtname = $("#txtname").val();
var txtdepartment = $("#txtdepartment").val();
var txtphone = $("#txtphone").val();
$.post("/ajax_add", { txtname: txtname, txtdepartment: txtdepartment, txtphone: txtphone}, function(data) {
$("#displaymessage").html(data);
$("#displaymessage").show();
});
$(this).parents("tr").find(".error").first().focus();
if(!empty){
input.each(function(){
$(this).parent("td").html($(this).val());
});
$(this).parents("tr").find(".add, .edit").toggle();
$(".add-new").removeAttr("disabled");
}
});
// Delete row on delete button click
$(document).on("click", ".delete", function(){
$(this).parents("tr").remove();
$(".add-new").removeAttr("disabled");
var id = $(this).attr("id");
var string = id;
$.post("/ajax_delete", { string: string}, function(data) {
$("#displaymessage").html(data);
$("#displaymessage").show();
});
});
// update rec row on edit button click
$(document).on("click", ".update", function(){
var id = $(this).attr("id");
var string = id;
var txtname = $("#txtname").val();
var txtdepartment = $("#txtdepartment").val();
var txtphone = $("#txtphone").val();
$.post("/ajax_update", { string: string,txtname: txtname, txtdepartment: txtdepartment, txtphone: txtphone}, function(data) {
$("#displaymessage").html(data);
$("#displaymessage").show();
});
});
// Edit row on edit button click
$(document).on("click", ".edit", function(){
$(this).parents("tr").find("td:not(:last-child)").each(function(i){
if (i=='0'){
var idname = 'txtname';
}else if (i=='1'){
var idname = 'txtdepartment';
}else if (i=='2'){
var idname = 'txtphone';
}else{}
$(this).html('<input type="text" name="updaterec" id="' + idname + '" class="form-control" value="' + $(this).text() + '">');
});
$(this).parents("tr").find(".add, .edit").toggle();
$(".add-new").attr("disabled", "disabled");
$(this).parents("tr").find(".add").removeClass("add").addClass("update");
});
});
</script>
</head>
<body>
<div class="container"><p><h1 align="center">Python Flask add edit delete Datatable Row Using Jquery Ajax and mysqldb database</h1></p>
<div class="table-wrapper">
<div class="table-title">
<div class="row">
<div class="col-sm-8"><h2>Employee <b>Details</b></h2></div>
<div class="col-sm-4">
<button type="button" class="btn btn-info add-new"><i class="fa fa-plus"></i> Add New</button>
</div>
<div class='btn btn-info' id="displaymessage" style="display:none;width:100%;margin-top:10px;"></div>
</div>
</div>
<table class="table table-bordered">
<thead>
<tr>
<th>Name</th>
<th>Department</th>
<th>Phone</th>
<th>Actions</th>
</tr>
</thead>
<tbody>
{% for row in employee %}
<tr>
<td>{{row.name}}</td>
<td>{{row.department}}</td>
<td>{{row.phone}}</td>
<td>
<a class="add" title="Add" data-toggle="tooltip" id="{{row.id}}"><i class="fa fa-user-plus"></i></a>
<a class="edit" title="Edit" data-toggle="tooltip" id="{{row.id}}"><i class="fa fa-pencil"></i></a>
<a class="delete" title="Delete" data-toggle="tooltip" id="{{row.id}}"><i class="fa fa-trash-o"></i></a>
</td>
</tr>
{% endfor %}
</tbody>
</table>
</div>
</div>
</body>
</html>
VIDEO
