Build a Search Box with SQL LIKE Operator using Jquery Ajax Python Flask and PostgreSQL

Search Box SQL LIKE operator using Jquery Ajax Python Flask and PostgreSQL database

Subscribe: https://www.youtube.com/c/Cairocoders/featured

Source Code


#app.py
from flask import Flask, render_template, request, jsonify
import psycopg2 #pip install psycopg2 
import psycopg2.extras
     
app = Flask(__name__)
     
app.secret_key = "caircocoders-ednalan"
     
DB_HOST = "localhost"
DB_NAME = "sampledb"
DB_USER = "postgres"
DB_PASS = "admin"
         
conn = psycopg2.connect(dbname=DB_NAME, user=DB_USER, password=DB_PASS, host=DB_HOST)
 
@app.route('/')
def index():
    return render_template('index.html')
  
@app.route("/searchdata",methods=["POST","GET"])
def searchdata():
    if request.method == 'POST':
        search_word = request.form['search_word']
        print(search_word)
        cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
        cur.execute('SELECT * FROM employee WHERE name LIKE %(name)s', { 'name': '%{}%'.format(search_word)})
        employee = cur.fetchall()
    return jsonify({'data': render_template('response.html', employee=employee)})
  
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>Search Box SQL LIKE operator using Jquery Ajax Python Flask and PostgreSQL database</title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
<script type="text/javascript">
$(function() {
  $(".search_button").click(function() {
      var search_word = $("#search_box").val();
      var dataString = 'search_word='+ search_word;
      if(search_word==''){
      }else{
        $.ajax({
          type: "POST",
          url: "/searchdata",
          data: dataString,
          cache: false,
          beforeSend: function(html) {
              document.getElementById("insert_search").innerHTML = ''; 
              $("#flash").show();
              $("#searchword").show();
              $(".searchword").html(search_word);
              $("#flash").html('<img src="/static/img/loader.gif" align="absmiddle"> Loading Results...');
            },
          success: function(html){
              $("#insert_search").show();
              $("#insert_search").append(html.data);
              $("#flash").hide();
          }
        });
      }
    return false;
  });
});
</script>
</head>
<body>
<div align="center">
  <div style="width:700px">
  <div style="margin-top:20px; text-align:left">
    <p align="center"><h1>Search Box SQL LIKE operator using Jquery Ajax Python Flask and PostgreSQL database </h1></p>
    <form method="get" action="">
    <input type="text" name="search" id="search_box" class='search_box'/>
    <input type="submit" value="Search" class="search_button" /><br />
    <span style="color:#666666; font-size:14px; font-family:Arial, Helvetica, sans-serif;"><b>Ex :</b> Cairocoders</span>
    </form>
  </div>   
  <div>
    <div id="searchword">Search results for <span class="searchword"></span></div>
    <div id="flash"></div>
    <ol id="insert_search" class="update"></ol>
  </div>
  </div>
</div>
<style>
body{
font-family:Arial, Helvetica, sans-serif;
}
a
{
color:#DF3D82;
text-decoration:none
}
a:hover
{
color:#DF3D82;
text-decoration:underline;
}
#search_box{
 padding:3px; border:solid 1px #666666; width:400px; height:45px; font-size:18px;-moz-border-radius: 6px;-webkit-border-radius: 6px;
}
.search_button{
 height:50px;border:#fe6f41 solid 1px; padding-left:9px;padding-right:9px;padding-top:9px;padding-bottom:9px; color:#000; font-weight:bold; font-size:16px;-moz-border-radius: 6px;-webkit-border-radius: 6px;
}
ol.update{
 list-style:none;font-size:1.1em; margin-top:20px;padding-left:0; 
}
#flash{
 margin-top:20px;
 text-align:left;
}
#searchword{
 text-align:left; margin-top:20px; display:none;
 font-family:Arial, Helvetica, sans-serif;
 font-size:16px;
 color:#000;
}
.searchword{
 font-weight:bold;
 color:#fe6f41;
}
ol.update li{ border-bottom:#dedede dashed 1px; text-align:left;padding-top:10px;padding-bottom:10px;}
ol.update li:first-child{ border-top:#dedede dashed 1px; text-align:left}
</style>
</body>
</html>

templates/response.html

//templates/response.html
{% for row in employee %}  
  <li>{{row.name}} <br/><span style='font-size:12px;'>{{row.position}}</span></li>
{% endfor %}

#python #postgresql