arcpy.da.SearchCursor with sql_clause containing SQL "COUNT"

[BernhardEhrminger]

I am working with ArcGIS 10.4.1.

The following statement:

[row[0] for row in arcpy.da.SearchCursor(in_table=sde_isolinien_FC,

                                                                     field_names=['NAME', ],

                                                                     where_clause='NAME=\'{0}\''.format(name),

                                                                     sql_clause=('COUNT (NAME)', None))]

forces c:\Python27\ArcGISx6410.4\python.exe to crash. 

However, since I am only interested in if there are records or not, the following statement is a workaround:

[row[0] for row in arcpy.da.SearchCursor(in_table=sde_isolinien_FC,

                                                                  field_names=['NAME', ],

                                                                  where_clause='NAME=\'{0}\''.format(name),

                                                                  sql_clause=('MAX (NAME)', None))

Is this a error/missunderstanding on my side or a bug in arcpy?

Thank You Bernhard

[ChristopherSwint]

My guess is that there's an issue with the COUNT function being passed in the sql clause.  (I've run into similar issues with SQL functions).

If you only care about if records exist, I would suggest using "Get Count" from arcpy.  It's going to be faster than creating a cursor and dealing with potential issues/locks.

Get Count—Help | ArcGIS Desktop 

[BernhardEhrminger]

Thank You for your reply Christopher,

in my opinion getcount is not an optimal solution because of the where_clause, and I am not interested in transfering a possibly large number of features to the arcpy-client.

[JakeSkinner · Accepted solution]

Hi Bernhard,

I would still give the GetCount function a try.  It's pretty fast.  You could create a feature layer with your where statement, and then pass the feature layer to the GetCount function.  Ex:

arcpy.MakeFeatureLayer_management(sde_isolinien_FC, "featureLayer","Name != None")
results = arcpy.GetCount_management("featureLayer")
count = int(results.getOutput(0))‍‍‍‍‍‍‍

[BernhardEhrminger]

thank you Jake, and yes creating a  intermediate FeatureLayer and counting its features is a usefull alternative

[BernhardEhrminger]

Jake,

you are right: the FeatureLayer/GetCount recipe has excellent performance.

Thank You, Bernhard

sde_connection = r'O:\_______.sde'

tiles = [u'2710_1253', u'2727_1227', u'2666_1193', u'2780_1170', u'2740_1214',

u'2670_1263', u'2737_1235', u'2764_1215', u'2623_1217', u'2621_1190']

if __name__ == '__main__':

def f1():

for name in tiles:

con = arcpy.ArcSDESQLExecute(sde_connection)

n = con.execute('SELECT COUNT (NAME) FROM ISOLINES WHERE NAME = \'{0}\''.format(name))

return

def f2():

for name in tiles:

arcpy.Delete_management('out_layer')

arcpy.MakeFeatureLayer_management(in_features=sde_isolinien_FC,

out_layer='out_layer',

where_clause='NAME = \'{0}\''.format(name))

n = arcpy.GetCount_management('out_layer').getOutput(0)

return

r1 = timeit.repeat(stmt='f1()', setup='from __main__ import f1',repeat=5,number=1)

r2 = timeit.repeat(stmt='f2()', setup='from __main__ import f2',repeat=5,number=1)

print r1

print r2

the output:

[2.3578824236148948, 0.663463898293061, 0.6561606928581454, 0.6562781213256437, 0.6874187019173439]

[12.34176151757427, 0.5830196624761683, 0.6405659508762618, 0.6435843756744966, 0.5681143976659193]

[JoshuaBixby]

I think you are trying to build an invalid SQL statement.  Can you write out the SQL statement (just straight SQL as if doing it outside of ArcGIS) you want to build?

[BernhardEhrminger]

Joshua thank for your reply.

Actually for example "select count(name) from isolines where name='2620_1159'" works perfectly against Oracle.

Since I only need to know if there are records or not, the SQL statement

select max(name) from isolines where name='2620_1159'

is a usefull alternative (by checking for empty results as shown in python below).

if [row[0] for row in arcpy.da.SearchCursor(in_table=sde_isolinien_FC,

field_names=['NAME', ],

where_clause='NAME=\'{0}\''.format(name),

sql_clause=('MAX (NAME)', None))][0] is None:

print 'empty tile: ', name

else:

print 'finished tile: ', Name

Since both SQL statements are homologous I conclude there is a bug/limitation in arcpy.da.SearchCursor.

[LukeWebb]

I think the bug here is that either of the statements work at all with the cursor!  (So its a documented limitation)

arcpy.da.SearchCursor Doc:

SQL prefix supports None, DISTINCT, and TOP.   SQL postfix supports None, ORDER BY, and GROUP BY.

[BernhardEhrminger]

thank you for your reply Luke. So I found a undocumented feature (the working sql_clause=('MAX (NAME)', None))?