Do I want to set the database connection as an environment in order to loop through feature classes?

PaulHuffman · ‎12-15-2017

After mass loading a bunch of feature classes into a enterprise geodatabases (10.3.1, MSSQL), I need to add privileges for a role to many of them. I tested ChangePrivileges with a single feature class, it worked, so now I thought I would extend this by getting a list of feature classes, then using a For loop on the list to grant the privileges. But it didn't wok to set up the correct database connection as a env.workspace. What's the correct way to loop through a enterprise geodatabase to add permissions? Or do I want to do this in a totally different way? The following script gives a TypeError: 'NoneType' object is not iterable on debugging line 17, indicating I didn't get anything into the list.

# ModPriv2lp.py
# Testing how to modify enterprise geodatabase privileges with python
# Paul Huffman, 2017-12-14

import arcpy
from arcpy import env
env.workspace = "Database Connections/Streams as GisOwner.sde"

#get list of feature classes in geodatabase.
fclist = []
fclist = arcpy.ListFeatureClasses ("Database Connections/Streams as GisOwner.sde")

#datasetName = "Database Connections/Streams as GisOwner.sde/Streams.GISOWNER.str37_route_rivers"
#datasetName = "Streams.GISOWNER.str37_route_rivers"
#arcpy.ChangePrivileges_management(datasetName, "GISUsers", "GRANT", "AS_IS")

for fc in fclist:
    print fc
    #arcpy.ChangePrivileges_management(fc, "GISUsers", "GRANT", "AS_IS")‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

PaulHuffman · ‎12-18-2017

I tried it with walk. Almost the same thing, although the walk version ran without errors from IDLE, the feature class list was empty, and permissions were not updated. If I copy and paste the python lines into ArcCatalog Python window, the lines run to completion, the print filename line shows the current feature class getting processed, and the privileges get added. It's as if the Database connection is a property that only ArcCatalog knows about. Arcpy and os don't know about the database connections.

# -*- coding: utf-8 -*-
# ModPrivlpwalk.py
# Testing how to modify enterprise geodatabase privileges with python
#  this time with python Walk rather than ListFeatureClass
# Paul Huffman, 2017-12-18

# Set the necessary product code
import arceditor
import os

import arcpy
from arcpy import env
env.workspace = "Database Connections/Streams as GisOwner.sde"
ws = "Database Connections/Streams as GisOwner.sde"

#get list of feature classes in geodatabase.
fclist = []
walk = arcpy.da.Walk(ws, datatype="FeatureClass")
#fclist = arcpy.ListFeatureClasses ("Database Connections/Streams as GisOwner.sde")
#fclist = arcpy.ListFeatureClasses ()

#The folling line worked by setting connection + feature class in a string for ChangePrivileges
#datasetName = "Database Connections/Streams as GisOwner.sde/Streams.GISOWNER.str37_route_rivers"
#datasetName = "Streams.GISOWNER.str37_route_rivers"
#arcpy.ChangePrivileges_management(datasetName, "GISUsers", "GRANT", "AS_IS")

#for fc in fclist:
for dirpath, dirnames, filenames in walk:
    for filename in filenames:
        print filename
        arcpy.ChangePrivileges_management(filename, "GISUsers", "GRANT", "AS_IS")
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

PaulHuffman · ‎12-22-2017

I asked ESRI, and Rex found the correct syntax for adding a enterprise geodatabase connection as a env.workspace. to get this to run outside of ArcGIS Desktop, you need to read in the connection file. But you must also ensure that the connection is made with the "Save username / password" option checked in the connection properties. Otherwise, the connection will try to promprt you for the username/password, and fail in a script.

To find the connection file path, " in ArcCatalog- right click the connection file < Properties < Copy the file path). " In my case this was C:\Users\huffm\AppData\Roaming\Esri\Desktop10.3\ArcCatalog\Streams as GisOwner.sde. Knowing this will open up all kinds of SDE geodatabase management and processing for me. Couldn't find this anywhere in Help. Also note, env.Workspace doesn't work, it needs to be env.workspace.

In this script, you can see several ways I tried to get the syntax correct that in the commented out in lines 12 - 14, then the last env.workspace in line 15 is the correct way, going to the connection file. This script successfully populates the fclist, and prints list elements out to the Python Shell. Remove the comment on line 29 and it will grant privileges to everything in the geodatabase connection.

