issue with creating of the where_clause in ArcPy

78
3
12-03-2021 10:16 AM
AlfonsZitterbacke
New Contributor II

I would like to create a query in ArcPy without creating a temporary additional attribute first. Unfortunately I fail with the syntax for creating the expression. I want to check if a ratio of two existing attributes (RCT_WIDTH_MBG_Length_REWI and RCT_WIDTH_MBG_Width_REWID) falls below a certain value.

My latest code is:

 

    expression = '!RCT_WIDTH_MBG_Length_REWI! / !RCT_WIDTH_MBG_Width_REWID! > 3' 
    arcpy.SelectLayerByAttribute_management('Clean_LYR', 'NEW_SELECTION', expression)
    if int(arcpy.GetCount_management('Clean_LYR')[0]) > 0:
        arcpy.DeleteFeatures_management('Clean_LYR')

 

and during the script run I code the error message:

 

 

ExecuteError: ERROR 000358: Invalid expression

 

 My question is, how will be the right syntax of the expression?

0 Kudos
3 Replies
Tomasz_Tarchalski
New Contributor III

try:

- set expression_type to PYTHON3 or PYTHON_9.3

- convert your inputs into integer (int(!RCT_WIDTH_MBG_Length_REWI!) 

Finally, run that in arcmap/ or in PRo manually, go to history and see how it was written (copy python snippet).

JoeBorgione
MVP Esteemed Contributor

Your expression needs to be SQl compatible, not arcpy or python compatible.  I think the !FieldName! references are what is sinking your ship.  Here are a couple of examples of select expressions I have used in the past:

 

#UNIQUE_ID is a Character Type of Field...

expression = "UNIQUE_ID = '0' Or UNIQUE_ID IS NULL"

expression = 'CAST(UNIQUE_ID as INT) >= 15000000'

 

One way to test selection expressions with the the select tool.  Here I am using ArcGIS Pro with  two numeric fields:

JoeBorgione_0-1638560521006.png

 

 

That should just about do it....
DominicRoberge2
Occasional Contributor

Hey Alfons

below is a piece of code that is similar that what you are trying to achieve.

1) Make sure you are using the FIELD Name and not the aliases

2) make you your formula returns valid values (no division /0...)

That should works! Good luck!

 

    arcpy.MakeFeatureLayer_management(sdeFCT, MFL,"TOTPOP10 >0 and ALAND10 >0")
    print(arcpy.GetCount_management(MFL))

    expression = '("ALAND10"/"TOTPOP10") > 1000'
    print(expression)
    arcpy.SelectLayerByAttribute_management(MFL, 'NEW_SELECTION', where_clause=expression)
    if int(arcpy.GetCount_management(MFL)[0]) > 0:
        print("delete records")
        ##arcpy.DeleteFeatures_management(sdeFCT)
    else:
        print("good to go")

 

0 Kudos