Debugging SQL in arcpy.da.SearchCursor RuntimeError: An invalid SQL statement was used?

Question

I am going through a python script in arcpy 10.1 to Select a field value (i.e. NEAR_DIST) based on three condition as

 1) NEAR_DIST<=radius(i.e.500 or anything from user input) 
 2) NEAR_DIST<>0 
 3) NEAR_DIST<> -1

I am using 500 which is to be variable asked from user and to be saved in "radius" variable so, i am only using "radius" variable in the SQL statement in following code-

 import arcpy,os,sys
 feature = "C:\Users\Bakul\Desktop\Od\example_shapefile\Polyline_segments_example.shp"
 radius = 500
 # create a dictionary to hold the list of near ids for each input id
 nearest_dict = dict()
 arra = []
 whereClause = '''"NEAR_DIST" <= '%{x}%' AND "NEAR_DIST" <>'%{y}%' AND "NEAR_DIST" <>'%{z}%\''''.format(x=radius,y=-1,z=0)
 print radius
 with arcpy.da.SearchCursor(feature, "NEAR_DIST", whereClause) as rows: ##NEAR_DIST is a field, I need sort all NEAR_DIST <=500 (i.e radius) and not equal 0 and -1
 for row in rows:## debugger does not pass here, 
 nearest_id = row[0]
 arra.append(nearest_id)
 del rows
 del row
 print arra

Debugger Error is

 500
 Traceback (most recent call last):
 File "C:\Users\Bakul\Desktop\Od\Python\scratch.py", line 89, in <module>
 for row in rows:
 RuntimeError: An invalid SQL statement was used.

Can you tell where is the problem (i think in SQL whereClause)?

• Roel: Instead of 'print radius' put in a 'print whereClause' and post the output from that, that's a lot easier to check than having to manually decipher the format string. Furthermore, I'm not sure the triple-quote and named argument formatting is helping legibility much - I'd suggest "'NEAR_DIST' <= '{0}' AND <etc>".format(radius, y, z) ; much easier to read, makes syntax errors much easier to spot. Finally, are you sure you're using the right field delimiters? Because with the field names in separate variables, that can never go wrong, plus your format string becomes even easier to read.
• PolyGeo: @Roel I think you could have provided this as an answer, and of course, still can.
• Roel: OK I've done so - different SE sites, different mores I guess; on stackoverflow.com where I post most, people are quick to whine that one should post comments and not answers... But I'll keep in mind to post as answers here :)

Answer 1

Instead of 'print radius' put in a 'print whereClause' and post the output from that, that's a lot easier to check than having to manually decipher the format string.

Furthermore, I'm not sure the triple-quote and named argument formatting is helping legibility much - I'd suggest "'NEAR_DIST' <= '{0}' AND ".format(radius, y, z) ; much easier to read, makes syntax errors much easier to spot.

Finally, are you sure you're using the right field delimiters? Because with the field names in separate variables, that can never go wrong, plus your format string becomes even easier to read.

• Learner: Thanks I am getting right output from the above mentioned code block- oh triple single quote is of no use...Thanks

Answer 2

Thanks those who spent their endevour. I have been able to solve the problem. My modified code is as below

 feature = "C:\Users\Name\Desktop\Od\example_shapefile\Polyline_segments_example.shp"
 radius = 500
 # create a dictionary to hold the list of near ids for each input id
 nearest_dict = dict()
 arra = []
 whereClause='''"NEAR_DIST" <= {x} AND "NEAR_DIST" <>{y} AND "NEAR_DIST" <>{z}'''.format(x=radius,y=-1,z=0)
 print radius
 print whereClause
 with arcpy.da.SearchCursor(feature,("NEAR_DIST"),whereClause) as rows:
 for row in rows:
 nearest_id = row[0]
 arra.append(nearest_id)
 del rows
 del row
 print arra

Thanks..

• nickves: So the percentage (%) symbol was the problem?