Python提取HTML表格行插入SQL表时的重复插入问题求助

我有一个带表格的HTML页面，用来展示信息供用户编辑。现在遇到个问题：把HTML表格行插入SQL表时，测试应该插入4行，但实际插了5行，而且全是第一行的重复值。我知道是for循环写错了，但一直没搞定，求帮忙。

原HTML代码 #

<body>
        <h1>Edit Form</h1>
        <form action="#" method="POST">
            <form action="/submit" method="post">
                <table>
                    <thead>
                        <tr>
                            <th>BOL No</th>
                            <th>Qty</th>
                            <th>Heat</th>
                            <th>ITEM DESC</th>
                            <th>WEIGHT</th>
                        </tr>
                    </thead>
                    <tbody>
                        {% for row in data %}
                        <tr>
                             <td><input type="text" name="BOLno" value="{{ BOLno }}"></td>
                            <td><input type="number" name="SERLTQTY" value="{{ row.SERLTQTY }}"></td>
                            <td><input type="text" name="SERLTNUM" value="{{ row.SERLTNUM }}"></td>
                            <td><input type="text" name="ITEMDESC" value="{{ row.ITEMDESC }}"></td>
                            <td><input type="number" name="WEIGHT" value="{{ row.WEIGHT }}"></td>
                        </tr>
                        {% endfor %}
                    </tbody>
                </table>
                <button type="submit">Submit</button>
        </form>
    </body>

原Python代码 #

@views.route('/edit/<bol>', methods=['POST', 'GET'])
def editbol(bol):
    sql_conn = odbc.connect('DRIVER=xxxxxxxxxxxxx;SERVER=xxxxxx;DATABASE=xxxxxxx;UID=xxxxxxx;PWD=xxxxxxx;')
    csr = sql_conn.cursor()
    BOLno = None
    SERLTQTY = None
    ITEMDESC = None
    WEIGHT = None
    if request.method == 'GET':
        csr.execute(f"select * from BOL_VIEW where bolno = {bol}")
        data = csr.fetchall()
        return render_template('edit.html', data = data, BOLno = bol)
    elif request.method == 'POST':
        formdata = []
        for x in request.form:
            BOLno = request.form['BOLno']
            SERLTQTY = request.form['SERLTQTY']
            SERLTNUM = request.form['SERLTNUM']
            ITEMDESC = request.form['ITEMDESC']
            WEIGHT = request.form['WEIGHT']
            formdata.append((BOLno, SERLTQTY, SERLTNUM, ITEMDESC, WEIGHT ))
        for line in formdata:
            csr.execute("INSERT INTO BOL_HIST_Rev2 (BOLno, SERLTQTY, SERLTNUM, ITEMDESC, WEIGHT) VALUES (?, ?, ?, ?, ?)", (BOLno, SERLTQTY, SERLTNUM, ITEMDESC, WEIGHT))
            sql_conn.commit()
        csr.close()
        sql_conn.close()
        return f'<h1>{bol} insert</h1>'
    else:
        return f'<h1>{bol} end</h1>'

核心问题点 #

1. 前端字段命名重复：所有行的输入框使用相同name，提交后后端只能获取单一行的值，无法区分各行数据。
2. 后端循环逻辑错误：遍历request.form的键会导致循环次数等于字段数量而非表格行数，每次循环取同一个值，最终插入重复数据。
3. 嵌套表单无效：HTML中嵌套两个<form>标签，不符合规范，会导致提交行为异常。

修正后的HTML代码 #

<body>
    <h1>Edit Form</h1>
    <!-- 移除嵌套form，保留单个有效表单 -->
    <form action="/edit/{{ BOLno }}" method="post">
        <table>
            <thead>
                <tr>
                    <th>BOL No</th>
                    <th>Qty</th>
                    <th>Heat</th>
                    <th>ITEM DESC</th>
                    <th>WEIGHT</th>
                </tr>
            </thead>
            <tbody>
                {% for row in data %}
                <tr>
                    <!-- 将name改为数组格式，后端通过getlist获取所有行数据 -->
                    <td><input type="text" name="BOLno[]" value="{{ BOLno }}"></td>
                    <td><input type="number" name="SERLTQTY[]" value="{{ row.SERLTQTY }}"></td>
                    <td><input type="text" name="SERLTNUM[]" value="{{ row.SERLTNUM }}"></td>
                    <td><input type="text" name="ITEMDESC[]" value="{{ row.ITEMDESC }}"></td>
                    <td><input type="number" name="WEIGHT[]" value="{{ row.WEIGHT }}"></td>
                </tr>
                {% endfor %}
            </tbody>
        </table>
        <button type="submit">Submit</button>
    </form>
</body>

修正后的Python代码 #

@views.route('/edit/<bol>', methods=['POST', 'GET'])
def editbol(bol):
    sql_conn = odbc.connect('DRIVER=xxxxxxxxxxxxx;SERVER=xxxxxx;DATABASE=xxxxxxx;UID=xxxxxxx;PWD=xxxxxxx;')
    csr = sql_conn.cursor()
    
    if request.method == 'GET':
        # 修复SQL注入风险：使用参数化查询替代字符串拼接
        csr.execute("select * from BOL_VIEW where bolno = ?", (bol,))
        data = csr.fetchall()
        return render_template('edit.html', data=data, BOLno=bol)
    
    elif request.method == 'POST':
        # 通过getlist获取所有行的字段值，得到数组格式结果
        bol_numbers = request.form.getlist('BOLno[]')
        qtys = request.form.getlist('SERLTQTY[]')
        nums = request.form.getlist('SERLTNUM[]')
        descs = request.form.getlist('ITEMDESC[]')
        weights = request.form.getlist('WEIGHT[]')
        
        # 遍历组合每一行的完整数据
        formdata = []
        for i in range(len(bol_numbers)):
            formdata.append((
                bol_numbers[i],
                qtys[i],
                nums[i],
                descs[i],
                weights[i]
            ))
        
        # 批量插入提升效率
        csr.executemany(
            "INSERT INTO BOL_HIST_Rev2 (BOLno, SERLTQTY, SERLTNUM, ITEMDESC, WEIGHT) VALUES (?, ?, ?, ?, ?)",
            formdata
        )
        sql_conn.commit()
        
        csr.close()
        sql_conn.close()
        return f'<h1>{bol} 插入完成</h1>'
    
    else:
        return f'<h1>{bol} 请求结束</h1>'

关键修正说明 #

1. 前端字段命名：将name="BOLno"改为name="BOLno[]"，让表单提交时以数组形式传递所有行的对应字段值。
2. 后端数据处理：用request.form.getlist()获取字段数组，通过索引遍历组合每行数据，确保字段对应正确。
3. 批量插入优化：使用executemany替代循环执行execute，减少数据库交互次数，提升性能。
4. SQL注入修复：GET请求的SQL查询改为参数化写法，避免注入风险。
5. 移除无效嵌套表单：删除外层冗余<form>标签，保证提交行为符合规范。

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