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

KamrulKashem · ‎10-04-2012

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"

KamrulKashem · ‎10-05-2012

Anyone!? 🙂

DuncanHornby · ‎10-07-2012

Hi,
I am not particularly great with python so I'm guessing that %d thing in the whereclause of the cursor is some clever pyhton thing going on? My suggestion is to simplify that into something like:

sql = r"FID > " + str(records_return)

Then place the whereclause with sql string in the update cursor, its also makes it a lot easier to understand and debug!

Also check that you are using the correct syntax for the field name, do you put in [] for example (e.g. [FID] > .....)?

Just an idea...

Duncan

KamrulKashem · ‎10-08-2012

Hi,
I am not particularly great with python so I'm guessing that %d thing in the whereclause of the cursor is some clever pyhton thing going on? My suggestion is to simplify that into something like:

sql = r"FID > " + str(records_return)

Then place the whereclause with sql string in the update cursor, its also makes it a lot easier to understand and debug!

Also check that you are using the correct syntax for the field name, do you put in [] for example (e.g. [FID] > .....)?

Just an idea...

Duncan

THANK YOU THANK YOU THANK YOU!
It worked a treat, i had been thinking of doing the SQL expression outside of the function but it skipped my mind to turn it into a string field like that!

Thank you very much Hornbydd!

KamrulKashem · ‎10-08-2012

Hmmm, i knew it was not over! It seems when i turn this into a tool for ArcGIS it gives me errors back down at the deleting rows section!

I have added messages to my script to make it easier to see where it is giving me an error.

Sorry about the cluttered script!

Does anyone know why it is asking for 'row' to be defined in the for in loop? The script works fine outside of the toolbox with set parameters, but now when I test it with parameters, using the same ones as I did before but selecting them in the tool window instead i get errors!

Here is my script updated with arcpy.AddMessage functions

import arcpy
import os

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



in_features = arcpy.GetParameterAsText(0)
near_features = arcpy.GetParameterAsText(1)
search_radius = arcpy.GetParameterAsText(2)
records_return = arcpy.GetParameterAsText(3)

out_table = r"PathtoOutTable.dbf"

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


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

arcpy.AddMessage("Deleting Previous Tables")
for tbl in arcpy.ListTables():
    try:
        print tbl
        if tbl != "TestingSort":
            arcpy.Delete_management(tbl)
        
    except arcpy.ExecuteError: 
        arcpy.GetMessages(2)

del tbl
arcpy.AddMessage("Completed Deleting Tables")

arcpy.AddMessage("Processing Sort")
arcpy.Sort_management(out_table, "sorted_out_table", ["DISTANCE"])
arcpy.AddMessage("Completed Sorting Tables")

sorted_out_table = "sorted_out_table"
 
arcpy.AddMessage("Adding new field FID")
arcpy.AddField_management(sorted_out_table,"FID","DOUBLE","","","","","","","")
arcpy.AddMessage("Completed New Field FID")


arcpy.AddMessage("Calculating FID")
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")
arcpy.AddMessage("Completed Calculation")


arcpy.AddMessage("Search Cursor to delete rows")
sql = r"FID > " + str(records_return)
rows = arcpy.UpdateCursor(sorted_out_table,sql,"","")
arcpy.AddMessage("Processing Update Cursor")



for row in rows:
    rows.deleteRow(row)
del row, rows
    
arcpy.AddMessage("Deleted Rows")

The error i am getting in the Geoprocessing window shows that the script gets to the message ''Processing Update Cursor" and fails on the for row in rows section.

What can i do to fix this?

Error:

Executing: PointDistance
Start Time: Mon Oct 08 11:14:52 2012
Running script PointDistance...
Processing Point Distance
Completed Point Distance
Processing Sort
Deleting Previous Tables
Completed Deleting Tables
Completed Sorting Tables
Adding new field FID
Completed New Field FID
Calculating FID
Completed Calculation
Search Cursor to delete rows
Processing Update Cursor
<type 'exceptions.NameError'>: name 'row' is not defined
Failed to execute (PointDistance).
Failed at Mon Oct 08 11:15:41 2012 (Elapsed Time: 49.00 seconds)

AndrewKeith3 · ‎04-06-2016

Remove row from rows.deleteRow(row)

for row in rows:  
    rows.deleteRow()  
del row, rows