1

I'm trying to use ArcPy and Python more since the ability to just point and click isn't that marketable. I've been using ArcPy for Select by Attribute. It works fine when I try to select attribute with numbers (float, double etc) like bigger than (>) or smaller than (<). But when I try to include text/string in my expression, I always get the "ERROR 000358: Invalid expression"

For instance:

arcpy.SelectLayerByAttribute_management('Subregional Groups', 'NEW_SELECTION', '"MUNICODE" > 13')

The code above gives me the result I want.

But

arcpy.SelectLayerByAttribute_management('Subregional Groups', 'NEW_SELECTION', '"NAME" = "Allentown City"')

gives me the error mentioned before.

I have also tried

arcpy.SelectLayerByAttribute_management('Subregional Groups', 'NEW_SELECTION', '[NAME] = "Allentown City"')

and it doesn't work either.

I'm using ArcGIS Pro 2.2.

2

This problem is very common and can be fixed with AddFieldDelimiters:

Adds field delimiters to a field name to allow for use in SQL expressions.

The field delimiters used in an SQL expression differ depending on the format of the queried data. For instance, file geodatabases and shapefiles use double quotation marks (" "), personal geodatabases use square brackets ([ ]), and enterprise geodatabases don't use field delimiters. The function can take away the guess work in ensuring that the field delimiters used with your SQL expression are the correct ones.

Use single quotes around Allentown city and three doubles quotes around everything, for example:

field = 'NAME'
sql = """{0} = 'Allentown City'""".format(arcpy.AddFieldDelimiters('Subregional Groups',field))
arcpy.SelectLayerByAttribute_management('Subregional Groups', 'NEW_SELECTION', sql)
  • 1
    I could be missing it, but it looks like you're missing a single quote after Allentown City... – John Sep 5 '18 at 16:38
  • Thanks a lot for your answer. But why do I need to use "AddFieldDelimiters" at all? Is it just how SQL works with string? And thanks for helping me again. The other day you helped me with the pandas pivot table function and I've been studying panda ever since. And another question:what is the "{0}=" part about? I have seen it left and right. Thanks. – AndrewLebron Sep 5 '18 at 17:31
  • It has nothing to do with selecting strings but if you use it you will know that this part of the sql query is always correct no matter input data (gdb, shapefile, mdb etc.). Format is used to insert some string (or number) into another string (see pyformat.info). It is safer and more robust than using +: 'Beginning ' + str(123) + ' end' use: 'Beginning {0} end'.format(123) – BERA Sep 5 '18 at 17:37
  • @BERA Thanks for the link. I'm in the process of perusing, or rather understanding it. In my understanding, adding field delimiters is like preparation for SQL expression. I have to add the delimiters in order to build the SQL expression. And is {0} a placeholder for where the string will be inserted? Am I understanding these correctly? Last silly question: dos your Beginning and end stand for the delimiters of the field or are they just random string examples that you used. Thanks again. – AndrewLebron Sep 5 '18 at 20:57
2

I think the issue you are running into is a difference between Python and SQL regarding Quotation marks. In python, you can use single or double quotes for strings, as long as you use the same one to start and stop any particular string. However, SQL requires the use of single quotes for strings (double quotes are typically for field names). So you would need to use 'Allentown City' not "Allentown City". Probably the safest on the python side may be to use python's triple quotes so you don't run into any issues.

arcpy.SelectLayerByAttribute_management('Subregional Groups', 'NEW_SELECTION', ''' "NAME" = 'Allentown City' ''')

The single quotes vs double quotes around Allentown City, I'm pretty sure, is the issue you were specifically having here, but you should also definitely refer to @BERA's answer regarding field delimiters and AddFieldDelimiters() if you're potentially going to be re-using the SQL against data from multiple different data sources or you don't know the data source it will be run against.

  • It also depends on the source of the data that you're querying against. If it's a geodatabase or shapefile, you'll need double quotes around the field name, if it's SDE you don't need anything. Take a look at their Python query guide, pro.arcgis.com/en/pro-app/arcpy/get-started/… – neighdough Sep 5 '18 at 16:55
  • Thanks a lot. But somehow I couldn't get your code to work. It ran successfully without creating errors but it doesn't return any results. I will try later as my ArcGIS Pro keeps crashing. As for BERA's answer, what's the advantage of using SQL? So that I can reuse it no matter what data source (shapefile or feature class) is as I don't need to worry about single or double quotation mark? Thanks again. – AndrewLebron Sep 5 '18 at 20:46
2

You're using double quotes for your select value when you should be using single quotes. Here's a few ways:

arcpy.SelectLayerByAttribute_management('Subregional Groups', 'NEW_SELECTION', '"NAME" = \'Allentown City\'')

arcpy.SelectLayerByAttribute_management('Subregional Groups', 'NEW_SELECTION', "\"NAME\" = 'Allentown City'")

arcpy.SelectLayerByAttribute_management('Subregional Groups', 'NEW_SELECTION', """"NAME" = 'Allentown City'""")

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.