Conditional SQL Query

Question

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
        cursor.updateRow(row)

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?

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

ianbroad · Accepted Answer · 2014-03-25 16:45:55Z

2

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:

edited Mar 25, 2014 at 16:45

answered Mar 25, 2014 at 16:38

ianbroad

9,1515 gold badges44 silver badges91 bronze badges

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
– Tom

2016-03-02 16:20:55 +00:00
Commented Mar 2, 2016 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
– PolyGeo ♦

2017-10-28 07:30:22 +00:00
Commented Oct 28, 2017 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
– ianbroad

2017-10-31 06:11:17 +00:00
Commented Oct 31, 2017 at 6:11

Add a comment |

Stack Exchange Network

Conditional SQL Query

1 Answer 1

Your Answer

Hot Network Questions

Conditional SQL Query

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions