topic Re: Variables in Select by Attribute Where Clause in Python Questions

Variables in Select by Attribute Where Clause

andrewcollins12 — Fri, 01 Sep 2023 20:43:29 GMT

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.

Re: Variables in Select by Attribute Where Clause

AlfredBaldenweck — Fri, 01 Sep 2023 20:25:53 GMT

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

Re: Variables in Select by Attribute Where Clause

andrewcollins12 — Fri, 01 Sep 2023 20:40:46 GMT

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

Re: Variables in Select by Attribute Where Clause

AlfredBaldenweck — Fri, 01 Sep 2023 20:57:42 GMT

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!

Re: Variables in Select by Attribute Where Clause

AlfredBaldenweck — Fri, 01 Sep 2023 21:07:43 GMT

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

Re: Variables in Select by Attribute Where Clause

andrewcollins12 — Fri, 01 Sep 2023 21:19:26 GMT

@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.

Re: Variables in Select by Attribute Where Clause

AlfredBaldenweck — Fri, 01 Sep 2023 21:31:20 GMT

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.

Re: Variables in Select by Attribute Where Clause

andrewcollins12 — Fri, 01 Sep 2023 21:41:03 GMT

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.

Re: Variables in Select by Attribute Where Clause

AlfredBaldenweck — Fri, 01 Sep 2023 21:44:19 GMT

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.

Re: Variables in Select by Attribute Where Clause

andrewcollins12 — Fri, 01 Sep 2023 22:37:27 GMT

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.

Re: Variables in Select by Attribute Where Clause

JingchaoZhou — Wed, 29 Nov 2023 19:03:42 GMT

You mentioned that the script works if you run them line by line in the python window, but did not work as a script.

I was wondering whether you ran the script in the python window or as a standalone script outside of ArcGIS Pro?

Re: Variables in Select by Attribute Where Clause

andrewcollins12 — Wed, 24 Jan 2024 21:05:02 GMT

Both. When I ran the code in the original post line-by-line in the python window within ArcGIS Pro, it all ran correctly. But something was breaking with the select functions when I ran it as a standalone script outside of Arc Pro. The solution proposed by @AlfredBaldenweck--to assign the selection to a variable--enabled me to run it as a standalone script.