nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Invalid Expression or Syntax Error

LorindaGilbert

Writing a python script and attempting to use the SelectLayerByAttribute_management command.
I've tried it several different ways and keep getting a syntax error on the variable set, or if the variable set works, get invalid expression. I am querying against a file geodatabase.

python snippet:

where_clause = (r"Status = 'U'")

arcpy.SelectLayerByAttribute_management("Crimes", "NEW_SELECTION", where_clause)

I'd like to add .outputCount to this to see if there are any rows selected. I have done this once in the python window but can't remember how I did it now and didn't put it in my script when it worked.

I've also tried various ways of putting quotes and single quotes with and without the r or the parenthesis. Syntax error or it doesn't get passed properly to the query and gives the Invalid expression.

Help!

Forgot to add: In the ArcMap window using the Select by Attributes dialog, the proper syntax is "Status" = 'U' and it works as expected. Just can't get the translation to python to work properly.

Encuentra más publicaciones etiquetadas con

Accepted answers

All comments

JakeSkinner

Take a look at the example script below. Also, be sure to execute the make a feature layer before executing the 'arcpy.SelectbyAttribute_management' function.

where_clause = ("Status = 'U'")

arcpy.MakeFeatureLayer_management("Crimes", "Crimes_Lyr")

arcpy.SelectLayerByAttribute_management("Crimes_Lyr", "NEW_SELECTION", where_clause)

print arcpy.GetCount_management("Crimes_Lyr")

You can also use the 'arcpy.FieldDelimiters' function to get the correct syntax of the field:

fld = arcpy.AddFieldDelimiters("Crimes", "Status")

where_clause = (fld + " = 'U'")

arcpy.MakeFeatureLayer_management("Crimes", "Crimes_Lyr")

arcpy.SelectLayerByAttribute_management("Crimes_Lyr", "NEW_SELECTION", where_clause)

print arcpy.GetCount_management("Crimes_Lyr")

HemingZhu

Writing a python script and attempting to use the SelectLayerByAttribute_management command.
I've tried it several different ways and keep getting a syntax error on the variable set, or if the variable set works, get invalid expression. I am querying against a file geodatabase.

python snippet:

where_clause = (r"Status = 'U'")

arcpy.SelectLayerByAttribute_management("Crimes", "NEW_SELECTION", where_clause)

I'd like to add .outputCount to this to see if there are any rows selected. I have done this once in the python window but can't remember how I did it now and didn't put it in my script when it worked.

I've also tried various ways of putting quotes and single quotes with and without the r or the parenthesis. Syntax error or it doesn't get passed properly to the query and gives the Invalid expression.

Help!

Forgot to add: In the ArcMap window using the Select by Attributes dialog, the proper syntax is "Status" = 'U' and it works as expected. Just can't get the translation to python to work properly.

If the layer came from file GDB, your where_clause ="Status" ='U' should work. One way you can test is using string literal in the function:

arcpy.SelectLayerByAttribute_management("Crimes", "NEW_SELECTION", "\"Status\"='U'")

LorindaGilbert

Tried that, it worked great in the python window. Added to the script that will be a tool in ArcMap and it failed.
Running script ProcessCAUFile...
<class 'arcgisscripting.ExecuteError'>: ERROR 000622: Failed to execute (Make Feature Layer). Parameters are not valid.
ERROR 000628: Cannot set input into parameter in_features.

Failed to execute (ProcessCAUFile).

The ultimate goal is to produce a tool for the toolbar in ArcMap. I keep getting where the python scripting works fine in the python window and then fails in the script. I am using the create tool wizard, have it run python script in process.

The reason that I am looking for a number output from the selection query is to see if all features geocoded. If the number of rows is greater than 0, then the user needs to run the rematch/review tool, otherwise it tells them that all geocoded and they can proceed to the next step - another tool on the toolbar that copies the geocoded data for archiving and appends it to a yearly file for mapping - this one actually works.

MollyWatson

