install psycopg2 https://pypi.org/project/psycopg2/
Psycopg is the most popular PostgreSQL database adapter for the Python programming language.
(venv) PS C:\flaskmyproject> pip install psycopg2
CREATE TABLE employee (
id serial PRIMARY KEY,
name VARCHAR ( 100 ) NOT NULL,
position VARCHAR ( 100 ) NOT NULL,
office VARCHAR ( 100 ) NOT NULL,
age INT NOT NULL,
salary INT NOT NULL,
photo VARCHAR ( 150 ) NOT NULL
);
INSERT INTO
employee(name, position, office, age, salary, photo)
VALUES
('Tiger Wood', 'Accountant', 'Tokyo', 36, 5689, '01.jpg'),
('Mark Oto Ednalan', 'Chief Executive Officer (CEO)', 'London', 56, 5648, '02.jpg'),
('Jacob thompson', 'Junior Technical Author', 'San Francisco', 23, 5689, '03.jpg'),
('cylde Ednalan', 'Software Engineer', 'Olongapo', 23, 54654, '04.jpg'),
('Rhona Davidson', 'Software Engineer', 'San Francisco', 26, 5465, '05.jpg'),
('Quinn Flynn', 'Integration Specialist', 'New York', 53, 56465, '06.jpg'),
('Tiger Nixon', 'Software Engineer', 'London', 45, 456, '07.jpg'),
('Airi Satou', 'Pre-Sales Support', 'New York', 25, 4568, '08.jpg'),
('Angelica Ramos', 'Sales Assistant', 'New York', 45, 456, '09.jpg'),
('Ashton updated', 'Senior Javascript Developer', 'Olongapo', 45, 54565, '01.jpg'),
('Bradley Greer', 'Regional Director', 'San Francisco', 27, 5485, '02.jpg'),
('Brenden Wagner', 'Javascript Developer', 'San Francisco', 38, 65468, '03.jpg'),
('Brielle Williamson', 'Personnel Lead', 'Olongapo', 56, 354685, '04.jpg'),
('Bruno Nash', 'Customer Support', 'New York', 36, 65465, '05.jpg'),
('cairocoders', 'Sales Assistant', 'Sydney', 45, 56465, '06.jpg'),
('Zorita Serrano', 'Support Engineer', 'San Francisco', 38, 6548, '07.jpg'),
('Zenaida Frank', 'Chief Operating Officer (COO)', 'San Francisco', 39, 545, '08.jpg'),
('Sakura Yamamoto', 'Support Engineer', 'Tokyo', 48, 5468, '05.jpg'),
('Serge Baldwin', 'Data Coordinator', 'Singapore', 85, 5646, '05.jpg'),
('Shad Decker', 'Regional Director', 'Tokyo', 45, 4545, '05.jpg');
#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 %}
