ListFields in Feature Classes in Multiple Feature Datasets in Oracle SDE

Question

I need to create individual reports for feature classes that contain the field "FACILITY_STATE" AND "FACILITY_STATE = 'PPC'". These feature classes span multiple feature datasets within an Oracle SDE.

I am looping through datasets within the SDE connection, then looping through feature classes, and if the feature class contains the field "FACILITY_STATE", using a search cursor, I append each row to a list of rows WHERE "FACILITY_STATE ='PPC'". Write this list to a csv named by feature class. I am fairly new to python and VERY new to working within SDE so I am not sure if I am losing connection or if I am not setting the loops up correctly, but my script runs the way it should until reaching a specific dataset. After that point, my script tells me that each feature class does not contain the FACILITY_STATE field, but I am positive that a good number of them do. What could be going wrong?

# Import modules
import arcpy
import os
import csv

# Roaming SDE environments and variables
arcpy.env.overwriteOutput = True
sde_conn_file = r'C:\Users\caree\AppData\Roaming\ESRI\Desktop10.6\ArcCatalog\gepr.sde'
facilityState = 'FACILITY_STATE'
inclFields = ['OID@', 'FACILITY_STATE', 'DATE_PLACED', 'PLACED_BY', 'DATE_MODIFIED', 'MODIFIED_BY']
arcpy.env.workspace = sde_conn_file

# Proposed construction report
for ds in arcpy.ListDatasets():
    dspath = sde_conn_file + os.sep + ds
    print('Listing feature classes in: {}'.format(dspath))
    for fc in arcpy.ListFeatureClasses('', '', ds):
        flist = [f.name for f in arcpy.ListFields(fc)]
        if facilityState in flist:
            print('Running PPC report for {}...'.format(fc))
            fcRows = []
            with arcpy.da.SearchCursor(fc, inclFields, "FACILITY_STATE = 'PPC'") as cursor:
                for row in cursor:
                    fcRows.append(row)
                    with open(r'C:\giswork\ppcReport\{0}.csv.'.format(fc), 'w', newline='') as csvfile:
                        writer = csv.writer(csvfile)
                        writer.writerow(inclFields)
                        writer.writerows(fcRows)
            print('PPC report created for {}'.format(fc))
        else:
            print('{0} not in {1}'.format(facilityState, fc))

AdminGIS2 · Answer

To debug I would try to print flist as well. I suspect that will always return an empty list if you're sure the field exists. Your 'fc' will probably reference the relative path within the dataset, I'm not familiar with Oracle databases, but perhaps ListFields requires the full path? I.e. dspath/fc?

caree · Answer

Thanks Erik,

I have cleaned up the script a bit and have it working exactly how I need it to, but using a local GDB as the workspace. Now I need to make this work with an Oracle SDE connection instead of my local test GDB.

# Import modules
import arcpy
import csv
import os

# Python overwrite environment
arcpy.env.overwriteOutput = True

# Local variables
gdb = r'C:\giswork\ppcReport.gdb'
facilityState = 'FACILITY_STATE'
inclFields = ['OID@', 'FACILITY_STATE', 'DATE_PLACED', 'PLACED_BY', 'DATE_MODIFIED', 'MODIFIED_BY']

# Python workspace environment
arcpy.env.workspace = gdb

# Proposed construction report
for ds in arcpy.ListDatasets():
    dspath = gdb + os.sep + ds
    print('Listing feature classes in: {}:'.format(dspath))
    for fc in arcpy.ListFeatureClasses('', '', ds):
        flist = [f.name for f in arcpy.ListFields(fc)]
        if facilityState in flist:
            print('Running PPC report for {}...'.format(fc))
            fcRows = []
            with arcpy.da.SearchCursor(fc, inclFields, "FACILITY_STATE = 'PPC'") as cursor:
                for row in cursor:
                    fcRows.append(row)
                    with open(r'C:\giswork\ppcReport\{0}.csv.'.format(fc), 'w', newline='') as csvfile:
                        writer = csv.writer(csvfile)
                        writer.writerow(inclFields)
                        writer.writerows(fcRows)
            print('PPC report created for {}'.format(fc))
        else:
            print('{0} not in {1}'.format(facilityState, fc))

CMV_Erik · Answer

I notice you are using a Try clause without an Except clause, so any errors you run across will be hidden. For starters, you could add something like this between try: and finally:

except Exception as e:
print(e)

You could also run the For loop outside of any Try clauses while you're working out the bugs; the errors that are thrown should help.