I am having a similar problem when trying to write a where clause that contains multiple fields and connectors. I have 3 address fields that I would like to select and then zoom to the selected parcel. I think it's an issue with double or single quotes around the entire string, but I haven't figured out the solution. I've read on a few sites that numeric values do not need quotes so I left those off of the number. I also tried using them and the script also did not work. Here's what I have now that does not work:

#Select by Attributes
parcels = "C:\PreApp Maps\Parcels.lyr"
whereClause = '"ADDRNO" = 1930 AND "ADDRSTREET" = 'BIG OWL' AND "ADDRSUFFIX" = 'ROAD''
arcpy.SelectLayerByAttribute_management (parcels, "NEW_SELECTION", whereClause)

#Zoom to Selected
mxd = arcpy.mapping.MapDocument(r"Current")
df = arcpy.mapping.ListDataFrames(mxd, "Boulder County")[0]
df.zoomToSelectedFeatures()
arcpy.RefreshActiveView()

The original source for the parcels layer is on sde not a personal geodatabase so I'm pretty sure I'd use some sort of quotes instead of brackets.

ChrisSnyder

Assuming the ADDRNO field is numeric and the others are text, try this:

whereClause = "ADDRNO = 1930 AND ADDRSTREET = 'BIG OWL' AND ADDRSUFFIX = 'ROAD'"

Since I think v9.1 or so you don't have to put quotes and backslashes around the field names. When you export scripts out of ModelBuilder it seems to keep these old school formatting faux pass for seme reason. You don't need them!

MollyWatson

Thanks for the suggestion. When I tried it, I got a new error:

<class 'arcgisscripting.ExecuteError'>: Failed to execute. Parameters are not valid.
ERROR 000825: The value is not a layer or table view
ERROR 000840: The value is not a Raster Layer.
ERROR 000840: The value is not a Mosaic Layer.
Failed to execute (SelectLayerByAttribute).

Failed to execute (TestAddressZoomTo).

Any other suggestions? Again, I am trying to select 3 address fields and zoom to the selected feature. The first field is numeric, the other 2 fields are text. I also tried changing the script so that the 3 fields were user defined parameters. Then I used the GetParameterAsText function within the SelectLayerByAttribute where clause. I got the same error as listed above. Here is the revised script I was trying:

# Script arguments
PAR1 = arcpy.GetParameterAsText(0)
PAR2 = arcpy.GetParameterAsText(1)
PAR3 = arcpy.GetParameterAsText(2)

#Select by Attributes
parcels = "C:\PreApp Maps\Parcels.lyr"
whereClause = "ADDRNO = PAR1 AND ADDRSTREET = PAR2 AND ADDRSUFFIX = PAR3"
arcpy.SelectLayerByAttribute_management (parcels, "NEW_SELECTION", whereClause)

#Zoom to Selected
mxd = arcpy.mapping.MapDocument(r"Current")
df = arcpy.mapping.ListDataFrames(mxd, "Boulder County")[0]
df.zoomToSelectedFeatures()
#arcpy.RefreshActiveView()

HemingZhu

Thanks for the suggestion. When I tried it, I got a new error:

<class 'arcgisscripting.ExecuteError'>: Failed to execute. Parameters are not valid.
ERROR 000825: The value is not a layer or table view
ERROR 000840: The value is not a Raster Layer.
ERROR 000840: The value is not a Mosaic Layer.
Failed to execute (SelectLayerByAttribute).

Failed to execute (TestAddressZoomTo).

Any other suggestions? Again, I am trying to select 3 address fields and zoom to the selected feature. The first field is numeric, the other 2 fields are text. I also tried changing the script so that the 3 fields were user defined parameters. Then I used the GetParameterAsText function within the SelectLayerByAttribute where clause. I got the same error as listed above. Here is the revised script I was trying:

# Script arguments
PAR1 = arcpy.GetParameterAsText(0)
PAR2 = arcpy.GetParameterAsText(1)
PAR3 = arcpy.GetParameterAsText(2)

