SelectLayerByAttribute_management searching with different datatypes

Question

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 '19 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 '19 at 09:49

score 1 · Answer 1 · answered Jan 14 '19 at 19:50

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 '19 at 11:29

SelectLayerByAttribute_management searching with different datatypes

1 Answers1