Using SelectByAttribute_management after making XY Event layer in ArcPy

Question

I imported a CSV file using the MakeXYEventLayer_management and trying to use the SelectlayerByAttribute_management function, but I am getting error message saying on "Object ID(OID)" is present.. Is there a way to work around this?

Below is my code so far:

import arcpy
arcpy.env.overwriteOutput = True

xFieldName = 'LON'
yFieldName = 'LAT'
outFolder = "C:\\GEOG485\\FinalProject\\Output"
asos = "ASOS Stations"
spatialRef = arcpy.SpatialReference(4326)
csvFilePath = "C:\\GEOG485\\FinalProject\\isd-history.csv"
targetState = 'NJ'

arcpy.MakeXYEventLayer_management(csvFilePath, xFieldName, yFieldName, asos, spatialRef)
arcpy.FeatureClassToShapefile_conversion(asos, outFolder)

arcpy.SelectLayerByAttribute_management(asos, "NEW_SELECTION", '"STATE_NAME" = targetState')

Answer 1

As discussed in Making XY Event Layer into Copy Feature layer using ArcPy?, you need to select from the feature class (or a layer based on that feature class, actually), not on the event layer. Try something like the version below to create the new layer from a temporary in-memory feature class, and then the select uses that new layer instead of the event layer.

import arcpy
arcpy.env.overwriteOutput = True

xFieldName = 'LON'
yFieldName = 'LAT'
outFolder = "C:\\GEOG485\\FinalProject\\Output"
eventLayer = "ASOS"
spatialRef = arcpy.SpatialReference(4326)
csvFilePath = "C:\\GEOG485\\FinalProject\\isd-history.csv"
targetState = 'NJ'
featureClassLayer = "fc_layer"

arcpy.MakeXYEventLayer_management(csvFilePath, xFieldName, yFieldName, eventLayer, spatialRef)
arcpy.FeatureClassToFeatureClass_conversion(eventLayer, "in_memory", "tmpFeatureClass")
arcpy.MakeFeatureLayer("in_memory/tmpFeatureClass", featureClassLayer)
arcpy.SelectLayerByAttribute_management(featureClassLayer, "NEW_SELECTION", "STATE_NAME = '{}'".format(targetState))

I've moved your targetState out of the string so that it's not considered as a string literal, but as a variable to be included in the string - and put it's placeholder within single quotes. I have also renamed your asos variable to eventLayer and its value to 'ASOS' as I think this makes the variables' uses clearer, and the value is more suitable for use as a shapefile name (which will be derived from the event layer name in this case.)

Depending on what you're planning to do with the selected features, you may not even need to do a select. You can create the new layer with the same 'where' clause that you use in the select to have the layer only include those matching features. Then you don't need to do a select at all. If this suits your purposes, you could replace the last two lines above with the single line:

arcpy.MakeFeatureLayer("in_memory/tmpFeatureClass", featureClassLayer, where_clause="STATE_NAME = '{}'".format(targetState))

If your script needs to continue other work later, you may want to do the following at some point, to free up memory after the temporary feature class and its layer are no longer required for the rest of the script:

arcpy.Delete_management(featureClassLayer)
arcpy.Delete_management("in_memory/tmpFeatureClass")