#Select by Attributes
parcels = "C:\PreApp Maps\Parcels.lyr"
whereClause = "ADDRNO = PAR1 AND ADDRSTREET = PAR2 AND ADDRSUFFIX = PAR3"
arcpy.SelectLayerByAttribute_management (parcels, "NEW_SELECTION", whereClause)

#Zoom to Selected
mxd = arcpy.mapping.MapDocument(r"Current")
df = arcpy.mapping.ListDataFrames(mxd, "Boulder County")[0]
df.zoomToSelectedFeatures()
#arcpy.RefreshActiveView()

Based on your error message, it looks like it doesn't recognize parcels as a feature layer or table view. Either check parcels path make sure its souce is accessible or make a layer explicitly:

arcpy.env.workspace ="C:\PreApp Maps"
arcpy.MakeFeatureLayer_management("Parcels", "lyr")
...
arcpy.SelectLayerByAttribute_management ("lyr", "NEW_SELECTION", whereClause)

MollyWatson

Thanks for the suggestion. When I tried the MakeFeatureLayer it still did not work. I think there is an issue with the parcel feature class being located on SDE. So I exported a copy and saved it as a shapefile. The MakeFeatureLayer function then worked. However, I am still getting an error 00358 invalid expression when it gets to the whereClause for the SelectLayerByAttribute. Again, here's what the whereClause expression is now that does not work:

# Script arguments
PAR1 = arcpy.GetParameterAsText(0)
PAR2 = arcpy.GetParameterAsText(1)
PAR3 = arcpy.GetParameterAsText(2)

#Select by Attributes
arcpy.env.workspace ="C:\PreApp Maps"
arcpy.MakeFeatureLayer_management("Parcels.shp", "parcels_lyr")
whereClause = "ADDRNO = PAR1 AND ADDRSTREET = PAR2 AND ADDRSUFFIX = PAR3"
arcpy.SelectLayerByAttribute_management ("parcels_lyr", "NEW_SELECTION", whereClause)

DanPatterson_Retired

have you tested the query through the standard select by attributes interface in ArcMap? Have you tried it incrementally (ie add one piece, then the second etc)?

HemingZhu

have you tested the query through the standard select by attributes interface in ArcMap? Have you tried it incrementally (ie add one piece, then the second etc)?

You stated that your first field is a numeric. If that is the case, you need parse you first parameter to a numeric. something like this:
PAR1 = arcpy.GetParameterAsText(0)
# don't know what your numeric type is, so just put int here for demo
PAR1_int =int(PAR1)
whereClause = "ADDRNO = PAR1_int AND ADDRSTREET = PAR2 AND ADDRSUFFIX = PAR3"

MollyWatson

I tried inserting the parse phrase as suggested and I still get this error:

<class 'arcgisscripting.ExecuteError'>: ERROR 000358: Invalid expression
Failed to execute (SelectLayerByAttribute).

I verified the whereClause using the manual select by attribute box and it verified successfully and selected the correct parcel. This is the example where clause I took from the selection box:

"ADDRNO" = 2 AND "ADDRSTREET" = 'PONDEROSA' AND "ADDRSUFFIX" = 'LANE'

So based on this it does appear that the field names need quotes. However, when I put double quotes around the entire string it still does not work. I tried the following to enclose the string and I get invalid syntax errors for all:

""ADDRNO" = 2 AND "ADDRSTREET" = 'PONDEROSA' AND "ADDRSUFFIX" = 'LANE'" (double quotes)
'"ADDRNO" = 2 AND "ADDRSTREET" = 'PONDEROSA' AND "ADDRSUFFIX" = 'LANE'' (single quotes)
'"ADDRNO" = 2 AND "ADDRSTREET" = 'PONDEROSA' AND "ADDRSUFFIX" = 'LANE'" (single beginning, double end)
"ADDRNO" = 2 AND "ADDRSTREET" = 'PONDEROSA' AND "ADDRSUFFIX" = 'LANE' (no quotes)

When I remove the quotes from around the field names, the script says it is completed successfully, yet no parcel is selected in the parcel layer.

LoganPugh

