I am using ArcGis Pro 2.4 python window. I have seen suggestions of using "SearchCursor" but am unclear how to utilize this tool in the Where-Clause in my SQL selection statement to select by CPAName_X and AlcInv = 'T'. I need help with creating a statement for my selection that iterates through each CPA Name so that I do not have to write the selection 12 times per crash type.

Here is my code so far:

import arcpy

#Set Workspace:
arcpy.env.workspace = 'N:\AGISFILE\PROJECTS\CompPlanCPAMetrics\CompPlanMetrics\CrashData_ID0612041\CrashData_ID0612041_2019_0626\CrashData_ID0612041_2019_0626.gdb'

#Input Feature Class containing the data for selection and field calculation
CrashData = "JOIN_CrashDataByCPA_2010_2016"
CPALayerCalculate = "CPAs_2016CrashesTotaled"

#Lists to Use to iterate through in selection statement
CPAName = ["Central ABQ","East Gateway", "Foothills", "Mesa del Sol", "Mid Heights", "Near Heights", "Near North Valley", "North Albuquerque", "North I-25", "Northwest Mesa", "Southwest Mesa", "West Mesa"]
CrashType = ["ALCInv", "DRUGInv","PEDInv","PECInv"]

#Select crash data By CPA from CrashData
SelectField = "CPAName" #groups all records together for selection by CPA

#Make Table View
CrashView = arcpy.MakeTableView_management(CrashData,"CrashTableView")

#SQL and Selection: Total Crashes
ListTotal = []
for i in CPAName:
    SelectStatement = "{0} = '{1}'".format(arcpy.AddFieldDelimiters(CrashData, SelectField),CPAName[i])
    SelectCPA = arcpy.SelectLayerByAttribute_management(CrashView, "New Selection", SelectStatement)
    TotalSelected = int(str(arcpy.GetCount_management(SelectCPA)))

ListTotal1 = []
for i in CPAName:
#SQL and selection: Total Crashes By Type per CPA: add and part of sql
    SelectStatement1 = "{0} = '{1}' And {2}= 'T'".format(arcpy.AddFieldDelimiters(CrashData, SelectField),CPAName[i],CrashType[0]) 
    SelectCPA = arcpy.SelectLayerByAttribute_management(CrashView, "New Selection", SelectStatement1)
    TotalSelected2 = int(str(arcpy.GetCount_management(SelectCPA)))

Alternatively, this code iterates but does not select so I get a list with [1831,0,0,0,0,0,0,0,0,0,0,0] as a result.

ListTotalAlcInv = []
for i in CPAName:
    SQL1 = "CPAName = 'i' and ALCInv = 'T'"
    SelectCPASQL1 = arcpy.SelectLayerByAttribute_management(CrashView, "New Selection", SQL1)
    NumSelected = int(str(arcpy.GetCount_management(SelectCPASQL1)))
  • What are you trying to do here? This is not an SQL statement as indicated by the comment above the line: SelectCPASQL = arcpy.SelectLayerByAttribute_management(CrashView, "New Selection", SQL, invert_where_clause = "") – RGfromRWBJV Jul 3 '19 at 19:51
  • You need to escape or raw your path arcpy.env.workspace = 'N:\... change to arcpy.env.workspace = r'N:\... to make the string raw, otherwise each character following the backslash is escaped. – Michael Stimson Jul 3 '19 at 22:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.