Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I need to specify a "group by" clause for UpdateCursor arcpy.da. From here I found out that it's possible to do using an optional pair of SQL prefix and postfix. But there are no examples and I'm stuck with the syntaxis problem. Can, please, smbd help me?

PS. I've tried various modifications of "(None, 'GROUP BY "INPUT_FID"')", but still can't get it.

Thank you in advance.

Here is the part of the code I'm using:

oDSFields = ('INPUT_FID','NEAR_FID','DISTANCE', popTotalField, popBlacksField)

kernel = 0
alpha = 0.005
#, "(None, 'GROUP BY "INPUT_FID"')"
with arcpy.da.UpdateCursor("outDistanceSorted", oDSFields, "(None, 'GROUP BY "INPUT_FID"')") as cursor:

    for row in cursor:

        kernel = float(math.exp(-0.5*math.pow(row[2]/alpha, 2)))
share|improve this question
2  
It looks like you are passing your group by as the where part. Try this: arcpy.da.UpdateCursor("outDistanceSorted", oDSFields,sql_clause="(None, 'GROUP BY "INPUT_FID"')" –  Nathan W Aug 4 '13 at 22:35
    
for anyone looking for an answer: the way to do this is by including the objectID in the sql clause even though you don't want to show it, it won't work otherwise... sql = [None,"Group BY r_Identity, ObjectID"] see: forums.arcgis.com/threads/… –  jonatr Apr 13 at 9:46

2 Answers 2

As @NathanW pointed out, you are trying to pass your sql_clause to the where_clause parameter. I've found two posts (here and here) that show passing the sql_clause parameter as a tuple (the help also agrees), not a string, so you might want to try the following:

sql = (None, 'GROUP BY "INPUT_FID"')
with arcpy.da.UpdateCursor("outDistanceSorted", oDSFields, sql_clause=sql) as cursor:

If that doesn't work, your syntax should include some escape characters if you want quotations around INPUT_FID (these are a few ways you can do it):

"(None, 'GROUP BY \"INPUT_FID\"')"

or

"""(None, 'GROUP BY "INPUT_FID"')"""
share|improve this answer
    
Thank you, guys, for your help. I figured that I was passing GROUP BY statement to the wrong parameter. Such a silly mistake. Although now it says: RuntimeError: An invalid SQL statement was used. [SELECT INPUT_FID, NEAR_FID, DISTANCE, VD01, VD06, LOC_J, LOC_BLACKS_J, OBJECTID FROM outDistanceSorted GROUP BY INPUT_FID] I used to work with SQL before and can't see why this statement doesn't work. I tried different specifications as @Paul said, but nothing works. I also tried to enclose INPUT_FID in quotations a separate string and include OBJECTID field. –  Eugene Kudko Aug 5 '13 at 1:16
    
Actually, I need to make something like this: "SELECT INPUT_FID, SUM(LOC_J) FROM outDistanceSorted GROUP BY INPUT_FID". Is there the way I can do this? Summary statistics (analysis toolbox) doesn't group fields and sql_clause in da.UpdateCursor doesn't allow to use the SUM() function. –  Eugene Kudko Aug 5 '13 at 1:37
1  
@EugeneKudko, I'd try to get a simple sql_clause working before implementing other stuff. –  Paul Aug 5 '13 at 1:46
    
I have no clue why the statement doesn't work. I checked the field names and they are correct. The GROUP BY statement works if I specify only one filed "OBJECTID" in the list of fields to select from, but when I include others it gives me an error. –  Eugene Kudko Aug 5 '13 at 23:59
up vote 0 down vote accepted

Solved the problem with a much easier way. ArcGIS has a perfect tool "Statistics" in the "Analysis" toolbox, which is very flexible and was used here as the substitution for SQL

...SUM(field1)...GROUP BY field2.

Thank you everybody for useful comments and suggestions.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.