You will either need to escape your double-quotes with backslashes or single-quotes. Backslashes are probably the most obvious:

"\"ADDRNO\" = 2 AND \"ADDRSTREET\" = 'PONDEROSA' AND \"ADDRSUFFIX\" = 'LANE'"

HemingZhu

You will either need to escape your double-quotes with backslashes or single-quotes. Backslashes are probably the most obvious:
"\"ADDRNO\" = 2 AND \"ADDRSTREET\" = 'PONDEROSA' AND \"ADDRSUFFIX\" = 'LANE'"

I have successfully used expression in searchCursor. You really don't need quotes (single or double) for field names for arcpy. Not sure why it did not work. If you could post your script and part of data, i will test it myself and get back to you.

MollyWatson

Attached is a zip file containing a portion of the parcel shapefile. Here is the code I was using to just try to test the Select by Attributes function:

#Select by Attributes
arcpy.env.workspace ="C:\PreApp Maps"
arcpy.MakeFeatureLayer_management("Parcels.shp", "parcels_lyr")
whereClause = "\"ADDRNO\" = 2 AND \"ADDRSTREET\" = 'PONDEROSA' AND \"ADDRSUFFIX\" = 'LANE'"
arcpy.SelectLayerByAttribute_management ("parcels_lyr", "NEW_SELECTION", whereClause)

Ideally, I would like to use 3 user defined parameters to get the parcel selected which I have been using this script that doesn't work:

# Script arguments
PAR1 = arcpy.GetParameterAsText(0)
PAR2 = arcpy.GetParameterAsText(1)
PAR3 = arcpy.GetParameterAsText(2)

#Parse first parameter to numeric
PAR1_int =int(PAR1)

#Select by Attributes
arcpy.env.workspace ="C:\PreApp Maps"
arcpy.MakeFeatureLayer_management("Parcels.shp", "parcels_lyr")
whereClause = "ADDRNO = PAR1_int AND ADDRSTREET = PAR2 AND ADDRSUFFIX = PAR3"
arcpy.SelectLayerByAttribute_management ("parcels_lyr", "NEW_SELECTION", whereClause)

Parcels_clip.zip

achapapkowski

Your where clause is incorrect, you need to format it as such: "ADDRNO" = 1 AND "ADDRSTREET" = 'GOLD' AND "ADDRSUFFIX" = 'TRAIL', but in the code provided, you do the following:
whereClause = "ADDRNO = PAR1_int AND ADDRSTREET = PAR2 AND ADDRSUFFIX = PAR3"

Try the following:

whereClause = "\"ADDRNO\" = " + str(PAR1_int) + " AND \"ADDRSTREET\" = '" + str(PAR2) + "' AND \"ADDRSUFFIX\" = '" + str(PAR3) +"'"

This also assume that you will always be using a shapefile for the field names. You might want to look at arcpy.AddFieldDelimiters() which will format the fields for you based on the workspace.

Enjoy

MollyWatson

Andrew, thanks for the suggestion. I tried using your where clause for both Select Layer by Attributes and Search Cursor. Both scripts said they were completed successfully, yet neither selected the parcel defined by the user parameters. When I created the three parameters in the tool properties, I made them strings. Should they be something else?

Here's the script I have for the Select by Attributes:

# Script arguments
PAR1 = arcpy.GetParameterAsText(0)
PAR2 = arcpy.GetParameterAsText(1)
PAR3 = arcpy.GetParameterAsText(2)

#Parse first parameter to numeric
PAR1_int =int(PAR1)

#Select by Attributes
arcpy.env.workspace ="C:\PreApp Maps"
arcpy.MakeFeatureLayer_management("Parcels.shp", "parcels_lyr")
whereClause = "\"ADDRNO\" = " + str(PAR1_int) + " AND \"ADDRSTREET\" = '" + str(PAR2) + "' AND \"ADDRSUFFIX\" = '" + str(PAR3) +"'"
arcpy.SelectLayerByAttribute_management ("parcels_lyr", "NEW_SELECTION", whereClause)

