Arcpy SQL Clause not working

JakeBryson — Sun, 25 Aug 2013 19:53:40 GMT

Hi All

I am running the Arcpy below in the Catalougue Python window but the result is unexpected. Basically it looks as if the variable rSQL is recognised, however the native underlying SQL functions of Distinct and Group By arn't being interpreted correctly as native SQL functions. Does anyone have any ideas as I'm really stuck. I'd also like to wrap this code in a loop which lists all Distinct layers for each feature class within a File Geodatabase basically so the output looks like.

FeatureClassName1,DistinctLayerName1
FeatureClassName1,DistinctLayerName2
FeatureClassName1,DistinctLayerName3
FeatureClassName1,DistinctLayerName4
FeatureClassName1,DistinctLayerName5
FeatureClassName2,DistinctLayerName1
FeatureClassName2,DistinctLayerName2
FeatureClassName...n...n,DistinctLayerName...n...n

Thanks Jake

>>> import arcpy
... fc = "D:\CAD_Data_Anthony_Ramen\CAD_Data_Examples.gdb\Akaroa_Street_GIS_dwg_Annotation"
... namefield = "Layer"
... rSQL = ('Distinct','GroupBy')
... cursor = arcpy.da.SearchCursor(fc,namefield,None,None,None,rSQL)
... for row in cursor:
... print (row[0])
...
3
3
PROPERTY_STREET_NUMBER_LABEL_$1
3
3
3
PROPERTY_STREET_NUMBER_LABEL_$1
3
TEXT
3
3
PROPERTY_STREET_NUMBER_LABEL_$1
0
marking
STREET_NAME_LABEL_LABEL
3
3
STREET_NAME_LABEL_LABEL
STREET_NAME_LABEL_LABEL
PROPERTY_STREET_NUMBER_LABEL_$1
PROPERTY_STREET_NUMBER_LABEL_$1
STREET_NAME_LABEL_LABEL
PROPERTY_STREET_NUMBER_LABEL_$1
dimension
TEXT
STREET_NAME_LABEL_LABEL

Re: Arcpy SQL Clause not working

JamesCrandall — Mon, 26 Aug 2013 10:45:39 GMT

Please list the exact Feature Class name, its storage container type and the where clause you'd like to filter by. The way you have it now is an invalid SQL statement as the "SELECT *" is hardcoded into it and cannot include DISTINCT or GROUP BY, this requires a subquery (which also depends upon the storage database you are using).

http://resources.arcgis.com/en/help/main/10.1/index.html#//00s50000002t000000

http://resources.arcgis.com/en/help/main/10.1/index.html#//00s500000033000000

Re: Arcpy SQL Clause not working

oscarespitia — Tue, 01 Oct 2013 15:08:53 GMT

hi james

would you give me a example for 'group by' with a subquery?, please

topic Arcpy SQL Clause not working in Python Questions

Arcpy SQL Clause not working

Re: Arcpy SQL Clause not working

Re: Arcpy SQL Clause not working