Database table
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;
#app.py
from flask import Flask, render_template, request, 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():
return render_template('index.html')
@app.route("/ajaxlivesearch",methods=["POST","GET"])
def ajaxlivesearch():
cur = mysql.connection.cursor(MySQLdb.cursors.DictCursor)
if request.method == 'POST':
search_word = request.form['query']
print(search_word)
if search_word == '':
query = "SELECT * from employee ORDER BY id"
cur.execute(query)
employee = cur.fetchall()
else:
query = "SELECT * from employee WHERE name LIKE '%{}%' OR email LIKE '%{}%' OR phone LIKE '%{}%' ORDER BY id DESC LIMIT 20".format(search_word,search_word,search_word)
cur.execute(query)
numrows = int(cur.rowcount)
employee = cur.fetchall()
print(numrows)
return jsonify({'htmlresponse': render_template('response.html', employee=employee, numrows=numrows)})
if __name__ == "__main__":
app.run(debug=True)
templates/index.html
//templates/index.html
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Python Flask Live Data Search using Jquery Ajax and MySql Database</title>
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/4.1.1/css/bootstrap.min.css" rel="stylesheet" id="bootstrap-css">
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.1.1/js/bootstrap.min.js"></script>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
<script>
$(document).ready(function(){
load_data();
function load_data(query)
{
$.ajax({
url:"/ajaxlivesearch",
method:"POST",
data:{query:query},
success:function(data)
{
$('#result').html(data);
$("#result").append(data.htmlresponse);
}
});
}
$('#search_text').keyup(function(){
var search = $(this).val();
if(search != ''){
load_data(search);
}else{
load_data();
}
});
});
</script>
</head>
<body>
<div class="container search-table">
<p><h2 align="center">Python Flask Live Data Search using Jquery Ajax and MySql Database</h2></p>
<div class="search-box">
<div class="row">
<div class="col-md-3">
<h5>Search All Fields</h5>
</div>
<div class="col-md-9">
<input type="text" name="search_text" id="search_text" class="form-control" placeholder="Search all fields e.g. HTML">
</div>
</div>
</div>
<div id="result"></div>
</div>
<style>
.search-table{
padding: 10%;
margin-top: -6%;
}
.search-box{
background: #c1c1c1;
border: 1px solid #ababab;
padding: 3%;
}
.search-box input:focus{
box-shadow:none;
border:2px solid #eeeeee;
}
.search-list{
background: #fff;
border: 1px solid #ababab;
border-top: none;
}
.search-list h3{
background: #eee;
padding: 3%;color:#fe6f41;
margin-bottom: 0%;
}
</style>
</body>
</html>
templates/response.html
//templates/response.html
<h3>{{numrows}} Records Found</h3>
<table class="table table-striped custab">
<thead>
<tr>
<th>Name</th>
<th>Email</th>
<th>Phone</th>
</tr>
</thead>
<tbody>
{% for row in employee %}
<tr>
<td>{{row.name}}</td>
<td>{{row.email}}</td>
<td>{{row.phone}}</td>
</tr>
{% endfor %}