#Zoom to Selected
mxd = arcpy.mapping.MapDocument(r"Current")
df = arcpy.mapping.ListDataFrames(mxd, "Boulder County")[0]
df.zoomToSelectedFeatures()
arcpy.RefreshActiveView()

For the Search Cursor, the first part and last part is the same and here's the middle:
#Search Cursor
arcpy.env.workspace ="C:\PreApp Maps"
arcpy.MakeFeatureLayer_management("Parcels.shp", "parcels_lyr")
whereClause = whereClause = "\"ADDRNO\" = " + str(PAR1_int) + " AND \"ADDRSTREET\" = '" + str(PAR2) + "' AND \"ADDRSUFFIX\" = '" + str(PAR3) +"'"
rows = arcpy.SearchCursor("parcels_lyr", whereClause)

Were you able to get it to work with the where Clause you provided?

Thanks everyone for your help.

HemingZhu

Attached is a zip file containing a portion of the parcel shapefile. Here is the code I was using to just try to test the Select by Attributes function:

#Select by Attributes
arcpy.env.workspace ="C:\PreApp Maps"
arcpy.MakeFeatureLayer_management("Parcels.shp", "parcels_lyr")
whereClause = "\"ADDRNO\" = 2 AND \"ADDRSTREET\" = 'PONDEROSA' AND \"ADDRSUFFIX\" = 'LANE'"
arcpy.SelectLayerByAttribute_management ("parcels_lyr", "NEW_SELECTION", whereClause)

Ideally, I would like to use 3 user defined parameters to get the parcel selected which I have been using this script that doesn't work:

# Script arguments
PAR1 = arcpy.GetParameterAsText(0)
PAR2 = arcpy.GetParameterAsText(1)
PAR3 = arcpy.GetParameterAsText(2)

#Parse first parameter to numeric
PAR1_int =int(PAR1)

#Select by Attributes
arcpy.env.workspace ="C:\PreApp Maps"
arcpy.MakeFeatureLayer_management("Parcels.shp", "parcels_lyr")
whereClause = "ADDRNO = PAR1_int AND ADDRSTREET = PAR2 AND ADDRSUFFIX = PAR3"
arcpy.SelectLayerByAttribute_management ("parcels_lyr", "NEW_SELECTION", whereClause)

Molly,
I tested your code, i made a little adjustment. It should worked by the following:

PAR1 = arcpy.GetParameterAsText(0)
PAR2 = arcpy.GetParameterAsText(1)
PAR3 = arcpy.GetParameterAsText(2)

#Select by Attributes
arcpy.env.workspace ="C:\PreApp Maps"
arcpy.MakeFeatureLayer_management("Parcels.shp", "parcels_lyr")
whereClause = "ADDRNO =" + PAR1 +" AND ADDRSTREET = '"+ PAR2+"' AND ADDRSUFFIX ='" + PAR3 +"'" 
arcpy.SelectLayerByAttribute_management ("parcels_lyr", "NEW_SELECTION", whereClause)

JanSouek_8-1756206175358.png

MollyWatson

Were you able to get the code you posted to work? The script runs successfully but no parcel is selected so the map does not zoom to the selected parcel. I copied your code exactly as you posted it and I'm still not successful. I also tried including the "parse first paramter to numeric" suggestion as mentioned on 8/16/2011 but then I get an error that that it cannot concatenate 'str' and 'int' objects.

HemingZhu

Were you able to get the code you posted to work? The script runs successfully but no parcel is selected so the map does not zoom to the selected parcel. I copied your code exactly as you posted it and I'm still not successful. I also tried including the "parse first paramter to numeric" suggestion as mentioned on 8/16/2011 but then I get an error that that it cannot concatenate 'str' and 'int' objects.

Sorry for late reply. Yes i indeed got results. You can test it on a .mxd using the script as as layer.

Únete a la Conversación

Los miembros registrados pueden publicar, seguir actualizaciones y más. ¿Nuevo aquí? Regístrate gratis.

Enlaces Rápidos

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.