Invalid Expression or Syntax Error

LorindaGilbert · ‎08-10-2011

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.

HemingZhu · ‎08-16-2011

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 · ‎08-16-2011

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 · ‎08-16-2011

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 · ‎08-16-2011

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 · ‎08-17-2011

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)

AndrewChapkowski · ‎08-17-2011

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 · ‎08-17-2011

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 · ‎08-17-2011

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)

MollyWatson · ‎08-22-2011

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 · ‎08-29-2011

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.