如何使用Python（Flask/MySQL）实现服务端DataTables的编辑删除功能

1. 后端新增编辑、删除接口 #

在现有Flask代码中新增两个路由，分别处理数据更新和删除请求：

# 编辑数据接口
@app.route("/edit_record", methods=["POST"])
def edit_record():
    try:
        cursor = mysql.connection.cursor(MySQLdb.cursors.DictCursor)
        # 接收前端传递的参数
        record_id = request.form['id_ates']
        tes1 = request.form['tes1']
        tes2 = request.form['tes2']
        tes3 = request.form['tes3']
        
        # 执行更新语句
        cursor.execute("""
            UPDATE ates 
            SET tes1 = %s, tes2 = %s, tes3 = %s 
            WHERE id_ates = %s
        """, (tes1, tes2, tes3, record_id))
        mysql.connection.commit()
        
        return jsonify({"success": True, "msg": "更新成功"})
    except Exception as e:
        mysql.connection.rollback()
        return jsonify({"success": False, "msg": f"更新失败：{str(e)}"})
    finally:
        cursor.close()

# 删除数据接口
@app.route("/delete_record", methods=["POST"])
def delete_record():
    try:
        cursor = mysql.connection.cursor(MySQLdb.cursors.DictCursor)
        record_id = request.form['id_ates']
        
        cursor.execute("DELETE FROM ates WHERE id_ates = %s", (record_id,))
        mysql.connection.commit()
        
        return jsonify({"success": True, "msg": "删除成功"})
    except Exception as e:
        mysql.connection.rollback()
        return jsonify({"success": False, "msg": f"删除失败：{str(e)}"})
    finally:
        cursor.close()

2. 前端新增编辑模态框、修改DataTables配置 #

2.1 新增编辑弹窗HTML代码 #

在现有页面HTML中加入编辑用的模态框：

<!-- 编辑模态框 -->
<div class="modal fade" id="editModal" tabindex="-1" role="dialog">
  <div class="modal-dialog" role="document">
    <div class="modal-content">
      <div class="modal-header">
        <h5 class="modal-title">编辑数据</h5>
        <button type="button" class="close" data-dismiss="modal" aria-label="Close">
          <span aria-hidden="true">&times;</span>
        </button>
      </div>
      <div class="modal-body">
        <input type="hidden" id="edit_id">
        <div class="form-group">
          <label>tes1</label>
          <input type="text" class="form-control" id="edit_tes1">
        </div>
        <div class="form-group">
          <label>tes2</label>
          <input type="text" class="form-control" id="edit_tes2">
        </div>
        <div class="form-group">
          <label>tes3</label>
          <input type="text" class="form-control" id="edit_tes3">
        </div>
      </div>
      <div class="modal-footer">
        <button type="button" class="btn btn-primary" id="saveEditBtn">保存</button>
        <button type="button" class="btn btn-secondary" data-dismiss="modal">关闭</button>
      </div>
    </div>
  </div>
</div>

2.2 修改JS代码，添加操作列和交互逻辑 #

修改原有DataTables初始化代码，增加操作列渲染、编辑删除交互：

$(document).ready(function() {
    // 保存DataTables实例，方便后续刷新
    var table = $('#datatables').DataTable({
        "processing": true,
        "serverSide": true,
        "ajax": {
            "url": "{{ url_for('ajaxfiletest') }}",
            "type": 'POST'
        },
        "columns": [
            {"data": "id_ates"},
            {"data": "tes1"},
            {"data": "tes2"},
            {"data": "tes3"},
            {
                "data": null,
                "render": function(data, type, row) {
                    // 渲染编辑、删除按钮，绑定行数据id
                    return `<button class="edit-btn btn btn-sm btn-info" data-id="${row.id_ates}" data-tes1="${row.tes1}" data-tes2="${row.tes2}" data-tes3="${row.tes3}">编辑</button> 
                            <button class="delete-btn btn btn-sm btn-danger" data-id="${row.id_ates}">删除</button>`;
                }
            }
        ]
    });

    // 编辑按钮点击事件
    $('#datatables').on('click', '.edit-btn', function() {
        // 把行数据填充到模态框
        $('#edit_id').val($(this).data('id'));
        $('#edit_tes1').val($(this).data('tes1'));
        $('#edit_tes2').val($(this).data('tes2'));
        $('#edit_tes3').val($(this).data('tes3'));
        $('#editModal').modal('show');
    });

    // 保存编辑事件
    $('#saveEditBtn').click(function() {
        $.post("{{ url_for('edit_record') }}", {
            id_ates: $('#edit_id').val(),
            tes1: $('#edit_tes1').val(),
            tes2: $('#edit_tes2').val(),
            tes3: $('#edit_tes3').val()
        }, function(res) {
            if(res.success) {
                alert(res.msg);
                $('#editModal').modal('hide');
                table.ajax.reload(); // 刷新表格
            } else {
                alert(res.msg);
            }
        })
    })

    // 删除按钮点击事件
    $('#datatables').on('click', '.delete-btn', function() {
        var recordId = $(this).data('id');
        if(confirm('确认删除该条数据？')) {
            $.post("{{ url_for('delete_record') }}", {id_ates: recordId}, function(res) {
                if(res.success) {
                    alert(res.msg);
                    table.ajax.reload();
                } else {
                    alert(res.msg);
                }
            })
        }
    })
});

3. 依赖说明 #

如果用到了Bootstrap模态框，需要提前引入Bootstrap的CSS和JS文件，没有的话可以自行替换成原生弹窗表单实现。

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