arcpy.da.UpdateCursor with sql_clause for artifically inverting cursor loops

Question

Why is row not recognized within my update cursor?

The data is stored in a file geodatabase.
I am using ArcGIS Desktop version 10.1 ArcInfo License.

The screen shot shows the data "ORDER BY X ASC" with some values assigned.
Now I'm trying to reverse sort and continue adding values in the last column.

I know cursors don't work backwards, so I thought reversing the sort might be a good work around.

import arcpy
import itertools

fc = r"C:\Data\Workspace.gdb\MyFeatureClass"
sql_rev = (None, "ORDER BY X DESC")

inc = itertools.count(13,1)
expression = "{0} = 1 AND {1} = 1".format("Gpriority", "FlightID")
with arcpy.da.UpdateCursor(fc, ["FlightLnOrder", "X"], expression, sql_clause = sql_rev) as uCur:
    c = 1
    for row in uCur:
        c += 1
        if c == 2:
            row[0] = inc.next()
            c = 0
            uCur.updateRow(row)         

Answer 1

To properly utilize the arcpy.da.UpdateCursor with an sql_clause you need to ensure your feature class is stored within a database, not a File Geodatabase or as a Shapefile in folder. In the provided solution a Microsoft (JET) Access personal geodatabase is utilized, however other databases can provide improved efficiencies with 64-bit support.

If your data is stored in a File Geodatabase the sql_clause may still function, but it can be unreliable or not work at all when attempting to process rows which already have values written to them.

The following link is provided to reference optional pairs of SQL prefix and postfix clauses organized in a list or tuple. http://resources.arcgis.com/en/help/main/10.1/index.html#//018w00000014000000

Below is the corrected code used to update the above table as shown. Reminder: the above table already had an update cursor run on it with an "ORDER BY X ASC" sql_clause.

import arcpy
import itertools

fc = r"C:\Data\New Personal Geodatabase.mdb\MyFeatureClass"
sql_rev = (None, "ORDER BY X DESC")

inc = itertools.count(13,1)
expression = "{0} = 1 AND {1} = 1".format("Gpriority", "FlightID")
with arcpy.da.UpdateCursor(fc, ["FlightLnOrder", "X"], expression, sql_clause = sql_rev) as uCur:
    c = 1
    for row in uCur:
        c += 1
        if c == 2:
            row[0] = inc.next()
            c = 0
            uCur.updateRow(row)      

Note for multiple arcpy.da.UpdateCursors with an sql_clause:

If you are attempting to use multiple arcpy.da.UpdateCursors, each with their own sql_clause, you need to wrap both cursors within an edit session and all data needs to be stored in a database.

http://resources.arcgis.com/en/help/main/10.1/index.html#//018w00000014000000 "Opening simultaneous insert or update operations on the same workspace using different cursors requires the start of an edit session." http://resources.arcgis.com/en/help/main/10.1/index.html#/Editor/018w00000005000000/