Hey Esri community,
I'm a novice python user trying to get my hands dirty with python toolboxes (.pyt) for ArcGIS pro, and I'm hoping to get some pointers.
I have a series of Python scripts for use in ArcMap that constitute a multi-criterion decision analysis toolbox, and I'm trying to adapt/compile them all into a python toolbox for ArcGIS pro. Through combing this forum and some help from ChatGPT, I've learned a lot about how to configure the parameters for the ArcPro GUI, but my lack of SQL knowledge is really blocking further progress in the execute portions of the toolbox.
A specific problem I have is with how the `arcpy.da.SearchCursor()` function works. I'm trying to work with it as though it's a python for loop, but I'm having a lot of trouble.
An example:
class WeightedSumScore(object):
def __init__(self):
"""Define the tool (tool name is the name of the class)."""
self.label = "Weighted Sum Score"
self.description = "Takes standardized data from a site attribute table and user-provided weights, performs a weighted-sum operation, and returns a score and a numerical ranking for each site."
self.canRunInBackground = False
def getParameterInfo(self):
"""Define parameter definitions"""
input_table = arcpy.Parameter(
displayName="Input Table",
name="input_table",
datatype="GPTableView",
parameterType="Required",
direction="Input")
fields = arcpy.Parameter(
displayName="Fields",
name="fields",
datatype="Field",
parameterType="Required",
direction="Input",
multiValue=True,
enabled=False)
fields.parameterDependencies = [input_table.name]
weights = arcpy.Parameter(
displayName="Weights",
name="weights",
datatype="Double",
parameterType="Required",
direction="Input",
multiValue=True)
score_field_name = arcpy.Parameter(
displayName="Score Field Name",
name="score_field_name",
datatype="GPString",
parameterType="Required",
direction="Input")
rank_field_name = arcpy.Parameter(
displayName="Rank Field Name",
name="rank_field_name",
datatype="GPString",
parameterType="Required",
direction="Input")
parameters = [input_table, fields, weights, score_field_name, rank_field_name]
return parameters
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."""
if parameters[0].altered:
parameters[1].enabled = True
def execute(self, parameters, messages):
"""The source code of the tool."""
input_table = parameters[0].valueAsText
fields = parameters[1].valueAsText
weights = parameters[2].values
# Create a list to store the weighted sum score for each site
weighted_sum_scores = []
arcpy.AddMessage(f"Fields = {fields}")
# Calculate the weighted sum score for each site
with arcpy.da.SearchCursor(input_table, fields) as cursor: #<--error
for row in cursor:
weighted_sum_score = 0
for i, value in enumerate(row):
weighted_sum_score += value * weights[i]
weighted_sum_scores.append(weighted_sum_score)
# Sort the weighted sum scores and create a list of rankings
rankings = [i+1 for i in sorted(range(len(weighted_sum_scores)), key=lambda x: weighted_sum_scores[x], reverse=True)]
# Return the weighted sum scores and rankings
return weighted_sum_scores, rankings
`RuntimeError: An invalid SQL statement was used. [Select OBJECTID, {my vars} FROM {my data layer}. `
I added (what I think is) a print statement to try to verify that my fields were populated, and a similar error is raised. I'm not sure how to interpret this error.
If you're the kind of knowledgeable developer with lots of free time (lol, I know), please find the kindness in your heart to glance at my GitHub repository. The issue tickets hold each specific tool I want to make, as well as their old ArcMap scripts.
One thing I would check is on line 70, "fields" must be a python list. I can't tell how it is returned on line 62 but I suspect it is a string with a comma-separated set of values and not a true python list - just a guess
I concur. I see you already have messaging to write the fields (line 67 of your code snippet). What is that output? Like @DonMorrison1 said, you'll need to turn it into a list field names (strings).
Don't think you'll see the message from the tool since this is all backend code. You can output it to a txt though just as simple:
with open(r'path to a folder.txt', 'w') as fle:
fle.write(fields)
For it saying that an invalid SQL is used, but you are not setting the sql in SearchCursor points to something in the fields variable not being a true list and is placing its values into the where clause and sql positions in the cursor.