Issue: Invalid Expression Failed to Execute (SelectLayerByAttribute)

[KioshiMishiro]

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")

[CodyPatterson]

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

[KioshiMishiro]

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) [...]

[JoshuaBixby]

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)

[KioshiMishiro]

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

[JoshuaBixby · Accepted solution]

Strings must be single-quoted:

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

[HaydenWelch]

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.

[JoshuaBixby]

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

[HaydenWelch]

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

[KioshiMishiro]

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