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)?

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.

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..