3

I am attempting to write a script that processes a lot of different feature classes in a geodatabase. Most of the feature classes have the same schema as the user connecting to the database

geodatabase.myschema.featureclass

however some feature classes have a schema belonging to a different user

geodatabase.differentschema.featureclass2

When using arcpy with a .sde connection and user that owns the default schema, I do not need to specify the schema name when referencing feature classes in arcpy tools.

import arcpy, os
db = r"D:\GIS\DBConns\geodatabase.sde"
fc = "featureclass"
fcpath = os.path.join(db, fc)

result = int(arcpy.GetCount_management(fcpath).getOutput(0))
print result

> 147

However if I reference the feature class with a different schema, the above doesn't work

Runtime error Traceback (most recent call last): File "<string">, line 6, in <module> 
File "C:\program files (x86)\arcgis\desktop10.3\arcpy\arcpy\management.py", line 15370, in GetCount
raise e Execute Error:
Failed to execute. Parameters are not valid.
Error 000732: Input Rows:
D:\GIS\DBConns\geodatabase.sde\featureclass2 does not exist or is not supported
Failed to execute (GetCount).

If I change the fc variable to include the schema name e.g. fc = "differentschema.featureclass2" then it works no problem.

Is there a way to quickly determine the schema name for a feature class from arcpy so that the tools work correctly?

I tried doing an arcpy.ListFeatureClasses("*featureclass2") however this adds a lot of time to the process (the gdb has a lot of feature classes, many of which are in Feature Datasets also), so would like to avoid that if possible.

2
  • Esri does not support schemas other than the login, so you're at the cusp of undefined behavior. I've loaded PG tables with other than the login schema, but only by loading to a scratch table then doing an INSERT .. SELECT from the scratch table.
    – Vince
    Mar 21, 2017 at 22:01
  • @Vince we have different users that have create rights, and as put per Esri they have their own schema in the database to be able to create feature classes. I am trying to access all feature class regardless of who created them. We haven't gone outside Esri's guidance in this setup
    – Midavalo
    Mar 21, 2017 at 23:22

2 Answers 2

1

You can always describe the SDE workspace and get all children recursively from the describe object. Faster than repeated calls to ListFC.

import arcpy
from pprint import pprint

gdb = r"C:\PostgreSQL.sde"

children = {}

# Walk the workspace and build dictionary of {name : catalog path}
# rpartition is perfect for extracting this information!
for item in arcpy.Describe(gdb).children:
    children[item.baseName.rpartition('.')[-1]] = item.catalogPath
    for grandchild in item.children:
        children[grandchild.baseName.rpartition('.')[-1]] = grandchild.catalogPath

pprint(children)

for fc in ['test', 'baz']:
    if fc in children:
        path = children[fc]
        count = arcpy.GetCount_management(path)[0]
        print("There are {} features in {}".format(count, path))


{'baz': 'C:\\PostgreSQL.sde\\foo.sde.baz',
 'myFD': 'C:\\PostgreSQL.sde\\foo.un.myFD',
 'test': 'C:\\PostgreSQL.sde\\foo.un.myFD\\foo.un.test'}
There are 0 features in C:\PostgreSQL.sde\foo.un.myFD\foo.un.test
There are 0 features in C:\PostgreSQL.sde\foo.sde.baz
3
  • +1 Oh nice, I'd not considered a Describe on the gdb itself. Takes 2+ minutes to just run your code, so quicker than before but still very slow
    – Midavalo
    Mar 21, 2017 at 19:53
  • @Midavalo Wow, that must be one complex workspace. I've talked to EGDB on remote machines and describing it has never taken more than 10 seconds.
    – Paul
    Mar 21, 2017 at 20:30
  • only about 300 feature classes, but includes at least 3 geometric networks and 2 road networks
    – Midavalo
    Mar 21, 2017 at 20:34
1

Inspired by @Paul's answer (which worked but was still quite slow) I made an attempt which queried the database Table Registry sde_table_registry to get the list of tables.

import arcpy, os

gdb = r"D:\GIS\DBConns\geodatabase.sde"
arcpy.env.workspace = gdb

sqlconn = arcpy.ArcSDESQLExecute(gdb)

query = """ SELECT OWNER, TABLE_NAME FROM sde.SDE_TABLE_REGISTRY """

resultDict = {str(item[1]).upper(): str(item[0]).upper() for item in sqlconn.execute(query)}

for fc in ['featureclass', 'featureclass2']:
    if fc.upper() in resultDict:
        path = "{0}.{1}".format(resultDict[fc.upper()], fc)
        print path
        count = arcpy.GetCount_management(path)[0]
        print("There are {} features in {}".format(count, path))

Not an ideal way to do it, but much faster (4s vs 2m+). I did like @Paul's answer as it kept it within the arcpy/ArcGIS logic rather than stepping into SQL, but the time difference makes all the difference here!

3
  • SQL Execute is pretty handy, wow.
    – Paul
    Mar 21, 2017 at 21:42
  • @Paul I probably use SQL Execute too often in my code, but it's quick and gives me the desired result. Could be dangerous if used incorrectly though!
    – Midavalo
    Mar 21, 2017 at 21:44
  • Of course, this only captures tables which have been registered, which is different than what ListTables will return.
    – Vince
    Mar 21, 2017 at 23:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.