paulbrandt73

Group By functionality of da.cursors sql_clause

Discussion created by paulbrandt73 on Feb 17, 2013
Latest reply on Jun 11, 2014 by wantal
I must be doing something wrong. I am trying to use the Group By functionality of the new da.SearchCursor, but keep getting the error:
RuntimeError: Underlying DBMS error [Microsoft SQL Server Native Client 11.0: Column 'demo.test.PRCL_AL_WEST.OBJECTID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.] [demo.test.PRCL_AL_WEST][STATE_ID = 88181]

I don't understand this as I am not including OBJECTID in my field list, and it wouldn't make sense to include it in my Group By.

The problem I am trying to solve is; I have a bunch of feature classes in different datasets that are identical except that they have different projections (don't ask, I can't change this part), so I get the list of feature classes in the fcs variable by using the da.Walk functionality (really cool), filtered with the fnmatch module, and am trying to get record counts to track progress by date (removing time portion), user and create method code.

I know there are other tools like Statistics that can be used, but I am mostly just experimenting with the new 10.1 functionality, and would like to get this working, as it has lots of potential.

In the code below conn is a connection to a sqlite3 in-memory database and c is the cursor for it, I am inserting the individual feature class results into the sqlite table, then doing an aggregate to get the total counts from there, later in the script.

fieldList = ["COUNT(*) as GroupCount", "CONVERT(DATE, CRET_TS) as CRET_TS", "GISF_CRET_METH_CD", "BWCreator"]
sqlclaus = [None, "GROUP BY CONVERT(DATE, CRET_TS),GISF_CRET_METH_CD,BWCreator"]
#Gather the counts from the individual feature classes
for fc in fcs:
    for row in arcpy.da.SearchCursor(fc, fieldList, sql_clause=sqlclaus):
        #insert the individual feature class counts into the sqlite table
        vals = [fc]
        vals.extend(row)
        c.execute("INSERT INTO rslt VALUES(?,?,?,?,?)", vals)
        conn.commit()


Any help you can provide is much appreciated.

Thank you.

Outcomes