# -*- coding: utf-8 -*-
# ModPriv2lp.py
# Testing how to modify enterprise geodatabase privileges with python
# Paul Huffman, 2017-12-14

# Set the necessary product code
import arceditor
import os

import arcpy
from arcpy import env
#env.workspace = "Database Connections/Streams as GisOwner.sde"
#env.workspace = "Database Connections\\Streams as GisOwner.sde"
#env.workspace = r"Database Connections\Streams as GisOwner.sde"
env.workspace = r"C:\Users\huffm\AppData\Roaming\Esri\Desktop10.3\ArcCatalog\Streams as GisOwner.sde"

#get list of feature classes in geodatabase.
fclist = []
#fclist = arcpy.ListFeatureClasses ("Database Connections/Streams as GisOwner.sde")
fclist = arcpy.ListFeatureClasses ()

#The folling line worked by setting connection + feature class in a string for ChangePrivileges
#datasetName = "Database Connections/Streams as GisOwner.sde/Streams.GISOWNER.str37_route_rivers"
#datasetName = "Streams.GISOWNER.str37_route_rivers"
#arcpy.ChangePrivileges_management(datasetName, "GISUsers", "GRANT", "AS_IS")

for fc in fclist:
    print fc
    #arcpy.ChangePrivileges_management(fc, "GISUsers", "GRANT", "AS_IS")
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

PaulHuffman · ‎02-06-2019

I was just running this script at another office, and extending it to include other data besides feature classes, and it was running like a champ. Today I noticed it was running without reading my connection file, but just getting the connection from ArcCatalog. I think the difference is that I was finding the python file with File Explorer, right clicking on it, I get choices that include Edit with IDLE, Edit with IDLE (ArcGIS Pro), Run with ArcGIS Pro. If I select "Edit with IDLE", I think it gets opened in an ArcDesktop type environment, because it doesn't have any problem finding and importing arcpy, and the env.workspace can be set with the same string that ArcCatalog knows for my data owner connection. The slash in the Database Connections string can be either way / or \ .

# -*- coding: utf-8 -*-
# ModPriv2lp.py
# Testing how to modify enterprise geodatabase privileges with python
# Paul Huffman, 2017-12-14

# Set the necessary product code
#import arceditor
import os

import arcpy
from arcpy import env
#env.workspace = "Database Connections/Streams as GisOwner.sde"
env.workspace = "Database Connections/Other_Vector as GIS.sde"
#env.workspace = "Database Connections\\Streams as GisOwner.sde"#env.workspace = r"Database Connections\Streams as GisOwner.sde"

#get list of feature classes in geodatabase.
fclist = []
#fclist = arcpy.ListFeatureClasses ("Database Connections/Streams as GisOwner.sde")
fclist = arcpy.ListFeatureClasses ()
dslist = arcpy.ListDatasets ()
tblist = arcpy.ListTables ()
rslist = arcpy.ListRasters ()
#The folling line worked by setting connection + feature class in a string for ChangePrivileges
#datasetName = "Database Connections/Streams as GisOwner.sde/Streams.GISOWNER.str37_route_rivers"
#datasetName = "Streams.GISOWNER.str37_route_rivers"
#arcpy.ChangePrivileges_management(datasetName, "GISUsers", "GRANT", "AS_IS")

for fc in fclist:
    print fc
    arcpy.ChangePrivileges_management(fc, "DataViewer", "GRANT", "AS_IS")
for ds in dslist:
    print ds
    arcpy.ChangePrivileges_management(ds, "DataViewer", "GRANT", "AS_IS")
for tb in tblist:
    print tb
    arcpy.ChangePrivileges_management(tb, "DataViewer", "GRANT", "AS_IS")
for rs in rslist:
    print rs
    arcpy.ChangePrivileges_management(rs, "DataViewer", "GRANT", "AS_IS")
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

PaulHuffman · ‎02-07-2019

When I try to open a script like this with the "Edit with IDLE (ArcGIS Pro)" choice in File Explorer, I get Python 3.6.5 and the script fails with syntax error, missing parenthesis in print statement. Is print totally different in Python 3.6.5?

JakeSkinner · ‎02-07-2019

When using print with Python 3.x, you will want to put parenthesis around the print statement:

print("Print message")‍