如何在Flask+MySQL应用中实现Ajax日期过滤的表格与电压图表

以下是修改代码的具体步骤，实现表格和图表同时展示的功能：

1. 修改后端 main.py #

更新 /range 路由，同时返回表格HTML和原始数据（供图表使用），并确保查询结果按时间排序：

from flask import Flask, render_template, request, jsonify, flash, redirect
from flask_mysqldb import MySQL, 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():
    cur = mysql.connection.cursor(MySQLdb.cursors.DictCursor)
    # 按时间升序排列，保证图表X轴顺序正确
    cur.execute("SELECT * FROM voltage ORDER BY date asc, time asc")
    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)
    ordersrange = []
    if request.method == 'POST':
        From = request.form['From']
        to = request.form['to']
        # 按时间升序过滤数据
        query = "SELECT * FROM voltage WHERE date BETWEEN '{}' AND '{}' ORDER BY date asc, time asc".format(From, to)
        cur.execute(query)
        ordersrange = cur.fetchall()
    # 同时返回表格HTML和原始数据
    return jsonify({
        'htmlresponse': render_template('response.html', ordersrange=ordersrange),
        'data': ordersrange
    })

if __name__ == "__main__":
    app.run(debug=True,port="2021")

2. 修改前端 index.html #

添加图表容器、引入Chart.js库，并编写图表渲染逻辑：

<!doctype html>
<html>
<head>
    <meta charset="UTF-8">
    <title>OPSPOD Battery Voltage Monitor</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;
        }
        #purchase_order {
            float: left;
            width: 38%;
        }
        #voltage-chart-container {
            float: right;
            width: 60%;
            margin-top: 20px;
        }
        .clearfix {
            clear: both;
        }
    </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:100%" >
    <tr>
        <th width="25%">Date</th>
        <th width="25%">Time</th>
        <th width="25%">voltage</th>
        <th width="25%">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 id="voltage-chart-container">
        <canvas id="voltageChart"></canvas>
    </div>
    <div class="clearfix"></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>
    <!-- 引入Chart.js -->
    <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
    <script>
    $(document).ready(function(){
        $.datepicker.setDefaults({
            dateFormat: 'dd-mm-yy'
        });
        $(function(){
            $("#From").datepicker();
            $("#to").datepicker();
        });

        // 渲染电压图表的函数
        function renderVoltageChart(data) {
            // 提取时间（日期+时间）和电压数据
            const labels = data.map(item => `${item.date} ${item.time}`);
            const voltages = data.map(item => parseFloat(item.voltage));

            // 获取图表画布元素
            const ctx = document.getElementById('voltageChart').getContext('2d');
            
            // 销毁已有图表，避免重复创建
            if (window.voltageChart) {
                window.voltageChart.destroy();
            }

            // 创建折线图
            window.voltageChart = new Chart(ctx, {
                type: 'line',
                data: {
                    labels: labels,
                    datasets: [{
                        label: '电池电压',
                        data: voltages,
                        borderColor: 'rgba(75, 192, 192, 1)',
                        backgroundColor: 'rgba(75, 192, 192, 0.2)',
                        tension: 0.1,
                        fill: true
                    }]
                },
                options: {
                    responsive: true,
                    scales: {
                        x: {
                            title: {
                                display: true,
                                text: '时间'
                            },
                            ticks: {
                                maxRotation: 45,
                                minRotation: 45
                            }
                        },
                        y: {
                            title: {
                                display: true,
                                text: '电压'
                            },
                            beginAtZero: false
                        }
                    }
                }
            });
        }

        // 页面加载时用初始数据生成图表
        const initialData = {{ orders|tojson }};
        renderVoltageChart(initialData);

        $('#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(response)
                    {
                        // 更新表格内容
                        $('#purchase_order').html(response.htmlresponse);
                        // 更新图表内容
                        renderVoltageChart(response.data);
                    }
                });
            }
            else
            {
                alert("请选择日期范围");
            }
        });
    });
    </script>
</body>
</html>

3. 保持 response.html 不变 #

无需修改该文件，它仍负责渲染过滤后的表格内容。

功能说明 #

• 页面加载时自动用全部数据生成表格和图表
• 选择日期范围点击「Range」后，表格和图表会同步更新为过滤后的数据
• 图表采用折线图展示，X轴为完整的日期+时间，Y轴为电压值，支持响应式布局

内容的提问来源于stack exchange，提问作者user20759431