1

I have a feature class with three fields of interest EV_CODE (text), TREE_SIZE_CODE (text) and CANOPY_COVER (number). I needed to find all possible combinations of those three attributes within the data set and then extract each possible combination as its own feature class.

I am creating a Python script that uses arcpy summary statistics to first create the table of all possible combinations (there are 158 possibilities in this data set). The values in the table all seem to be Unicode type (I don't know if this is also a problem, but it might be). Then I would like to use a SearchCursor with the table to create a SQL query for each row to select each possible combination from the feature class and export as its own feature class and a name that reflects the attributes selected.

The script works until it gets to the SQL query at the end where I keep getting ERROR 000358 that I have an invalid expression. I have included the error message below the script. Does anyone have a suggestion to fix the code so that the script will run?

# Find all possible combinations of three fields and select those areas to
# export as unique feature classes

# Import arcpy and set workspace
import arcpy
from arcpy import env
env.workspace = "C:/Users/astratton/Documents/Assignments/X_SideProject/TroutCreek/FSVeg_Combinations.gdb"
env.overwriteOutput = True

# Feature class
fc = "FSVegSpR06ExtractVegSIMPLIFIED"

# Identify fields of interest
field1 = "EV_CODE"
field2 = "TREE_SIZE_CODE"
field3 = "CANOPY_COVER"

# Use summary statistics cases to create table of unique cases
out_table = "Cases"
statistics_fields = [["OBJECTID", "COUNT"]]
case_field = field1, field2, field3
CasesTable = arcpy.Statistics_analysis(fc, out_table, statistics_fields, case_field)

# Add field delimiters to feature class fields
field1fc = arcpy.AddFieldDelimiters(fc, "EV_CODE")
field2fc = arcpy.AddFieldDelimiters(fc, "TREE_SIZE_CODE")
field3fc = arcpy.AddFieldDelimiters(fc, "CANOPY_COVER")

# Explore using cursor for Cases table to populate the SQL query
cursor = arcpy.da.SearchCursor(CasesTable, [field1, field2, field3])
for row in cursor:
    var1 = "'" + str(row[0]) + "'"
    var2 = "'" + str(row[1]) + "'"
    var3 = str(row[2])

    var1a = str(row[0])
    var2a = str(row[1])
    var3a = str(row[2])   

    SQL = field1fc + " = " + var1 + " AND " + field2fc + " = " + var2 + " AND " + field3fc + " = " + var3
##    print SQL
    arcpy.Select_analysis(fc, "Unique_" + var1a + "_" + var2a + "_" + var3a, SQL)


Error Message...    
Traceback (most recent call last):
  File "C:\Python27\ArcGIS10.3\Lib\site-packages\pythonwin\pywin\framework\scriptutils.py", line 325, in RunScript
    exec codeObject in __main__.__dict__
  File "C:\Users\astratton\Documents\Assignments\X_SideProject\TroutCreek\Python\FSVeg_Combinations_searchCursorTodayE.py", line 42, in <module>
    arcpy.Select_analysis(fc, "Unique_" + var1a + "_" + var2a + "_" + var3a, SQL)
  File "C:\Program Files (x86)\ArcGIS\Desktop10.3\ArcPy\arcpy\analysis.py", line 84, in Select
    raise e
ExecuteError: ERROR 000358: Invalid expression "EV_CODE" = 'None' AND "TREE_SIZE_CODE" = 'None' AND "CANOPY_COVER" = None
Failed to execute (Select).
  • 2
    Please edit the question to contain diagnostic print statements with the expression values. – Vince Jan 4 '17 at 0:25
  • 2
    The best way to debug is create a valid SQL expression in Desktop, and add a print statement like @Vince said before Select -- compare the two values and adjust accordingly until they are identical. – Paul Jan 4 '17 at 2:30
1

There are a couple of interesting things happening with your SQL statements and search cursor. You were right to assume that part of your problem arises from the unicode to string issue. It appears that when your search cursor encountered a NULL in the summary statistic table, the string conversion of that NULL via str() transformed it into None, which you then wrapped in single quotes. SQL queries don't recognize = None as a valid query and they certainly don't recognize the string version of = 'None'. SQL queries use IS NULL for this case. To solve this problem, you need logic to detect null values and build your SQL query accordingly. This is just one of several ways to do this:

if var1 == 'None':
    var1a = ' IS NULL'
else: 
    var1a = " = " + var1
if var2 == 'None':
    var2a = ' IS NULL'
else: 
    var2a = " = " + var2
if var3 == None:
    var3a = ' IS NULL'
else: 
    var3a = " = " + var3

SQL = field1fc + var1a + " AND " + field2fc + var2a + " AND " + field3fc +  var3a

Another potential source of problems depends on where you stored the summary statistics table. If it's in a file geodatabase, then you don't need quotes around your field names (e.g. "EV_CODE" v. EV_CODE). The syntax needed to reference fields will change depending if your table is stored in a .gdb, .mdb, or as a stand alone table.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.