Select to view content in your preferred language

Developing a python toolbox for multi decision criteria analysis

868
3
04-07-2023 12:21 PM
fiendskrah
New Contributor

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.

0 Kudos
3 Replies
DonMorrison1
Frequent Contributor

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

BlakeTerhune
MVP Regular Contributor

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).

SearchCursor—ArcGIS Pro | Documentation

0 Kudos
by Anonymous User
Not applicable

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.

0 Kudos