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
Datepicker https://jqueryui.com/datepicker/
CREATE TABLE orders (
id serial PRIMARY KEY,
customer_name VARCHAR ( 150 ) NOT NULL,
purchased_items VARCHAR ( 150 ) NOT NUL,L
purchased_date TIMESTAMP NOT NULL,
price DOUBLE PRECISION
);
INSERT INTO
orders(customer_name,purchased_items,purchased_date,price)
VALUES
('Airi Satou', 'iPhone', '2020-05-07', 649.00),
('Angelica Ramos', 'Samsung Galaxy', '2020-11-10', 2500.00),
('Ashton Cox', 'Infinix Note 7', '2020-09-10', 299.09),
('Bradley Greer', 'Macbook Pro', '2020-11-26', 1799.50),
('Brenden Wagner', 'Samsung 50\" Smart 4K UHD TV ', '2020-11-27', 479.00),
('Brielle Williamson', '7 Series Curved LED 4K UHD', '2019-11-27', 269.00),
('Bruno Nash', 'iMac', '2019-11-28', 1999.05),
('Caesar Vance', 'Dell Inspiron 3573', '2019-11-30', 1999.05),
('Cara Stevens', 'Tlc 40inch tv Roku tv', '2019-12-07', 649.00),
('Cedric Kelly', 'Acer Aspire 3', '2021-01-13', 199.00);
#app.py
from flask import Flask, render_template, request, jsonify, flash, redirect
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():
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
cur.execute("SELECT * FROM orders ORDER BY id desc")
orders = cur.fetchall()
return render_template('index.html', orders=orders)
@app.route("/range",methods=["POST","GET"])
def range():
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
if request.method == 'POST':
From = request.form['From']
to = request.form['to']
print(From)
print(to)
query = "SELECT * from orders WHERE purchased_date BETWEEN '{}' AND '{}'".format(From,to)
cur.execute(query)
ordersrange = cur.fetchall()
return jsonify({'htmlresponse': render_template('response.html', ordersrange=ordersrange)})
if __name__ == "__main__":
app.run(debug=True)
templates/index.html
//templates/index.html
<!doctype html>
<html>
<head>
<meta charset="UTF-8">
<title>Date Range Search with Python Flask PostgreSQL jQuery Ajax and DatePicker</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css"/>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/jqueryui/1.12.1/jquery-ui.css"/>
</head>
<body>
<br/>
<div class="container">
<h2 align="center">Date Range Search with Python Flask PostgreSQL jQuery Ajax and DatePicker</h2>
<br/>
<div class="row">
<div class="col-md-2">
<input type="text" name="From" id="From" class="form-control" placeholder="From Date"/>
</div>
<div class="col-md-2">
<input type="text" name="to" id="to" class="form-control" placeholder="To Date"/>
</div>
<div class="col-md-8">
<input type="button" name="range" id="range" value="Range" class="btn btn-success"/>
</div>
<div class="clearfix"></div>
<br/>
</div>
<div id="purchase_order">
<table class="table table-bordered">
<tr>
<th width="5%">ID</th>
<th width="35%">Customer Name</th>
<th width="40%">Purchased Item</th>
<th width="20%">Purchased Date</th>
<th width="20%">Price</th>
</tr>
{% for row in orders %}
<tr>
<td>{{row.id}}</td>
<td>{{row.customer_name}}</td>
<td>{{row.purchased_items}}</td>
<td>{{row.purchased_date}}</td>
<td>$ {{row.price}}</td>
</tr>
{% endfor %}
</table>
</div>
</div>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.1.1/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jqueryui/1.12.1/jquery-ui.js"></script>
<script>
$(document).ready(function(){
$.datepicker.setDefaults({
dateFormat: 'yy-mm-dd'
});
$(function(){
$("#From").datepicker();
$("#to").datepicker();
});
$('#range').click(function(){
var From = $('#From').val();
var to = $('#to').val();
if(From != '' && to != '')
{
$.ajax({
url:"/range",
method:"POST",
data:{From:From, to:to},
success:function(data)
{
$('#purchase_order').html(data);
$('#purchase_order').append(data.htmlresponse);
}
});
}
else
{
alert("Please Select the Date");
}
});
});
</script>
</body>
</html>
templates/response.html
//templates/response.html
<table class="table table-bordered">
<tr>
<th width="5%">ID</th>
<th width="35%">Customer Name</th>
<th width="40%">Purchased Item</th>
<th width="20%">Purchased Date</th>
<th width="20%">Price</th>
</tr>
{% for row in ordersrange %}
<tr>
<td>{{row.id}}</td>
<td>{{row.customer_name}}</td>
<td>{{row.purchased_items}}</td>
<td>{{row.purchased_date}}</td>
<td>{{row.price}}</td>
</tr>
{% endfor %}
</table>
