I've been running a code that searches a desired field in a feature class and performs an specified SQL query based on whether the field is string or double. The variables are just temporary fillers to test the code, I'll be shifting them to GetParameterasText() as soon as I know that all of the code works. I need the code to work so that it can be turned into a ArcGIS toolbox script.

import arcpy
from arcpy import env
env.overwriteOutput = True

#Create working variables
fc = "C:/gisclass/assignment5/Results/CITIES.shp" #vector feature class
affectedField = "CAPITAL"
oldValue = -1
newValue = 2
integer = 0

fieldlist = arcpy.ListFields(fc)

for field in fieldlist:
    if field.type == "String":
        queryString = """"%s" LIKE '%s'"""%(affectedField, oldValue) or """%s" = '%s'"""%(affectedField, oldValue)
    elif field.type == "Double":
        queryString = """"%d" >= '%d'"""%(oldValue, integer) or """"%d" <= '%d'"""%(oldValue, integer)

with arcpy.da.UpdateCursor(fc, (affectedField,), queryString) as cursor:
    for row in cursor:
        row[0] = newValue

mess = arcpy.GetMessageCount()
print arcpy.GetMessage(0)
print arcpy.GetMessage(mess-1)

I have gotten the string where clause to work, but the integer clause is still producing the same result - Invalid SQL query. Any idea on what the solution to the problem is?

  • 1
    Shouldn't '%d' for the double be just %d? You don't want to use quotation marks for integers. – Alex Tereshenkov Mar 25 '14 at 6:13

I think you need to do something like this:

queryString = "\"{0}\" >= {1} OR \"{0}\" <= {1}".format(field.name, integer)

The query shouldn't use the actual value, it should compare the field name.

So the value in this field is less than this value.

Also, as far as I know, you shouldn't have the comma after affectedfield, just use:

with arcpy.da.UpdateCursor(fc, affectedField, queryString) as cursor:
  • 1
    Passing either a tuple or a string as the second argument to the UpdateCursor will work. In fact, I prefer to always pass--well a list instead of a tuple, because it gets me in the habitat of passing the same data type every time I use a cursor. – Tom Mar 2 '16 at 16:20
  • I think it has been fine to leave the double quotes off the field name for a while so this should work too and is easier to read: queryString = "{0} >= {1} OR {0} <= {1}".format(field.name, integer) – PolyGeo Oct 28 '17 at 7:30
  • I believe it depends on where the data is stored: desktop.arcgis.com/en/arcmap/10.3/map/working-with-layers/… – ianbroad Oct 31 '17 at 6:11

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.