Variables in Select by Attribute Where Clause

andrewcollins12 · ‎09-01-2023

Hello!

I'm having a heck of a time (read: I'm about to throw my computer down the stairs) trying to use two variables (both field and value) in a Select by Attribute query. I'm working in ArcGIS Pro 3.1/Python 3.9.

Here's the use case: I want to select points with a given value in a given field from a large grid of points in a point shapefile, and then export those points to a new shapefile. Most recent code attempt below.

import arcpy
import os

# Input point shapefile, very large
grid = arcpy.GetParameterAsText(0) 

# Specific area where I want to extract points with certain value
aoi = arcpy.GetParameterAsText(1) 

# Field in grid to use for values to query (dtype = double)
repl_field = arcpy.GetParameterAsText(2) 

# User-specified value to match with points to export (dtype = double)
value = arcpy.GetParameter(3) 

# Scratch workspace for intermediate data/debugging
ws = arcpy.GetParameterAsText(4) 

# Intermediate files
int_grid = os.path.join(ws, "int_grid.shp")
final_grid = os.path.join(ws, "final_grid.shp")

# Step 1: extract just the points inside AOI; this works as expected
arcpy.management.SelectLayerByLocation(grid,"COMPLETELY_WITHIN",aoi,"","NEW_SELECTION")
arcpy.conversion.ExportFeatures(grid,int_grid)

# Step 2: this selection isn't working; all points are exported, 
# not just those that match value.
where_clause = '"{}" = {}'.format(repl_field,value)
arcpy.management.SelectLayerByAttribute(int_grid,"NEW_SELECTION",where_clause)
arcpy.conversion.ExportFeatures(int_grid,final_grid)

I've also tried the methods commented here, here, and here (basically all just different ways of formatting the where clause), with no luck. No matter how I format it, I just can't get it to select and export the correct data.

When I enter this code (and several other versions) line-by-line in the Python console in ArcGIS Pro, it works perfectly. But I need it to run in this script. I'm thoroughly at a loss and would be very appreciative of any suggestions.

AlfredBaldenweck · ‎09-01-2023

I know earlier you mentioned that it exported all the points when you tried that; I think it's because you aren't assigning it as a variable; you're telling it to select from the table but not telling it what to do with that selection.

Of course, if it's breaking (throwing an error) that's a different story, but if all it's doing is ignoring the selection, that's probably why.

View solution in original post

AlfredBaldenweck · ‎09-01-2023

I'm not sure the FieldName needs to be in quotes, to be honest.

andrewcollins12 · ‎09-01-2023

Thanks @AlfredBaldenweck! I tried it without the quotes around the field name as well, unfortunately the result is the same.

I've also tried:

where_clause = '{} = {}'.format(repl_field,value) # all points export

where_clause = '\"{}\" ='.format(repl_field) + str(value) # invalid expression error

where_clause = '\"{}\" ='.format(repl_field) + value # invalid expression error

field_name = arcpy.AddFieldDelimiters(int_grid,repl_field)
where_clause = f'{field_name} = {value}' # all points export

# and just doing it inline, as below, but all points export
arcpy.management.SelectLayerByAttribute(int_grid,"NEW_SELECTION",
                                        '"{}" = {}'.format(repl_field,value))

arcpy.management.SelectLayerByAttribute(int_grid,"NEW_SELECTION",
                                        '{} = {}'.format(repl_field,value))

AlfredBaldenweck · ‎09-01-2023

Apologies, I'm in 2.9 still so I had to change it a bit (Export Features) and I didn't use shapefiles, but this worked for me. The other thing is I assumed you're feeding it file paths, and made sure to assign the selections as variables.

I think the issue is actually that you have the quotes in the wrong spot. SQL uses single quotes for strings, and right now you're feeding it a string without quotes, so it thinks you're looking for a field or something.

