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

3749
14
Jump to solution
09-05-2017 06:06 AM
BernhardEhrminger
New Contributor III

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

0 Kudos
1 Solution

Accepted Solutions
JakeSkinner
Esri Esteemed Contributor

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))‍‍‍‍

View solution in original post

14 Replies
ChristopherSwint
New Contributor

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 

0 Kudos
BernhardEhrminger
New Contributor III

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
Esri Esteemed Contributor

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
New Contributor III

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

0 Kudos
BernhardEhrminger
New Contributor III

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]

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

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
New Contributor III

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
Occasional Contributor III

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
New Contributor III

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