如何在HTML中添加日期输入框与站点选择框联动更新图表？

一、修复后端SQL查询的变量冲突问题 #

你的getData函数中SQL查询存在变量名冲突，where date = date会被数据库解析为字段等于自身，无法正确匹配传入的日期参数，同时改用参数化写法避免SQL注入风险：

def getData(station, date):
    with UseDatabase(app.config['dbconfig']) as cursor:
        # 参数化查询，解决变量冲突并防SQL注入
        select_query = "SELECT * FROM score_temperature_%s WHERE date = %s" % (station, date)
        cursor.execute(select_query)
        data = cursor.fetchall()
        # 增加无数据判断，避免索引越界报错
        if not data:
            return None, date, [], [], [], []
        idx, date, hours, T_model1, T_model2, T_obs = data[0]
    return idx, date, hours, T_model1, T_model2, T_obs

同时修正gm函数中变量名错误（T_bachir/T_arome应对应T_model1/T_model2），并移除后端不需要的fig.show()：

def gm(CPM='izg', date = datetime.date.today()):
    idx, date, hours, T_model1, T_model2, T_obs = getData(CPM, date)
    colors = ['rgb(180,0,0)', 'rgb(0,0,189)', 'rgb(67,67,67)']
    fig = go.Figure()

    # model1 = BACHIR
    fig.add_trace(go.Scatter(x=hours, y=T_model1, mode='lines', name='T_bachir', 
  line=dict(color=colors[0], width=2), connectgaps=True, ))

    # model2 = AROME
    fig.add_trace(go.Scatter(x=hours, y=T_model2, mode='lines', name='T_arome', 
  line=dict(color=colors[1], width=2), connectgaps=True, ))

    # Observed Temperature
    fig.add_trace(go.Scatter(x=hours, y=T_obs, mode='lines', name='T_obs', 
  line=dict(color=colors[2], width=2), connectgaps=True, ))

    graphJSON = json.dumps(fig, cls=plotly.utils.PlotlyJSONEncoder)    
    return graphJSON

二、更新Flask路由，支持接收日期参数 #

修改回调路由，同时获取站点和日期参数，并将前端传入的日期字符串转为date对象：

from datetime import datetime

@app.route('/callback', methods=['GET', 'POST'])
def cb():
    station = request.args.get('station')
    date_str = request.args.get('date')
    # 将前端YYYY-MM-DD格式的字符串转为date对象
    date = datetime.strptime(date_str, '%Y-%m-%d').date() if date_str else datetime.date.today()
    return gm(station, date) 

@app.route('/interactive')
def interactive():
    return render_template("chartsajax.html", graphJSON = gm())

三、修改前端HTML，添加日期选择器并更新回调逻辑 #

在页面中添加日期输入框，修改JS函数同时获取站点和日期值，发起请求时传递两个参数：

<!doctype html>
<html>
<head>
    <script src="https://cdn.plot.ly/plotly-latest.min.js"></script>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
    <script>
        function updateChart() {
            // 获取站点和日期的选中值
            const station = document.getElementById('fname').value;
            const date = document.getElementById('date-picker').value;
            $.getJSON({
                url: "/callback", 
                data: { 'station': station, 'date': date }, 
                success: function (result) {
                    Plotly.newPlot('chart', result, {staticPlot: true});
                }
            });
        }
    </script>
</head>

<body style="font-family:arial, sans-serif">
    <div class="container">
        <h1>Controle des températures </h1>
        <div class="text-container">
            <label for="fname">Choisissez le CPM</label>
            <select id="fname" onchange="updateChart()">
                <option value="izg">Inzegane</option>
                <option value="mass">Al-Massira</option>
                <option value="glm">Guelmime</option>
                <option value="sif">Sidi-Ifni</option>
                <option value="tan">Tan-Tan</option>
                <option value="trd">Taroudant</option>
                <option value="tiz">Tiznit</option>
            </select>

            <label for="date-picker" style="margin-left: 20px;">Choisissez la date</label>
            <!-- 设置默认日期为今天 -->
            <input type="date" id="date-picker" onchange="updateChart()" value="{{ datetime.date.today().isoformat() }}">
            
            <div id="chart" class="chart"></div>
        </div>
    </div>
</body>

<script>
    d = {{ graphJSON | safe }};
    Plotly.newPlot('chart', d, {});
</script>
</html>

四、额外注意事项 #

• 确保前端日期输入的格式为YYYY-MM-DD，与后端解析格式一致；
• 后端需导入datetime模块，避免类型转换报错；
• 可根据需求扩展无数据时的前端提示逻辑，提升用户体验。

内容的提问来源于stack exchange，提问作者Mohamed Ait Ahmed