import arcpy
import os
# Input point shapefile, very large
grid = arcpy.GetParameterAsText(0) 
# Specific area where I want to extract points with certain value
aoi = arcpy.GetParameterAsText(1) 
# Field in grid to use for values to query (dtype = double)
repl_field = arcpy.GetParameterAsText(2) 
# User-specified value to match with points to export (dtype = double)
value = arcpy.GetParameter(3) 
# Scratch workspace for intermediate data/debugging
ws = arcpy.GetParameterAsText(4) 
# Intermediate files
int_grid = os.path.join(ws, 'int_grid')
final_grid = os.path.join(ws, 'final_grid')
# Step 1: extract just the points inside AOI; this works as expected
grid = arcpy.management.SelectLayerByLocation(grid,"COMPLETELY_WITHIN",aoi,"","NEW_SELECTION")
arcpy.conversion.FeatureClassToFeatureClass(grid,ws,'int_grid')
# Step 2: this selection isn't working; all points are exported, 
# not just those that match value.
where_clause = "{} = '{}'".format(repl_field,value)
#arcpy.AddMessage(where_clause)
int_grid = arcpy.management.SelectLayerByAttribute(int_grid,"NEW_SELECTION",where_clause)
arcpy.conversion.FeatureClassToFeatureClass(int_grid,ws,'final_grid')

This will work so long as you're looking for stuff in a text field; if you feed it a number it will return something empty or break, I expect.

Hope this helps!

AlfredBaldenweck · ‎09-01-2023

The other thing is that you might have a more efficient time if you chain the two selects together, rather than exporting each one.

final_grid = os.path.join(ws, 'final_grid')
where_clause = "{} = '{}'".format(repl_field,value)
grid = arcpy.management.SelectLayerByAttribute(grid,"NEW_SELECTION",where_clause)
grid = arcpy.management.SelectLayerByLocation(grid,"COMPLETELY_WITHIN",aoi,"","NEW_SELECTION")
arcpy.conversion.FeatureClassToFeatureClass(grid,ws,'final_grid')

andrewcollins12 · ‎09-01-2023

@AlfredBaldenweck unfortunately it is a number that I'm trying to match--both the field and the user-input value are dtype=double, so I didn't think that would be an issue. As you predicted, it broke when I tried to put the number in quotes, which makes sense. I tried changing the user-input value to string, but that didn't work either (predictably).

Unfortunately I can't really mark your answer as a solution; HOWEVER your comment did give me an idea, which was to isolate whether it was a syntax issue or a tool-related issue. So I used the where_clause from my original post with the ExportFeatures tool instead of the selection tool, and it worked! So, for that idea, many thanks!

# This worked!
where_clause = '"{}" = {}'.format(repl_field,value)
arcpy.conversion.ExportFeatures(grid,final_grid,where_clause)

I can't really mark this as a solution either, sadly, since it's just a workaround 😂

Moral of the story: seems to be an issue with SelectLayerByAttribute.

AlfredBaldenweck · ‎09-01-2023

Weird.

I just tested again, this time on a double field (Completely missed that the first time), and the same except for removing those single quotes, and it worked fine for me.

andrewcollins12 · ‎09-01-2023

I just tried that again, just to make extra sure I wasn't crazy (sort of hoped I was).

where_clause = "{} = {}".format(repl_field,value)
arcpy.management.SelectLayerByAttribute(grid,"NEW_SELECTION",
                                        where_clause)

And it still didn't work. I don't know why--maybe something in my install is broken, or I just need to restart my computer.

AlfredBaldenweck · ‎09-01-2023

I know earlier you mentioned that it exported all the points when you tried that; I think it's because you aren't assigning it as a variable; you're telling it to select from the table but not telling it what to do with that selection.

Of course, if it's breaking (throwing an error) that's a different story, but if all it's doing is ignoring the selection, that's probably why.

andrewcollins12 · ‎09-01-2023

Well I'll be darned. I've been using ArcPy for over a decade at this point, and I didn't know you could assign a selection to a variable. Embarrassing. But you're absolutely right.