1

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

  • 2
    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. – Roel Jul 29 '14 at 8:25
  • 1
    @Roel I think you could have provided this as an answer, and of course, still can. – PolyGeo Jul 29 '14 at 8:32
  • 1
    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 :) – Roel Jul 29 '14 at 9:11
3

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.

  • Thanks I am getting right output from the above mentioned code block- oh triple single quote is of no use...Thanks – SIslam Jul 29 '14 at 9:22
1

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

  • So the percentage (%) symbol was the problem? – nickves Oct 14 '15 at 13:05

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.