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 product (
pid serial PRIMARY KEY,
name VARCHAR ( 70 ) NOT NULL,
image VARCHAR ( 255 ) NOT NULL,
category VARCHAR ( 70 ) NOT NULL,
price INT NOT NULL
);
INSERT INTO
product(name,image,category,price)
VALUES
('Samsung Galaxy A10S', '1.jpg', 'Mobile', 520),
('HP Laptop - 17z-ca100 ', '2.jpg', 'Laptop', 1600),
('3 IN 1 CAR VOLTMETER', '3.jpg', 'Car', 2020),
('Gucci G-Timeless', '4.jpg', 'Watch', 320),
('Infinix Hot S3', '5.jpg', 'Mobile', 150),
('VIVO V9 Youth', '6.jpeg', 'Laptop', 3500),
('Moto E4 Plus', '7.jpeg', 'Car', 250),
('Lenovo K8 Plus', '8.jpeg', 'Watch', 4500);
#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("/fetchrecords",methods=["POST","GET"])
def fetchrecords():
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
if request.method == 'POST':
query = request.form['action']
minimum_price = request.form['minimum_price']
maximum_price = request.form['maximum_price']
#print(query)
if query == '':
cur.execute("SELECT * FROM product ORDER BY pid ASC")
productlist = cur.fetchall()
print('all list')
else:
cur.execute("SELECT * FROM product WHERE price BETWEEN (%s) AND (%s)", [minimum_price, maximum_price])
productlist = cur.fetchall()
return jsonify({'htmlresponse': render_template('response.html', productlist=productlist)})
if __name__ == "__main__":
app.run(debug=True)
templates/index.html
//templates/index.html
<!DOCTYPE html>
<html>
<head>
<title>Price Range Product Filters Using Jquery Ajax Python Flask and PostgreSQL</title>
<link rel="stylesheet" href="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script>
<script src="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
<link rel="stylesheet" href="http://code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
<script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
</head>
<body>
<div class="container">
<div class="row">
<br />
<h2 align="center">Price Range Product Filters Using Jquery Ajax Python Flask and PostgreSQL</h2>
<br />
<div class="col-md-3">
<div class="list-group">
<h3>Price</h3>
<input type="hidden" id="hidden_minimum_price" value="0" />
<input type="hidden" id="hidden_maximum_price" value="65000" />
<p id="price_show">10 - 5000</p>
<div id="price_range"></div>
</div>
</div>
<div class="col-md-9">
<div class="row filter_data">
</div>
</div>
</div>
</div>
<style>
#loading
{
text-align:center;
background: url('images/loading.gif') no-repeat center;
height: 150px;
}
</style>
<script>
$(document).ready(function(){
filter_data();
function filter_data()
{
$('.filter_data').html('<div id="loading" style="" ></div>');
var action = 'fetch_data';
var minimum_price = $('#hidden_minimum_price').val();
var maximum_price = $('#hidden_maximum_price').val();
$.ajax({
url:"/fetchrecords",
method:"POST",
data:{action:action, minimum_price:minimum_price, maximum_price:maximum_price},
success:function(data){
$('.filter_data').html(data);
$(".filter_data").append(data.htmlresponse);
}
});
}
$('#price_range').slider({
range:true,
min:50,
max:5000,
values:[50, 5000],
step:50,
stop:function(event, ui)
{
$('#price_show').html(ui.values[0] + ' - ' + ui.values[1]);
$('#hidden_minimum_price').val(ui.values[0]);
$('#hidden_maximum_price').val(ui.values[1]);
filter_data();
}
});
});
</script>
</body>
</html>
templates/response.html
//templates/response.html
{% for row in productlist %}
<div class="col-sm-4 col-lg-3 col-md-3">
<div style="border:1px solid #ccc; border-radius:5px; padding:16px; margin-bottom:16px; height:300px;">
<img src="/static/images/{{row.image}}" alt="" class="img-responsive" >
<p align="center"><strong><a href="#">{{row.name}}</a></strong></p>
<h4 style="text-align:center;" class="text-danger" >{{row.price}}</h4>
</div>
</div>
{% endfor %}
