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
Solved! Go to 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))
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.
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.
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))
thank you Jake, and yes creating a intermediate FeatureLayer and counting its features is a usefull alternative
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]
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?
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.
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.
thank you for your reply Luke. So I found a undocumented feature (the working sql_clause=('MAX (NAME)', None))?