Custom python toolbox as a geoprocessing service fails
426
0
02-24-2022 08:10 AM
by
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:
