ArcPy SQL – Using Variables in SQL Expression in ArcPy Script

arcmaparcpymodelbuildersql

I have a Python script with three parameters to collect user inputs via GetParameterAsText…

Buffer_Distance = arcpy.GetParameterAsText(0) # a string data type number (e.g. 40)
slp_min = arcpy.GetParameterAsText(1) # a string data type min % slope (e.g. 2)
slp_max = arcpy.GetParameterAsText(2) # a string data type max% slope (e.g. 46)

I want to use the inputs from (1) and (2) in an SQL statement.

Thus far, I've tried many things and this is my latest and of course it doesn't work. The statement is:

SQL = "gridcode >=" slp_min + "AND gridcode <=" + slp_max

gridocde is the field these values (1) and (2) that are selected from. The expected end result is that a user puts in a buffer distance. That output (of the buffer) clips all data. The slope inputs (min and max above) replace needing an SQL statement on the user side (front end) and using a script like this makes it user friendly (so they don't need to know SQL) and break proof.

I'm using ArcMap 10.8.1 and Python 2.7.18.

Best Answer

The problem is in "arcpy.GetParameterAsText(0)" return a string to variable. When you try concatenate string to parse in SQL, you need include single quote between the strings.

Try this way:

SQL = "gridcode >= '" slp_min + "' AND gridcode <= '" + slp_max + "'"

Now, if values was float or integer, try change the variable attribution casting the type, like this:

slp_min = float(arcpy.GetParameterAsText(1))

or

slp_max = int(arcpy.GetParameterAsText(2))

Then you try concatenate like you write before.

Related Question