Problem with SQL_CLAUSE in data access cursor

StevePeaslee — Mon, 03 Feb 2014 15:30:37 GMT

Has anyone had any luck using the prefix in an SQL_CLAUSE? I am using ArcGIS 10.1 SP1 with a 10.0 File Geodatabase.

I would like to read a single text column from a large polygon attribute using a data access cursor. Originally I used some other Python methods for creating a unique list of sorted values, but when the featureclass got to be very large, performance began to suffer. The larger the list gets, the longer it takes to determine whether a value already exists in the list.

If I correctly understand the SQL_CLAUSE parameter for da cursors, I should be able to use the "DISTINCT" prefix clause to limit the number of records I have to read. The unique MUKEY values will be used to populate a list.

                # Create list of MUKEY values from input mapunit polygon featureclass
                mukeyList = list()
                muCnt = 0
                arcpy.SetProgressor("step", "Reading MUKEY values...", 0, muTotal)

                with arcpy.da.SearchCursor(inputFC, ("MUKEY"), "", "", False, ("DISTINCT", "ORDER BY MUKEY" )) as srcCursor:
                    # Create a unique, sorted list of MUKEY values in the MUPOLYGON featureclass
                    for rec in srcCursor:
                        mukeyList.append(rec[0])
                        muCnt += 1
                        arcpy.SetProgressorPosition()
                        arcpy.SetProgressorLabel("Reading MUKEY values  (" + Number_Format(muCnt, 0, True) + ")")

What's funny is the the ORDER BY postfix is working, but the list ends up containing an MUKEY for every polygon, including all the thousands of duplicate values.

If anyone has gotten the SQL_CLAUSE prefix to work properly, I would greatly appreciate a point in the right direction.

-Steve

topic Problem with SQL_CLAUSE in data access cursor in Geoprocessing Questions

Problem with SQL_CLAUSE in data access cursor