topic Re: Issue: Invalid Expression Failed to Execute (SelectLayerByAttribute) in Python Questions

Issue: Invalid Expression Failed to Execute (SelectLayerByAttribute)

KioshiMishiro — Wed, 24 Jul 2024 17:59:29 GMT

I have used ArcGIS Desktop 10.8.2, and I have an issue running the script with an error message about SQL expression in the script written in the title. I would appreciate it if you could support fixing this.

import arcpy # Define the input and output paths input_shapefile = r"C:\...\..shp" output_folder = r"C:\...\(folder)" # Define the field name that contains unique IDs unique_id_field = 'AreaKey' # Create a feature layer from the input shapefile arcpy.MakeFeatureLayer_management(input_shapefile, "temp_layer") # Get a list of unique IDs from the shapefile unique_ids = set([row[0] for row in arcpy.da.SearchCursor(input_shapefile, [unique_id_field])]) # Iterate through each unique ID for unique_id in unique_ids: # Define the SQL query to select the feature by unique ID query = '"{unique_id_field}" = {unique_id}' # Select the feature using the query arcpy.SelectLayerByAttribute_management("temp_layer", "NEW_SELECTION", query) # Check if the selection contains any features selected_count = int(arcpy.GetCount_management("temp_layer").getOutput(0)) if selected_count > 0: # Define the output shapefile path output_shapefile = "{output_folder}\\{unique_id}.shp" # Export the selected feature(s) to a new shapefile arcpy.CopyFeatures_management("temp_layer", output_shapefile) print("Successfully exported {selected_count} feature(s) with ID {unique_id} to {output_shapefile}") else: print("No features found with the unique ID {unique_id}.") # Clean up the temporary layer arcpy.Delete_management("temp_layer")

Re: Issue: Invalid Expression Failed to Execute (SelectLayerByAttribute)

CodyPatterson — Wed, 24 Jul 2024 18:16:38 GMT

Hey @KioshiMishiro

Looking into this now, but I do see something that could cause an issue. In your where clause, attempt to swap the double quotes with single quotes, like this:

query = "'{unique_id_field}' = {unique_id}"

If this works, let me know!

Cody

Re: Issue: Invalid Expression Failed to Execute (SelectLayerByAttribute)

KioshiMishiro — Wed, 24 Jul 2024 18:24:44 GMT

Thank you very much @CodyPatterson, but still have same issue like below:

[...]

Executing: SelectLayerByAttribute temp_layer NEW_SELECTION "'{unique_id_field}' = {unique_id}"
Start Time: Wed Jul 24 13:17:24 2024
ERROR 000358: Invalid expression
An invalid SQL statement was used.
An invalid SQL statement was used.
Failed to execute (SelectLayerByAttribute).
Failed at Wed Jul 24 13:17:24 2024 (Elapsed Time: 0.03 seconds) [...]

Re: Issue: Invalid Expression Failed to Execute (SelectLayerByAttribute)

JoshuaBixby — Wed, 24 Jul 2024 18:48:20 GMT

A few potential issues. Firstly, why all the curly brackets? If you are trying to use Python string formatting, you are missing calling the format function itself. Assuming the unique_id is a number and not a string, try

query = '"{}" = {}'.format(unique_id_field, unique_id)

Re: Issue: Invalid Expression Failed to Execute (SelectLayerByAttribute)

KioshiMishiro — Wed, 24 Jul 2024 19:02:36 GMT

That is not a number, but it is a string.

Re: Issue: Invalid Expression Failed to Execute (SelectLayerByAttribute)

HaydenWelch — Wed, 24 Jul 2024 20:26:13 GMT

In Python 3, the f string syntax is actaully preferred now:

query = f"'{unique_id_field}' = {unique_id}"

they make it easier to keep track of what is being inserted where because your eyes don't have to keep darting back and forth.

Re: Issue: Invalid Expression Failed to Execute (SelectLayerByAttribute)

JoshuaBixby — Wed, 24 Jul 2024 20:36:26 GMT

Unfortunately, the OP is using ArcMap 10.8.2, which is Python 2.x.

Re: Issue: Invalid Expression Failed to Execute (SelectLayerByAttribute)

HaydenWelch — Wed, 24 Jul 2024 20:57:37 GMT

Always forget that Arcmap is still using Python 2...

Re: Issue: Invalid Expression Failed to Execute (SelectLayerByAttribute)

JoshuaBixby — Wed, 24 Jul 2024 21:13:05 GMT

Strings must be single-quoted:

query = '"{}" = \'{}\''.format(unique_id_field, unique_id)

Re: Issue: Invalid Expression Failed to Execute (SelectLayerByAttribute)

KioshiMishiro — Thu, 25 Jul 2024 18:19:21 GMT

Thank you all. I solved the issues based on your comments. Really appreciate.