1

Following on from Correct SQL expression for SelectbyAttribute Tool, I have my code:

import arcpy

plantFile = r"U:\Users\K\Plants.shp"
field = "UID"

arcpy.MakeFeatureLayer_management (plantFile, "plant_temp")
expression = round("UID" / 2, 0) <> "UID" / 2
arcpy.SelectLayerByAttribute_management("plant_temp", "NEW_SELECTION", expression)

I encounter -- TypeError: unsupported operand type(s) for /: 'str' and 'int'

"UID" is a long integer field, so I'm not understanding why this is happening. I've looked up methods for changing types for individual values, but not for an entire field. I will also be running this on over 20,000 records, so processing time is a concern.


EDIT I applied the suggested expression expression = '''round("UID" / 2, 0) <> "UID" / 2'''

which returned ExecuteError: ERROR 000358: Invalid expression

  • 2
    Unfortunately, you may have been given a bum steer. The case of the SQL function doesn't matter, but passing it to SQL does. You just need quotes around the expression (so it's evaluated by ArcGIS SQL parser, not the Python interpreter) – Vince Feb 9 '17 at 2:57
3

@jbalk has put you almost there, however has left out the required arcpy.MakeFeatureLayer_management() for the Select to work.

import arcpy

plantFile = r"U:\Users\K\Plants.shp"
field = "UID"
arcpy.MakeFeatureLayer_management (plantFile, "plant_temp")

expression = ''' round("UID"/2, 0) <> "UID"/2 '''
arcpy.SelectLayerByAttribute_management("plant_temp", "NEW_SELECTION", expression)
3

Try this:

import arcpy

plantFile = r"U:\Users\K\Plants.shp"
field = "UID"
expression = "round({0} / 2, 0) <> ({0} / 2)".format(field)
arcpy.MakeFeatureLayer_management(plantFile,"plantLyr")
arcpy.SelectLayerByAttribute_management("plantLyr", "NEW_SELECTION", expression)

I think the key thing is making the feature layer because SelectLayerByAttribute needs a layer as input rather than a dataset.

I also used Python string formatting to make the expression easy to plug variable names into.

The last thing I did was to drop the delimiters around the field name because nowadays the SQL syntax parser seems to not require them (at least not for shapefiles and file geodatabases).

  • In this case I'd suggest you use expression = "round({0} / 2, 0) <> ({0} / 2)".format(arcpy.AddFieldDelimiters(plantFile, field)) – Midavalo Feb 9 '17 at 4:06
  • @Midavalo I'm sure that's not necessary if using a file geodatabase feature class, but I know it is a shapefile here, and I thought it was now the same for them too. – PolyGeo Feb 9 '17 at 4:09
  • you are right! I've learned something tonight – Midavalo Feb 9 '17 at 4:17
  • 1
    @PolyGeo, thank you, this did work. can you explain? – saoirse Feb 9 '17 at 18:55
  • @saoirse I updated my answer with some explanation. – PolyGeo Feb 12 '17 at 2:36
1

The problem is your expression variable. The expression needs to be a string.

Try this:

import arcpy

plantFile = r"U:\Users\K\Plants.shp"
field = "UID"

arcpy.MakeFeatureLayer_management (plantFile, "plant_temp")
expression = '''round("UID" / 2, 0) <> "UID" / 2'''
arcpy.SelectLayerByAttribute_management("plant_temp", "NEW_SELECTION", expression)
  • Thanks for the advice. So I applied the expression and was returned with this error now: ExecuteError: ERROR 000358: Invalid expression – saoirse Feb 9 '17 at 3:05
  • 1
    You will still need the arcpy.MakeFeatureLayer() for the SelectLayerByAttribute() to work on a shapefile – Midavalo Feb 9 '17 at 3:30
  • Sorry guys. I don't know how I missed that. In too much of a hurry. I've fixed it now. @Midavalo – jbalk Feb 9 '17 at 4:45
  • @jbalk You will need to update your select to look at the feature layer too - arcpy.SelectLayerByAttribute_management("plant_temp", "NEW_SELECTION", expression) – Midavalo Feb 10 '17 at 22:11
-1

You're writing Python code. You need to understand how Python code works.

The TypeError is from this line:

expression = round("UID" / 2, 0) <> "UID" / 2

In Python, something surrounded by double quotes is a str, more commonly referred to as just a "string." So "UID" is a string, a sequence of text characters. Note that a string is a value; it is not a variable. Your code above attempts to divide the string by 2, but this can't work. Division doesn't make sense for a text value. This like asking, "What's the result of the text, 'jgsdjhdshj' divided by 2?" It doesn't make any sense as a question. That's why you get TypeError: unsupported operand type; str and int don't support being divided together. Note that this happens immediately when Python attempts to execute that line, before anything following is executed.

The confusion most likely comes from ESRI deciding to reuse a lot of Python syntax in their own custom tools like the field calculator. They made it look like Python even though it isn't really Python. (It probably gets pre-parsed by ESRI and then evaluated as Python after some transformation.)

The way the achieve what you do in the field calculator with actual Python code is to make that input a string:

expression = 'round("UID" / 2, 0) <> "UID" / 2'

(I've chosen single quotes to delimit the string here, but Python has several ways of delimiting strings.)

Your second error, the ExecuteError, is something different. This error is from ArcGIS. It appears that the expression you provided is in some way malformed. It's difficult to be sure without knowing more about your set up, but the first thing I would ask is, "What kind of data source are you using?" If it's not an actual relational database (examples include Microsoft SQL Server, Oracle, and PostgreSQL), then using double quotes to refer to the field name is incorrect; in the cases of File GDBs (the directories ending in .gdb) or shapefiles, try surrounding the field name in square brackets instead. (So "UID" would become [UID].)

All of these details are discussed in the documentation of the tool you're using.

Side note: The sort of design ESRI has chosen here (taking user input as a string, performing some transformation, and then evaluating it as code) is normally considered bad form in Python (and most programming languages) since it's very easy to accidentally create a security vulnerability. Don't follow their example in your own code.

  • "The confusion most likely comes from ESRI deciding to reuse a lot of Python syntax in their own custom tools like the field calculator" - The expression is SQL... – Midavalo Feb 9 '17 at 18:18
  • @Midavalo Hm. I suppose you have a point. On the other hand, this is a Python script, not SQL. It only takes a modicum of knowledge to know that different languages don't mix. I'll sort it out. – jpmc26 Feb 9 '17 at 18:41
  • The SQL is being passed to query the data by python. You're not mixing languages, you're using one to pass the other as a query. – Midavalo Feb 9 '17 at 18:43
  • @Midavalo I'm referring to the fact the OP attempted to write a SQL filter as Python code. Whether intentional or not, the OP mixed the languages. – jpmc26 Feb 9 '17 at 19:33

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.