CREATE TABLE `employee` (
`id` int(11) NOT NULL,
`name` varchar(100) NOT NULL,
`position` varchar(100) NOT NULL,
`office` varchar(100) NOT NULL,
`age` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`photo` varchar(150) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `employee` (`id`, `name`, `position`, `office`, `age`, `salary`, `photo`) VALUES
(1, 'Tiger Wood', 'Accountant', 'Tokyo', 36, 5689, '01.jpg'),
(2, 'Mark Oto Ednalan', 'Chief Executive Officer (CEO)', 'London', 56, 5648, '02.jpg'),
(3, 'Jacob thompson', 'Junior Technical Author', 'San Francisco', 23, 5689, '03.jpg'),
(4, 'cylde Ednalan', 'Software Engineer', 'Olongapo', 23, 54654, '04.jpg'),
(5, 'Rhona Davidson', 'Software Engineer', 'San Francisco', 26, 5465, '05.jpg'),
(6, 'Quinn Flynn', 'Integration Specialist', 'New York', 53, 56465, '06.jpg'),
(8, 'Tiger Nixon', 'Software Engineer', 'London', 45, 456, '07.jpg'),
(9, 'Airi Satou', 'Pre-Sales Support', 'New York', 25, 4568, '08.jpg'),
(10, 'Angelica Ramos', 'Sales Assistant', 'New York', 45, 456, '09.jpg'),
(11, 'Ashton updated', 'Senior Javascript Developer', 'Olongapo', 45, 54565, '01.jpg'),
(12, 'Bradley Greer', 'Regional Director', 'San Francisco', 27, 5485, '02.jpg'),
(13, 'Brenden Wagner', 'Javascript Developer', 'San Francisco', 38, 65468, '03.jpg'),
(14, 'Brielle Williamson', 'Personnel Lead', 'Olongapo', 56, 354685, '04.jpg'),
(15, 'Bruno Nash', 'Customer Support', 'New York', 36, 65465, '05.jpg'),
(16, 'cairocoders', 'Sales Assistant', 'Sydney', 45, 56465, '06.jpg'),
(17, 'Zorita Serrano', 'Support Engineer', 'San Francisco', 38, 6548, '07.jpg'),
(18, 'Zenaida Frank', 'Chief Operating Officer (COO)', 'San Francisco', 39, 545, '08.jpg'),
(19, 'Sakura Yamamoto', 'Support Engineer', 'Tokyo', 48, 5468, '05.jpg'),
(20, 'Serge Baldwin', 'Data Coordinator', 'Singapore', 85, 5646, '05.jpg'),
(21, 'Shad Decker', 'Regional Director', 'Tokyo', 45, 4545, '05.jpg');
ALTER TABLE `employee`
ADD PRIMARY KEY (`id`);
ALTER TABLE `employee`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=22;
Download Datatables from here. https://datatables.net/download/
#app.py
from flask import Flask, request, render_template, jsonify, json
from flaskext.mysql import MySQL #pip install flask-mysql
import pymysql
app = Flask(__name__)
mysql = MySQL()
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 home():
return render_template('index.html')
@app.route("/ajaxfile",methods=["POST","GET"])
def ajaxfile():
try:
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
if request.method == 'POST':
draw = request.form['draw']
row = int(request.form['start'])
rowperpage = int(request.form['length'])
searchValue = request.form["search[value]"]
print(draw)
print(row)
print(rowperpage)
print(searchValue)
## Total number of records without filtering
cursor.execute("select count(*) as allcount from employee")
rsallcount = cursor.fetchone()
totalRecords = rsallcount['allcount']
print(totalRecords)
## Total number of records with filtering
likeString = "%" + searchValue +"%"
cursor.execute("SELECT count(*) as allcount from employee WHERE name LIKE %s OR position LIKE %s OR office LIKE %s", (likeString, likeString, likeString))
rsallcount = cursor.fetchone()
totalRecordwithFilter = rsallcount['allcount']
print(totalRecordwithFilter)
## Fetch records
if searchValue=='':
cursor.execute("SELECT * FROM employee ORDER BY name asc limit %s, %s;", (row, rowperpage))
employeelist = cursor.fetchall()
else:
cursor.execute("SELECT * FROM employee WHERE name LIKE %s OR position LIKE %s OR office LIKE %s limit %s, %s;", (likeString, likeString, likeString, row, rowperpage))
employeelist = cursor.fetchall()
data = []
for row in employeelist:
data.append({
'name': row['name'],
'position': row['position'],
'age': row['age'],
'salary': row['salary'],
'office': row['office'],
})
response = {
'draw': draw,
'iTotalRecords': totalRecords,
'iTotalDisplayRecords': totalRecordwithFilter,
'aaData': data,
}
return jsonify(response)
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
if __name__ == "__main__":
app.run()
templates/index.html
//templates/index.html
<!doctype html>
<html>
<head>
<title>DataTable AJAX pagination using Python Flask and Mysql database</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
<link href='https://cdn.datatables.net/1.10.24/css/jquery.dataTables.min.css' rel='stylesheet' type='text/css'>
<script src="https://code.jquery.com/jquery-3.5.1.js"></script>
<script src="https://cdn.datatables.net/1.10.24/js/jquery.dataTables.min.js"></script>
</head>
<body >
<div class="container" >
<div class="row" style="padding:50px;">
<p><h1>DataTable AJAX pagination using Python Flask and Mysql database</h1></p>
<div >
<table id='empTable' class='display dataTable' width='100%'>
<thead>
<tr>
<th>Employee Name</th>
<th>Position</th>
<th>Age</th>
<th>Salary</th>
<th>Office</th>
</tr>
</thead>
</table>
</div>
</div>
</div>
<script>
$(document).ready(function() {
var empDataTable = $('#empTable').DataTable({
'processing': true,
'serverSide': true,
'serverMethod': 'post',
'ajax': {
'url':'/ajaxfile'
},
'lengthMenu': [[5, 10, 25, 50, -1], [5, 10, 25, 50, "All"]],
searching: true,
sort: false,
"serverSide": true,
'columns': [
{ data: 'name' },
{ data: 'position' },
{ data: 'age' },
{ data: 'salary' },
{ data: 'office' },
]
});
});
</script>
</body>
</html>
