1

I am trying to use the SelectLayerByAttribute_management in a tool. The tool works like that: The User puts in the Feature which Layer should be selected by attribute -> "inputFeature". Then he chooses the Field of the table -> "inputFieldname". At the end he types in the value which will highlight the Layers by Attribute -> "inputValue". That is my code, which works just with numeric value.

def execute(self, parameters, messages):

    """The source code of the tool."""

    inputFeature = parameters[0].valueAsText
    ap.AddMessage("Feature: "+ inputFeature)
    inputFieldname = parameters[1].valueAsText
    ap.AddMessage("Fieldname: "+ inputFieldname)
    inputValue = parameters[2].valueAsText
    ap.AddMessage("Value: "+ inputValue)

    whereClause = """{} = '{}'""".format(ap.AddFieldDelimiters(inputFeature, inputFieldname), inputValue)

    ap.SelectLayerByAttribute_management(inputFeature,"NEW_SELECTION", whereClause)
    return

If I choose a field where just Strings are stored and I type in for the inputValue a String, I get this Error:

ExecuteError: ERROR 000358: Invalid expression. Failed to execute (SelectLayerByAttribute).

If I choose a field where just Numbers are stored (doesn't matter which type...double, float, integer) and I type in for the inputValue a Number, I get results. Or better said, all values which have been found are highlighted with SelectLayerByAttribute.

So what is the solution for typing in any datatype I want...?

  • Look at the second answer to this question: Including variable in where clause of arcpy.Select_analysis()? – BERA Jan 14 at 14:21
  • Since I am pretty new in working with python, I have some problems by using the method. How is it possible to use the buildWhereClause-Method in a Python Toolbox with parameters? To use the method, I have to put it in to the def execute in the Python toolbox right? – Andreas Jan 15 at 9:49
1

Your question is a bit tricky since:

The SQL syntax you use differs depending on the data source. Each DBMS has its own SQL dialect.

So if you query a date field in a shapefile the syntax is different from a postgis datefield. I have not found a Describe method to determine data source which would make it possible to build a function to delimit all different data source values.

But for shapefiles and feature classes in a file geodatabase something like this should work:

import arcpy

infc = r'X:\Formulas.shp'

def delimitValue(inputfc, fieldname, value):
    fieldtype = [f.type for f in arcpy.ListFields(inputfc) if f.name == fieldname][0]
    if fieldtype in ('OID','Double','Integer'):
        return '{0}'.format(value)
    elif fieldtype in ('String'):
        return "'{0}'".format(value)
    elif fieldtype in ('Date'):
        return "date '{0}'".format(value)

Then use like this:

#String
sql = """{0}={1}""".format(arcpy.AddFieldDelimiters(infc, 'somestringfield'), delimitValue(infc,'somestringfield','abc'))
#Numbers
sql = """{0}={1}""".format(arcpy.AddFieldDelimiters(infc, 'somenumberfield'), delimitValue(infc,'somenumberfield','123')) #123 will also work
#Date
sql = """{0}={1}""".format(arcpy.AddFieldDelimiters(infc, 'somedatefield'), delimitValue(infc,'somedatefield','2018-01-01'))
  • Thank you! I will try it. But the other solution in your comment above seems to be perfect. Till now I have no idea how to implement it in my codeblock. – Andreas Jan 15 at 11:29

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.