using join by in arcpy.da.searchcursor() - what is the right syntax?

[yonatanrubinstein]

Hi, I'm trying to run a code that uses a "join by statement" inside a searchcursor (it was done to imitate the join by inside the iterate features tool in the modelbuilder). I've tried this code (as a trial before implementing it on the real script), but I can't seem to find the right syntax or any documentation.

import arcpy  fc = "D:\\Projects\\proj1\\SumsOfRoutes.gdb\\all_routes_1stBatch" field = "r_Identity" field2 = "Shape_Length" ##sql = "(None, 'GROUP BY \"r_Identity\"')" sql = "(None,'Group BY r_Identity')"   fields=["*"] cursor = arcpy.da.SearchCursor(fc,fields,sql_clause=sql)   # print r_Identity and Shape_Length of each feature in feature class for row in cursor:     print(str(int(row.getValue(field))) + ":  " + str(row.getValue(field2)) + " meters")

Does anybody know how it should be written?


EDIT: for the script above, I get the following error:

TypeError: 'sql_clause' should be sequense of 2 strings(prefix, postfix)
Thanks.

[MathewCoyle]

You must list all fields that will be included in the sql_clause parameter in the fields parameter.

fc = r'H:\Documents\ArcGIS\Default.gdb\RainGauges'
fields = ('Data_Type', 'ObjectID')
sql=[None, "GROUP BY Data_Type, ObjectID"]
cursor = arcpy.da.SearchCursor(fc, fields,sql_clause=sql)

for row in cursor:
    print row[0]

[JamesCrandall]

You must list all fields that will be included in the sql_clause parameter in the fields parameter.

fc = r'H:\Documents\ArcGIS\Default.gdb\RainGauges'
fields = ('Data_Type', 'ObjectID')
sql=[None, "GROUP BY Data_Type, ObjectID"]
cursor = arcpy.da.SearchCursor(fc, fields,sql_clause=sql)

for row in cursor:
    print row[0]

Matthew,

I am still not understanding how this will correctly perform any GROUP BY because of the requirement include the unique values in ObjectID field.  How is this ever going to group on the target fields????

[MathewCoyle]

You are right I was using ORDER BY.

[yonatanrubinstein]

You cannot include Shape_Length (see bold above) in your select because it is not included in your GROUP BY portion.  Remove it and see if it at least completes without error.

OK, THANKS I GET AN ANSWER BY RUNNING THE BELOW TEXT:

import arcpy

fc = "D:\\Projects\\proj1\\SumsOfRoutes.gdb\\all_routes_1stBatch"
field1 = "r_Identity"
##field2 = "Shape_Length"

sql = [None,"Group BY r_Identity, ObjectID"]


fields=[field1]
cursor = arcpy.da.SearchCursor(fc,fields,sql_clause=sql)


# print r_Identity and Shape_Length of each feature in feature class
for row in cursor:
    print str(row)

Only I don't understand the thing with the OBJECTID,

and more so, this constellation DOES NOT HELP ME, AS I CAN'T REACH THE OTHER FIELDS OF THE FEATURES.

substitute field1 with "*" brings back the runtime error

[MathewCoyle]

OK, THANKS I GET AN ANSWER BY RUNNING THE BELOW TEXT:

import arcpy

fc = "D:\\Projects\\proj1\\SumsOfRoutes.gdb\\all_routes_1stBatch"
field1 = "r_Identity"
##field2 = "Shape_Length"

sql = [None,"Group BY r_Identity, ObjectID"]


fields=[field1]
cursor = arcpy.da.SearchCursor(fc,fields,sql_clause=sql)


# print r_Identity and Shape_Length of each feature in feature class
for row in cursor:
    print str(row)

Only I don't understand the thing with the OBJECTID,

and more so, this constellation DOES NOT HELP ME, AS I CAN'T REACH THE OTHER FIELDS OF THE FEATURES.

substitute field1 with "*" brings back the runtime error

You cannot use '*' when using sql_clause. You must specify each field explicitly.

[JamesCrandall]

This is truly a strange and confusing method available for the da.SearchCursor.  I mean the GROUP BY clause is normally applied because you are attempting to summarize things (say SUM or AVERAGE, or other some such metric).  But it is entirely weird to require ObjectID which would negate any ability to correctly perform such aggragetion, let alone not even be able to include such a thing as COUNT, MIN, MAX, AVERGE, etc...

I hope I am simply missing the obvious here.

[yonatanrubinstein]

You cannot use '*' when using sql_clause. You must specify each field explicitly.

OK, I GOT IT (I THINK). The same fields should be in the "group-by" part and in the "fields" part.

THANK YOU JAMES, THANK YOU MATHEW

it is very confusing, and the ObjectID part is weird, I wouldn't have got it on my own in a million years. Thanks.

[MathewCoyle]

The OBJECTID should not be required, if it ever was it is a bug and is fixed in 10.2.1.

[JamesCrandall]

The OBJECTID should not be required, if it ever was it is a bug and is fixed in 10.2.1.

It is required/an issue at 10.1 SP1