Custom python toolbox as a geoprocessing service fails

GrantHaynes — Thu, 24 Feb 2022 16:10:15 GMT

Howdy,

I have a very simple python toolbox that takes in an excel sheet that contains lithology information and a kml/kmz file that contains borehole locations, processes them and appends them to a specified point and table featureclass. The tool works great in pro, with no issues. However, I published the tool out to my arcgis portal, published a point and table layer to go with it, wrapped them all up in a web app and it fails to add any information to the web layers. Does anyone here know what's going wrong?

Code:

import arcpy, os, openpyxl, shutil, time

from arcgis.gis import GIS

from arcgis.features import FeatureLayerCollection

class Toolbox(object😞

def __init__(self😞

"""Define the toolbox (the name of the toolbox is the name of the

.pyt file)."""

self.label = "Toolbox"

self.alias = "toolbox"

# List of tool classes associated with this toolbox

self.tools = [BoreholeArchive]

class BoreholeArchive(object😞

def __init__(self😞

"""Define the tool (tool name is the name of the class)."""

self.label = " Simple Borehole Archive Tool"

self.description = ""

self.canRunInBackground = False

def getParameterInfo(self😞

"""Define parameter definitions"""

param0 = arcpy.Parameter(

displayName="Input KML/KMZ File",

name="input_kml",

datatype="DEFile",

parameterType="Optional",

direction="Input")

param1 = arcpy.Parameter(

displayName="Input excel file",

name="input_excel",

datatype="GPType",

parameterType="Required",

direction="Input")

param2 = arcpy.Parameter(

displayName="Borehole point featuer layer to append",

name="output_borehole_point",

datatype="DEFeatureClass",

parameterType="Optional",

direction="Input")

param3 = arcpy.Parameter(

displayName="Table to append",

name="output_lithology_table",

datatype="DETable",

parameterType="Required",

direction="Input")

params = [param0, param1, param2, param3]

return params

def isLicensed(self😞

"""Set whether tool is licensed to execute."""

return True

def updateParameters(self, parameters😞

"""Modify the values and properties of parameters before internal

validation is performed. This method is called whenever a parameter

has been changed."""

return

def updateMessages(self, parameters😞

"""Modify the messages created by internal validation for each tool

parameter. This method is called after internal validation."""

return

def execute(self, parameters, messages😞

kml_kmz_filepath = parameters[0].valueAsText

excel_filepath = parameters[1].valueAsText

output_fc = parameters[2].valueAsText

output_table = parameters[3].valueAsText

# Get folder paths, do any work relating to temp files here

arcpy.AddMessage("Starting")

cwd = arcpy.env.scratchFolder

temp_excel_file_path = os.path.join(cwd, "temp.xlsx")

if os.path.exists(temp_excel_file_path😞

os.remove(temp_excel_file_path)

kml_folder = os.path.join(cwd, "kml_conversion")

if os.path.exists(kml_folder) == False:

os.mkdir(kml_folder)

kml_items = os.listdir(kml_folder)

for kml_item in kml_items:

if ".gdb" in kml_item:

arcpy.Delete_management(os.path.join(kml_folder, kml_item))

else:

os.remove(os.path.join(kml_folder, kml_item))

temp_gdb = os.path.join(cwd, "borehole_temp.gdb")

if arcpy.Exists(temp_gdb😞

arcpy.Delete_management(temp_gdb)

# clean row 1 of the excel file

interval_column_names = ["0_5",

"5_10",

"10_15",

"15_20",

"20_25",

"25_30",

"30_35",

"35_40",

"40_45",

"45_50",

"50_55",

"55_60"]

# Create a copy of the input excel sheet

shutil.copy(excel_filepath, temp_excel_file_path)

wb1 = openpyxl.load_workbook(filename=excel_filepath)

ws1 = wb1.worksheets[0]

wb2 = openpyxl.load_workbook(filename=temp_excel_file_path)

ws2 = wb2.create_sheet(ws1.title)

for row in ws1:

for cell in row:

ws2[cell.coordinate].value = cell.value

wb2.save(temp_excel_file_path)

wb = openpyxl.load_workbook(temp_excel_file_path)

sheet = wb.active

i = 1

bid_tracker_no_column = 1

id_column = 2

# Clean column names

current_interval = ""

while i < sheet.max_column +1:

cell = sheet.cell(1, i)

column_name = cell.value

column_name = column_name.strip()

column_name = column_name.replace("'", "")

column_name = column_name.replace(".", "")

column_name = column_name.replace("-", "_")

column_name = column_name.replace(" ", "_")

column_name = column_name.replace("/", "_")

if column_name == "Bid_Tracker_No":

bid_tracker_no_column = i

if column_name == "ID":

id_column == i

if column_name in interval_column_names:

current_interval = column_name

if current_interval == column_name:

cell.value = "Int_{}".format(column_name)

elif current_interval == "":

cell.value = column_name

else:

cell.value = "Int_{}_{}".format(current_interval, column_name)

i += 1

# Run through the spreadsheet and create the foriegn keys

master_id_column = sheet.max_column +1

cell = sheet.cell(1, master_id_column)

cell.value = "Bid_Tracker_No_and_ID"

i = 2

while i < sheet.max_row + 1:

id_cell = sheet.cell(i, id_column)

bid_tracker_cell = sheet.cell(i, bid_tracker_no_column)

master_id_cell = sheet.cell(i, master_id_column)

if bid_tracker_cell.value and id_cell.value:

master_id_cell.value = "{}-{}".format(bid_tracker_cell.value, id_cell.value)

else:

arcpy.AddWarning("Bad Bid_Tracker_No_and_ID in lithology layer, replace 99999 values with correct value")

master_id_cell.value = 99999

i+=1

wb.save(temp_excel_file_path)

# Create a temp gdb to hold temp data

arcpy.management.CreateFileGDB(cwd, "borehole_temp.gdb")

arcpy.env.workspace = temp_gdb

# Convert the excel sheet into a gdb table

lithology_table_path = os.path.join(temp_gdb, "Lithology")

arcpy.conversion.ExcelToTable(temp_excel_file_path, lithology_table_path)

# Add the tracking fields

arcpy.AddField_management(lithology_table_path, "created_user", "TEXT")

arcpy.AddField_management(lithology_table_path, "created_date", "DATE")

arcpy.AddField_management(lithology_table_path, "last_edited_user", "TEXT")

arcpy.AddField_management(lithology_table_path, "last_edited_date", "Date")

# Update the online table

arcpy.Append_management(lithology_table_path, output_table, "NO_TEST")

arcpy.AddMessage("Lithology records added")

# If a kml filpath is given then make the kml/kmz into a gdb and pull out the

# needed information and push it into the temp gdbt

if kml_kmz_filepath != None:

arcpy.conversion.KMLToLayer(kml_kmz_filepath, kml_folder)

arcpy.AddMessage("KML/KMZ converted")

temp_folder_contents = os.listdir(kml_folder)

kmz_gdb = ""

for item in temp_folder_contents:

if 'gdb' in item:

kmz_gdb = os.path.join(kml_folder, item)

kml_points_fc = os.path.join(kmz_gdb, "Points")

# If the kml has been processed extract out the relevant information into a new point feature class

# in the temp geodatabase

if arcpy.Exists(kml_points_fc😞

new_points_fc = os.path.join(temp_gdb, "Boreholes")

arcpy.CreateFeatureclass_management(temp_gdb, "Boreholes", "POINT")

arcpy.CopyFeatures_management(kml_points_fc, new_points_fc)

arcpy.AddField_management(new_points_fc, "Bid_Tracker_No_and_ID", "TEXT")

arcpy.AddField_management(new_points_fc, "Bid_Tracker_No", "TEXT")

arcpy.AddField_management(new_points_fc, "ID", "TEXT")

arcpy.AddField_management(new_points_fc, "created_user", "TEXT")

arcpy.AddField_management(new_points_fc, "created_date", "DATE")

arcpy.AddField_management(new_points_fc, "last_edited_user", "TEXT")

arcpy.AddField_management(new_points_fc, "last_edited_date", "Date")

# Pull the information from Name and put it in Bid_Tracker_No_and_ID

with arcpy.da.UpdateCursor(new_points_fc, ["Name", "Bid_Tracker_No_and_ID"]) as cursor:

for row in cursor:

if row[0] != None:

row[1] = row[0]

else:

arcpy.AddWarning("Bad Bid_Tracker_No_and_ID in boreholes layer, replace 99999 values with correct value")

row[1] = 99999

cursor.updateRow(row)

del cursor

arcpy.AddMessage("Primary Keys Created")

# Delete unneeded fields

# new_points_fc_fields = arcpy.ListFields(new_points_fc)

# for field in new_points_fc_fields:

# if field.name not in ["OBJECTID", "Shape", "Bid_Tracker_No", "ID", "Bid_Tracker_No_and_ID", "created_user", "created_date", "last_edited_user", "last_edited_date"]:

# arcpy.DeleteField_management(new_points_fc, field.name)

# arcpy.AddMessage("Extra KML/KMZ fields removed")

# Since there was a kml update the web point layer

arcpy.Append_management(new_points_fc, output_fc, "NO_TEST")

arcpy.AddMessage("Borehole locations added")

else:

arcpy.AddWarning("No points found in kml/kmz, kml/kmz not processed")

arcpy.AddMessage("Complete")

time.sleep(10)

return

What it looks like in WAB pre run:

After running:

topic Custom python toolbox as a geoprocessing service fails in ArcGIS Web AppBuilder Questions

Custom python toolbox as a geoprocessing service fails