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.

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