1

Unfortunately it's possible to apply an invalid SQL where clause when using arcpy.MakeFeatureLayer_management. This can cause issues later in a script. For example, I created a feature layer and subsequently tried to apply Snap_edit to the layer, which caused IDLE to crash without throwing an error. The cause was a misspelled field name.

Is there an efficient way to check if an SQL was applied properly to a feature layer?

  • 1
    Syntactic analysis is used to verify that a buffer overflow exploit isn't being attempted, but semantic evaluation requires a round-trip to the database -- you'd need to execute a query through the layer (as in a cursor) to detect this. – Vince Dec 3 '15 at 16:21
  • 1
    The Make Feature Layer tool has an SQL expression builder and validator in ArcMap. Within arcpy the interface is not available and I don't know of any way to test it in arcpy without triggering an error during runtime. Typically I design query expression with ArcMap open and build them as a definition query in the source data layer to validate them, then paste the valid expression into arcpy. That avoids field name misspellings. However, I still have to be careful to pay attention to double and single quotes when I place the query within a quoted string python parameter. – Richard Fairhurst Dec 3 '15 at 16:31
2

Proper Python error checking using try catch should at least allow you to prevent a crash.

Apart from that, ArcPy offers the ValidateTableName and ValidateFieldName methods to do basic checks that can be used in code to possibly catch such errors, but note these tools DO NOT check for existing tables and fields, but allow you to test if a new supplied table or field name is valid in the context of the given geodatabase environment.

See this ArcGIS Help topic: Validating table and field names in Python

In case of the ValidateTableName method, you can see it actually modifies a supplied name to be valid, so in order to catch a mispelled table name, you would need to test the output of the method against the supplied table name. Again: this option doesn't check for existing tables and field names!

In order to test for the true presence of existing tables and fields, you could use the

arcpy.ListFeatureClasses

arcpy.ListTables

and

arcpy.ListFields

to do basic checks for validity.

1

You can actually do this very easily. If you have a layer object and you want to test if there are any selected features, you can use this function:

def verifySelection(lyr):
   return bool(arcpy.Describe(lyr).FIDSet)

If you are creating a new layer using make feature layer, you can simply check the count:

def verifyCount(lyr):
   return bool(int(arcpy.management.GetCount(lyr).getOutput(0)))

Take this example, where I have a zoning layer in ArcMap. I can test if anything was selected like this:

>>> where_good = "Zone_Code = 'C-1'"
>>> arcpy.management.SelectLayerByAttribute("Zoning", "NEW_SELECTION", where_good)
<Result 'Zoning'>
>>> def verifySelection(lyr):
...     return bool(arcpy.Describe(lyr).FIDSet)
...     
>>> verifySelection('Zoning')
True
>>> where_bad = "Zone_Code = 'not a real value'"
>>> arcpy.management.SelectLayerByAttribute("Zoning", "NEW_SELECTION", where_bad)
<Result 'Zoning'>
>>> verifySelection('Zoning')
False
>>> 

The FIDSet returns a semicolon delimited list of selected OID's. This is from after I ran the "where_good" query:

>>> print arcpy.Describe('Zoning').FIDSet
1; 2; 3; 4; 5

And you can do the same thing by making new feature layers from the zoning layer and calling the verifyCount() to see that there were actually output features.

  • These come close but I see some issues. First, a valid SQL expression can still return a feature layer with zero features if no features meet the criteria. Also, I was using GetCount initially but it returned the count of the feature class when a field name was misspelled in the SQL. – Emil Brundage Dec 3 '15 at 16:35
  • Yes, I thought you were checking for selected features or an output. In this case, @Marco_B has a good suggestion to parse out the SQL statement to look for valid field names. You can find the field names by splitting by spaces/proper field delimiters (assuming they are present?). This could get tricky as queries can be very different. Either way, the functions I showed are simple, but would at least flag invalid where clauses. – crmackey Dec 3 '15 at 18:36

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.