--
-- Table structure for table `orders`
--
CREATE TABLE `orders` (
`id` int(11) NOT NULL,
`customer_name` varchar(100) NOT NULL,
`purchased_items` varchar(100) NOT NULL,
`purchased_date` date NOT NULL,
`price` double(12,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `orders`
--
INSERT INTO `orders` (`id`, `customer_name`, `purchased_items`, `purchased_date`, `price`) VALUES
(1, 'Airi Satou', 'iPhone', '2020-05-07', 649.00),
(2, 'Angelica Ramos', 'Samsung Galaxy', '2020-11-10', 2500.00),
(3, 'Ashton Cox', 'Infinix Note 7', '2020-09-10', 299.09),
(4, 'Bradley Greer', 'Macbook Pro', '2020-11-26', 1799.50),
(5, 'Brenden Wagner', 'Samsung 50\" Smart 4K UHD TV ', '2020-11-27', 479.00),
(6, 'Brielle Williamson', '7 Series Curved LED 4K UHD', '2019-11-27', 269.00),
(7, 'Bruno Nash', 'iMac', '2019-11-28', 1999.05),
(8, 'Caesar Vance', 'Dell Inspiron 3573', '2019-11-30', 1999.05),
(9, 'Cara Stevens', 'Tlc 40inch tv Roku tv', '2019-12-07', 649.00),
(10, 'Cedric Kelly', 'Acer Aspire 3', '2021-01-13', 199.00);
#app.py
from flask import Flask, render_template, request, jsonify, flash, redirect
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():
cursor = mysql.connection.cursor()
cur = mysql.connection.cursor(MySQLdb.cursors.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 = mysql.connection.cursor(MySQLdb.cursors.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>Python Flask Date Range Search with jQuery Ajax DatePicker MySQL Database</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">Python Flask Date Range Search with jQuery Ajax DatePicker MySQL Database</h2>
<br/>
<br/>
<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 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="10%">Purchased Date</th>
<th width="5%">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 -->
<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="10%">Purchased Date</th>
<th width="5%">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>
