topic Re: arcpy.da.SearchCursor with sql_clause containing SQL "COUNT" in Python Questions

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

BernhardEhrminger — Tue, 05 Sep 2017 13:06:01 GMT

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

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

ChristopherSwint — Tue, 05 Sep 2017 13:29:41 GMT

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

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

BernhardEhrminger — Tue, 05 Sep 2017 14:14:19 GMT

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.

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

JoshuaBixby — Tue, 05 Sep 2017 15:26:43 GMT

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?

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

JakeSkinner — Sun, 12 Dec 2021 02:46:26 GMT

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

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

BernhardEhrminger — Wed, 06 Sep 2017 07:50:23 GMT

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

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

BernhardEhrminger — Wed, 06 Sep 2017 08:44:19 GMT

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.

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

LukeWebb — Wed, 06 Sep 2017 14:03:55 GMT

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.

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

BernhardEhrminger — Wed, 06 Sep 2017 14:23:42 GMT

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

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

VinceAngelo — Wed, 06 Sep 2017 15:44:36 GMT

The purpose of the clause array is to prepend and postpend to the SQL statement. The postpend is used for ORDER BY and GROUP BY. If you add inappropriate content, you will may get an error (depending on what the original SQL would have been). That's less bug than undefined behavior.

- V

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

JoshuaBixby — Sun, 12 Dec 2021 02:46:29 GMT

It is not possible to build the SQL statement you want using cursors. The SQL statements you are building do happen to be valid, but that is by coincidence.

I profiled the following cursor in SQL Server Management Studio:

cur = arcpy.da.SearchCursor(fc,
                            "Field1",
                            "Field1 = 1",
                            sql_clause=("MAX(Field1)", None))‍‍‍‍‍

and what is passed to the database is:

select  MAX(Field1) Field1  from  fc   where (Field1 = 1)‍

which is the same as:

select  MAX(Field1) as Field1  from  fc   where (Field1 = 1)‍

The SQL statement is calculating the maximum of a field, and then renaming that value as the original field name. I tested MIN, MAX, AVG, SUM, and COUNT; and all of the functions worked using ArcGIS 10.5.1 on a SQL Server 2016 enterprise geodatabase. Although they worked, it is a bit of a backwards way of getting a record count in ArcGIS, and I strongly suggest using Get Count for multiple reasons, including performance.

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

JamesCrandall — Sun, 12 Dec 2021 02:46:31 GMT

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

So why don't you just implement that with cx_Oracle?

arcpy is excellent at a lot of things, but perhaps SQL procedural language is not one of them.

import cx_Oracle

### Build a DSN (can be substituted for a TNS name)     
dsn = cx_Oracle.makedsn(ip, port, SID)
oradb = cx_Oracle.connect("user", "password", dsn)
cursor = oradb.cursor()

sqlQry = """select count(name) from isolines where name='2620_1159'"""
cursor.execute(sqlQry)
cxRows = cursor.fetchall()

#try to count rows
if cursor.rowcount == 0:
   print 'no rows in cursor'
else:
   for row in cxRows:
      'do stuff with row'‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

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

JoshuaBixby — Thu, 07 Sep 2017 13:58:57 GMT

If working outside of ArcGIS completely, one would need to make sure they are working with versioned views and not base tables. Even with versioned views, one needs to be using the "correct" version if there are multiple versions. Working outside of ArcGIS/ArcPy is possible, it just adds complexity that can trip up users. As much as I try to rely on native Python, including ArcPy, as much as possible; there are times where using the native geoprocessing tools is best. I think in this case a selection on a feature layer (or table view) followed by Get Count is probably the best path.

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

JamesCrandall — Thu, 07 Sep 2017 14:00:02 GMT

definitely.

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

BernhardEhrminger — Fri, 08 Sep 2017 09:35:29 GMT

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]