kamkashem

Trouble with using Update cursor to delete rows. - URGENT help required !

Discussion created by kamkashem on Oct 4, 2012
Latest reply on Apr 6, 2016 by akk602dev
Hi,

I am trying to develop a python script that I can create into a tool later on.

General Process:

Using the arcpy.PointDistance_analysis tool

1. User selects input feature and near feature.
2. User sets search radius
3. User sets the number of records to return in the table rather than all the records


The tool then creates the output table.

Sadly, it does not allow sorting before hand so i used the sort function.

I have a few loops regarding the sort due to not understanding why the function does not overwrite the previous table called 'sorted_out_table'.

After the sort i need a new unique identifier as the OBJECT ID becomes obsolete to use as it is not in order.. So i create an FID and include a code block to create sequential numbering

Finally i want to create a update cursor to delete the rows that are higher than the user specified, i.e. 5

I have commented out the 'getparameter' functions and used examples just to test it.

This is the error I receive:

Processing Point Distance
Processing Sort
sorted_out_table
TestingSort
Completed
Update Cursor
Adding new field FID
Calculating FID
Deleting Rows
Traceback (most recent call last):
  File "H:\Pointdistancescript.py", line 63, in <module>
    rows = arcpy.UpdateCursor(sorted_out_table,"FID > '%d'" %(int(records_return),),"","")
  File "C:\Program Files\ArcGIS\Desktop10.0\arcpy\arcpy\__init__.py", line 841, in UpdateCursor
    return gp.updateCursor(*args)
  File "C:\Program Files\ArcGIS\Desktop10.0\arcpy\arcpy\geoprocessing\_base.py", line 362, in updateCursor
    self._gp.UpdateCursor(*gp_fixargs(args)))
RuntimeError: ERROR 999999: Error executing function.
An invalid SQL statement was used.
An invalid SQL statement was used. [sorted_out_table]
>>>

It gets right down to the Update cursor successfully.. Why is it failing?

HERE is the code

import arcpy
import os

arcpy.env.workspace = r"pathto.gdb"
arcpy.env.overwriteOutput = True



#    in_features = arcpy.GetParameterAsText(0)
in_features = r"pathtoPointDistance.shp"
#    near_features = arcpy.GetParameterAsText(1)
near_features = r"pathtonearfeatures.lyr"


#    search_radius = arcpy.GetParameterAsText(2)
search_radius = "20 Meters"

#records_return = arcpy.GetParameterAsText(3)
records_return = 5

#    out_table = arcpy.GetParameterAsText(4)
out_table = r"pathtoPointDistanceAnalysis\OUTPUTTABLE"

print "Processing Point Distance"
arcpy.PointDistance_analysis(in_features, near_features, out_table, search_radius)


print "Processing Sort"

arcpy.env.workspace = r"pathto.gdb"
arcpy.env.overwriteOutput = True

# I have to delete the table from memory because the sort function does not allow overwrite?

for tbl in arcpy.ListTables():
    try:
        print tbl
        if tbl != "OUTPUTTABLE": #
            arcpy.Delete_management(tbl)
        
    except arcpy.ExecuteError: 
        arcpy.GetMessages(2)


#Sort by distance - This messes up the order of OBJECT ID

arcpy.Sort_management(out_table, "sorted_out_table", ["DISTANCE"])

print "Completed"

sorted_out_table = "sorted_out_table"

print "Update Cursor"


print "Adding new field FID"

arcpy.AddField_management(sorted_out_table,"FID","DOUBLE","","","","","","","")


print "Calculating FID"

#Calculate FID using codeblock - THIS WORKS - TESTED

arcpy.CalculateField_management(sorted_out_table, "FID", "autoIncrement()", "PYTHON_9.3", "rec=0\\ndef autoIncrement():\\n global rec\\n pStart = 1 #adjust start value, if req'd \\n pInterval = 1 #adjust interval value, if req'd\\n if (rec == 0): \\n  rec = pStart \\n else: \\n  rec = rec + pInterval \\n return rec\\n")

print "Deleting Rows"


#FAILS BELOW

rows = arcpy.UpdateCursor(sorted_out_table,"FID > '%d'" %(records_return,) ,"","")


for row in rows:

    print row.getValue("FID")
    rows.deleteRow(row)

del row, rows
    

print "Done Updating now deleting"


print "Deleted"

Outcomes