使用Flask+HTML从SQL生成表格无数据问题求助

核心问题 #

1. JavaScript JSON解析语法错误：原代码中JSON.parse("{JSON_data}")会导致引号冲突——json.dumps生成的JSON字符串包含双引号，替换后变成JSON.parse("[{"worker_id":1,...}]")，外层双引号与内部双引号冲突，JavaScript无法正常解析数据。
2. 表头拼写错误：HTML表头中wokrer_name多了一个字母错误，应为worker_name（虽不影响数据加载，但会导致表头显示不一致）。
3. 手动HTML文件替换不规范：直接读取HTML文件替换占位符的方式容易出问题，Flask自带的模板引擎更适合这类场景。

修复方案（保留原JavaScript生成表格的逻辑） #

修改后的HTML代码

<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">

<style>
    th{ 
        color:#fff;
    }
</style>

<table class="table table-striped">
    <tr class="bg-info">
        <th>worker_id</th>
        <th>worker_name</th> <!-- 修复拼写错误 -->
        <th>worker_type</th>
        <th>regular_hours</th>
        <th>hourly_rate</th>
        <th>overtime_rate</th>
    </tr>
    <tbody id="myTable"></tbody>
</table>

<script>
    // 直接赋值JSON数据，无需JSON.parse（json.dumps已生成合法的JS数组格式）
    var myArray = {JSON_data};
    buildTable(myArray);

    function buildTable(data){
        var table = document.getElementById('myTable');
        for (var i = 0; i < data.length; i++){
            var row = `<tr>
                            <td>${data[i].worker_id}</td>
                            <td>${data[i].worker_name}</td>
                            <td>${data[i].worker_type}</td>
                            <td>${data[i].regular_hours}</td>
                            <td>${data[i].hourly_rate}</td>
                            <td>${data[i].overtime_rate}</td>
                      </tr>`;
            table.innerHTML += row;
        }
    }
</script>

修改后的Python代码

import json
from flask import Flask

@app.route("/view_worker")
def view_worker():
    # 用with语句更安全地读取文件
    with open("templates/view_worker.html") as f:
        page = f.read()
    cursor.execute("SELECT * FROM workers")
    data = cursor.fetchall()
    worker_data = []
    for row in data:
        worker = {
            "worker_id": row[0],
            "worker_name": row[1],
            "worker_type": row[2],
            "regular_hours": row[3],
            "hourly_rate": row[4],
            "overtime_rate": row[5]
        }
        worker_data.append(worker)
    worker_data = json.dumps(worker_data)
    page = page.replace("{JSON_data}", worker_data)
    return page

更简洁的方案（用Flask Jinja2模板直接渲染表格） #

适合新手，无需JavaScript，直接在HTML中用模板语法生成表格：

修改后的HTML模板（templates/view_worker.html）

<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">

<style>
    th{ 
        color:#fff;
    }
</style>

<table class="table table-striped">
    <tr class="bg-info">
        <th>worker_id</th>
        <th>worker_name</th>
        <th>worker_type</th>
        <th>regular_hours</th>
        <th>hourly_rate</th>
        <th>overtime_rate</th>
    </tr>
    <tbody>
        <!-- 用Jinja2循环直接渲染每一行 -->
        {% for worker in worker_data %}
        <tr>
            <td>{{ worker.worker_id }}</td>
            <td>{{ worker.worker_name }}</td>
            <td>{{ worker.worker_type }}</td>
            <td>{{ worker.regular_hours }}</td>
            <td>{{ worker.hourly_rate }}</td>
            <td>{{ worker.overtime_rate }}</td>
        </tr>
        {% endfor %}
    </tbody>
</table>

对应的Python代码

from flask import Flask, render_template

@app.route("/view_worker")
def view_worker():
    cursor.execute("SELECT * FROM workers")
    data = cursor.fetchall()
    worker_data = []
    for row in data:
        worker = {
            "worker_id": row[0],
            "worker_name": row[1],
            "worker_type": row[2],
            "regular_hours": row[3],
            "hourly_rate": row[4],
            "overtime_rate": row[5]
        }
        worker_data.append(worker)
    # 直接传递数据给模板，无需手动处理JSON
    return render_template("view_worker.html", worker_data=worker_data)

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