DataTable Inline Editing using Python Flask MySQLdb jquery ajax and X-Editable
DataTables is a plug-in for the jQuery Javascript library. It is a highly flexible tool, built upon the foundations of progressive enhancement, that adds all of these advanced features to any HTML table.
https://datatables.net/
X-editable
This library allows you to create editable elements on your page. It can be used with any engine (bootstrap, jquery-ui, jquery only) and includes both popup and inline modes.
https://vitalets.github.io/x-editable/
--
-- Table structure for table `employee`
--
CREATE TABLE `employee` (
`id` int(11) NOT NULL,
`name` varchar(100) NOT NULL,
`email` varchar(100) NOT NULL,
`phone` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `employee`
--
INSERT INTO `employee` (`id`, `name`, `email`, `phone`) VALUES
(1, 'Caite Ednalan', 'caite@gmail.com', '35465465'),
(2, 'Mark Oto', 'marokoto@gmail.com', '123123123'),
(3, 'Jacob thompson', 'jacobthomson@gmail.com', '13123123'),
(4, 'cylde Ednalan', 'cyledednalan@gmail.com', '313123'),
(5, 'Angelica Ramos', 'AngelicaRamos@gmail.com', '21654654654999999'),
(6, 'Airi Satou', 'AiriSatou@gmail.com', '354646'),
(8, 'Tiger Nixon', 'TigerNixon@gmail.com', '546456'),
(9, 'Airi Satou', 'AiriSatou@gmail.com', '354656'),
(10, 'Angelica Ramos', 'Caite@gmail.com', '465465465465'),
(11, 'Ashton Cox', 'marokoto@gmail.com', '123123123'),
(12, 'Bradley Greer', 'jacobthomson@gmail.com', '13123123'),
(13, 'Brenden Wagner', 'cyledednalan@gmail.com', '313123'),
(14, 'Brielle Williamson', 'AngelicaRamos@gmail.com', '21654654654999999'),
(15, 'Bruno Nash', 'AiriSatou@gmail.com', '354646');
app.py
#app.py
from flask import Flask, render_template, json, request, redirect
from flask_mysqldb import MySQL,MySQLdb #pip install flask-mysqldb https://github.com/alexferl/flask-mysqldb
from datetime import datetime
app = Flask(__name__)
app.secret_key = "caircocoders-ednalan-2020"
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 main():
return redirect('/useradmin')
@app.route('/useradmin')
def useradmin():
cur = mysql.connection.cursor(MySQLdb.cursors.DictCursor)
result = cur.execute("SELECT * FROM employee")
employee = cur.fetchall()
return render_template('useradmin.html', employee=employee)
@app.route('/updateemployee', methods=['POST'])
def updateemployee():
pk = request.form['pk']
name = request.form['name']
value = request.form['value']
cur = mysql.connection.cursor(MySQLdb.cursors.DictCursor)
if name == 'name':
cur.execute("UPDATE employee SET name = %s WHERE id = %s ", (value, pk))
elif name == 'email':
cur.execute("UPDATE employee SET email = %s WHERE id = %s ", (value, pk))
elif name == 'phone':
cur.execute("UPDATE employee SET phone = %s WHERE id = %s ", (value, pk))
mysql.connection.commit()
cur.close()
return json.dumps({'status':'OK'})
if __name__ == '__main__':
app.run(debug=True)
templates/useradmin.html
//templates/useradmin.html
<html>
<head>
<meta name="viewport" content="width=device-width">
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>DataTable Inline Editing using Python Flask MySQLdb jquery ajax and X-Editable</title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
<script src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.10.12/js/dataTables.bootstrap.min.js"></script>
<link rel="stylesheet" href="https://cdn.datatables.net/1.10.12/css/dataTables.bootstrap.min.css" />
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
<link href="https://cdnjs.cloudflare.com/ajax/libs/x-editable/1.5.1/bootstrap3-editable/css/bootstrap-editable.css" rel="stylesheet">
<script src="https://cdnjs.cloudflare.com/ajax/libs/x-editable/1.5.1/bootstrap3-editable/js/bootstrap-editable.js"></script>
<script type="text/javascript" language="javascript">
$(document).ready(function(){
var dataTable = $('#sample_data').DataTable();
$('#sample_data').editable({
container:'body',
selector:'td.name',
url:'/updateemployee',
title:'Name',
type:'POST',
validate:function(value){
if($.trim(value) == '')
{
return 'This field is required';
}
}
});
$('#sample_data').editable({
container:'body',
selector:'td.email',
url:'/updateemployee',
title:'Email',
type:'POST',
validate:function(value){
if($.trim(value) == '')
{
return 'This field is required';
}
}
});
$('#sample_data').editable({
container:'body',
selector:'td.phone',
url:'/updateemployee',
title:'Phone',
type:'POST',
validate:function(value){
if($.trim(value) == '')
{
return 'This field is required';
}
}
});
});
</script>
</head>
<body>
<div class="container">
<h3 align="center">DataTable Inline Editing using Python Flask MySQLdb jquery ajax and X-Editable</h3>
<br />
<div class="panel panel-default">
<div class="panel-heading">DataTable</div>
<div class="panel-body">
<div class="table-responsive">
<table id="sample_data" class="table table-bordered table-striped">
<thead>
<tr>
<th>ID</th>
<th>Name</th>
<th>Email</th>
<th>Phone</th>
</tr>
</thead>
<tbody>
{% for row in employee %}
<tr>
<td data-pk="{{row.id}}">{{row.id}}</td>
<td data-name="name" class="name" data-type="text" data-pk="{{row.id}}">{{row.name}}</td>
<td data-name="email" class="email" data-type="text" data-pk="{{row.id}}">{{row.email}}</td>
<td data-name="phone" class="phone" data-type="text" data-pk="{{row.id}}">{{row.phone}}</td>
</tr>
{% endfor %}
</tbody>
</table>
</div>
</div>
</div>
</div>
<br />
<br />
</body>
</html>
