Search code examples
pythonmysqlajaxflaskplotly

How to setup ajax date picker to Flask app using MySQL database for plotting graph using date filtered data

I am trying to build small web application fetchehing data from MySQL database with help of ajax date filter and applying dates and rendering table and chart in frontend I can able to get Table with my script not able to get chart how to do changes for getting chart and table as well this is main.py file

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'] = 'xxxx'
app.config['MYSQL_DB'] = "battery_voltage"
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 voltage ORDER BY date 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 voltage WHERE 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,port="2021")

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"/>
    <style>
    table, th, td {
      border: 1px solid black;
      border-collapse: collapse;
    }
    </style>
</head>
<body>
    <br/>
    <div class="container">
    <h2 align="left">OPSPOD Battery Voltage</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" style="width:40%" align="left" >
    <tr>
        <th width="5%">Date</th>
        <th width="4%">Time</th>
        <th width="4%">voltage</th>
        <th width="4%">ignition</th>
    </tr>
    {% for row in orders %}
    <tr>
        <td>{{row.date}}</td>
        <td>{{row.time}}</td>
        <td>{{row.voltage}}</td>
        <td>{{row.ignition}}</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: 'dd-mm-yy'
        });
        $(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>

response.html

<table class="table table-bordered" style="width:40%" align="left" >
    <tr>
        <th width="5%">Date</th>
        <th width="4%">Time</th>
        <th width="4%">voltage</th>
        <th width="4%">ignition</th>
    </tr>
    {% for row in ordersrange %}
    <tr>
        <td>{{row.date}}</td>
        <td>{{row.time}}</td>
        <td>{{row.voltage}}</td>
        <td>{{row.ignition}}</td>
    </tr>
    {% endfor %}
</table>

This is the link for the data which I used in MYSQL DB.

With the code above I am able to get the output like this table below but I am trying for graph as well with same date filtering with X-axis time and Y-axis voltage

table image

Solution

  • Let's start by addressing your database issues. Storing dates and times as strings is a bad idea as they use more space and cannot be handled as efficiently as native DATE / TIME types. A date as string '01-12-2022' stored in a VARCHAR uses 11 Bytes, whereas if you convert it to DATE it is only 3 Bytes. Similarly for your time data - 8 Bytes as VARCHAR or 3 Bytes as TIME. Even better would be to combine the two together as DATETIME requiring only 5 Bytes, but I shall leave that for you to ponder.

    -- Update dates from dd-mm-yyyy (note 4 digit year) to yyyy-mm-dd
UPDATE `voltage` SET `date` = STR_TO_DATE(`date`, '%d-%m-%Y');
-- If your existing dates have 2 digit year then use
UPDATE `voltage` SET `date` = STR_TO_DATE(`date`, '%d-%m-%y');

-- update the column types
ALTER TABLE `voltage`
    MODIFY COLUMN `date` DATE NOT NULL,
    MODIFY COLUMN `time` TIME NOT NULL;

    You should also make sure you have a composite index on (date, time).

    To avoid this answer getting too long, I am not going to include the full content of the index.html template file but I have made the following changes -

    1. <div id="purchase_order"> to <div id="voltages">
    2. Added <div id="chart"></div> before <div id="voltages">
    3. Added <thead> around the header row and tbody around the rest of the table rows
    4. Added <script src="https://cdn.plot.ly/plotly-latest.min.js"></script> after the 2 jQuery scripts
    5. Renamed From as from in various places

    Then the inline script -

    <script>
const chartLayout = {
    hovermode: 'closest',
    xaxis: {
        type: 'date',
        dtick: 10800000,
        hoverformat: '%H:%M:%S',
        tickformat: '%H:00\n%d %b',
        rangebreaks: [{ pattern: 'hour' }]
    }
};
Plotly.react('chart', [{ x: [/* leaving these for you to figure out */], y: [], line: { simplify: false } }], chartLayout);

$(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:"GET",
                data:{from:from, to:to},
                success:function(data)
                {
                    let x = [], y = [], rows = '';
                    for (const row of data) {
                        x[x.length] = `${row.date} ${row.time}`;
                        y[y.length] = row.voltage;
                        rows += `<tr><td>${row.date}</td><td>${row.time}</td><td>${row.voltage}</td><td>${row.ignition}</td></tr>`;
                    }

                    // update table content
                    $('#voltages > table > tbody').html(rows);

                    // update chart
                    Plotly.react('chart', [{ x: x, y: y, line: { simplify: false } }], chartLayout);
                }
            });
        }
        else
        {
            alert("Please Select the Date");
        }
    });
});
</script>

    And this is the modified /range route -

    @app.route('/range')
def range():
    cur = mysql.connection.cursor(MySQLdb.cursors.DictCursor)

    fromDate = request.args.get('from')
    toDate = request.args.get('to')

    query = """
            SELECT CAST(`date` AS CHAR) AS `date`, CAST(`time` AS CHAR) AS `time`, `voltage`, `ignition`
            FROM voltage
            WHERE date BETWEEN '{}' AND '{}'
            ORDER BY date, time
        """.format(fromDate, toDate)
    cur.execute(query)
    voltages = cur.fetchall()

    return jsonify(voltages)

    The date and time have been cast to CHARs in the SELECT as json.dumps() (used by jsonify) does not like handling them as their native types. You should switch to using parameterized prepared statements to mitigate the current SQLi vulnerabilities.