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

[JoshuaBixby]

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.

[VinceAngelo]

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

[JamesCrandall]

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

[JoshuaBixby]

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.

[JamesCrandall]

definitely.