nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Najděte více příspěvků označených jako

Accepted answers

jskinner

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<SPAN class="punctuation token">.</SPAN>MakeFeatureLayer_management<SPAN class="punctuation token">(</SPAN>sde_isolinien_FC<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"featureLayer"</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">"Name != None"</SPAN><SPAN class="punctuation token">)</SPAN>
results <SPAN class="operator token">=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>GetCount_management<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"featureLayer"</SPAN><SPAN class="punctuation token">)</SPAN>
count <SPAN class="operator token">=</SPAN> int<SPAN class="punctuation token">(</SPAN>results<SPAN class="punctuation token">.</SPAN>getOutput<SPAN class="punctuation token">(</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN>‍‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

All comments

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

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

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.

<SPAN class="keyword token">import</SPAN> cx_Oracle

<SPAN class="comment token">### Build a DSN (can be substituted for a TNS name)&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>
dsn <SPAN class="operator token">=</SPAN> cx_Oracle<SPAN class="punctuation token">.</SPAN>makedsn<SPAN class="punctuation token">(</SPAN>ip<SPAN class="punctuation token">,</SPAN> port<SPAN class="punctuation token">,</SPAN> SID<SPAN class="punctuation token">)</SPAN>
oradb <SPAN class="operator token">=</SPAN> cx_Oracle<SPAN class="punctuation token">.</SPAN>connect<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"user"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"password"</SPAN><SPAN class="punctuation token">,</SPAN> dsn<SPAN class="punctuation token">)</SPAN>
cursor <SPAN class="operator token">=</SPAN> oradb<SPAN class="punctuation token">.</SPAN>cursor<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>

sqlQry <SPAN class="operator token">=</SPAN> <SPAN class="string token">"""select count(name) from isolines where name='2620_1159'"""</SPAN>
cursor<SPAN class="punctuation token">.</SPAN>execute<SPAN class="punctuation token">(</SPAN>sqlQry<SPAN class="punctuation token">)</SPAN>
cxRows <SPAN class="operator token">=</SPAN> cursor<SPAN class="punctuation token">.</SPAN>fetchall<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>

<SPAN class="comment token">#try to count rows</SPAN>
<SPAN class="keyword token">if</SPAN> cursor<SPAN class="punctuation token">.</SPAN>rowcount <SPAN class="operator token">==</SPAN> <SPAN class="number token">0</SPAN><SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp; <SPAN class="keyword token">print</SPAN> <SPAN class="string token">'no rows in cursor'</SPAN>
<SPAN class="keyword token">else</SPAN><SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp; <SPAN class="keyword token">for</SPAN> row <SPAN class="keyword token">in</SPAN> cxRows<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="string token">'do stuff with row'</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

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

BernhardEhrminger

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

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

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.

BernhardEhrminger

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

jskinner

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<SPAN class="punctuation token">.</SPAN>MakeFeatureLayer_management<SPAN class="punctuation token">(</SPAN>sde_isolinien_FC<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"featureLayer"</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">"Name != None"</SPAN><SPAN class="punctuation token">)</SPAN>
results <SPAN class="operator token">=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>GetCount_management<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"featureLayer"</SPAN><SPAN class="punctuation token">)</SPAN>
count <SPAN class="operator token">=</SPAN> int<SPAN class="punctuation token">(</SPAN>results<SPAN class="punctuation token">.</SPAN>getOutput<SPAN class="punctuation token">(</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN>‍‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

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

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.

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

Připojte se k diskusi

Přihlášení členové mohou přispívat, sledovat aktualizace a další. Jste tu noví? Zaregistrujte si bezplatný účet.

Rychlé odkazy

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.