topic Re: Do I want to set the database connection as an environment in order to loop through feature classes? in Python Questions

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

PaulHuffman — Sun, 12 Dec 2021 02:14:49 GMT

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")‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

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

PaulHuffman — Fri, 15 Dec 2017 19:48:30 GMT

I got this to run in ModelBuilder with iteration, but unfortunately, the export to python isn't much help in figuring out the python syntax for the workspace environment.

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

JakeSkinner — Sun, 12 Dec 2021 02:14:51 GMT

Hi Paul,

Once you set your environment workspace, the List functions (i.e. ListFeatureClasses, ListDatasets, etc) will automatically use that workspace. You're trying to set the workspace in the ListFeatureClasses function for the wild card parameter. Try the following:

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 ()‍‍‍‍‍‍‍

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

PaulHuffman — Sun, 12 Dec 2021 02:14:54 GMT

Thanks Jake. But I have tried that syntax as well. Still getting the error " for fc in fclist:
TypeError: 'NoneType' object is not iterable"

# -*- 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 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")
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")
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

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

JakeSkinner — Fri, 15 Dec 2017 21:09:36 GMT

Check the Streams as GisOwner.sde connection is connected as the correct user. You can do this by right-clicking on the database connection > Connection Properties. It could be the user does not have privileges to any of the feature classes.

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

JoshuaBixby — Fri, 15 Dec 2017 21:12:36 GMT

I am not sure what the best way is, but I would say the most Pythonic way is using Walk—Help | ArcGIS Desktop

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

PaulHuffman — Sun, 12 Dec 2021 02:14:56 GMT

Thanks Jake, but it worked when I ran a script like this on just one feature class. There must be a typo somewhere.

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

import arcpy

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

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

PaulHuffman — Sun, 12 Dec 2021 02:14:59 GMT

Well, now the short test version that changed just one feature class doesn't run. I keep getting "ExecuteError: ERROR 000229: Cannot open Database Connections/Streams as GisOwner.sde/Streams.GISOWNER.str37_route_rivers
Failed to execute (ChangePrivileges).

The Model with iteration version continues to update the privileges through the database connection. Export to python shows that the syntax is :

# Process: Change Privileges
arcpy.ChangePrivileges_management("'Database Connections\\Streams as GisOwner.sde\\Streams.GISOWNER.str39_route_rivers'", "GISUsers", "GRANT", "AS_IS")‍‍

I noticed that ModelBuilder used both double and single quotes around the connection string, but this didn't work for me in python when I tried it.

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

PaulHuffman — Fri, 15 Dec 2017 22:30:23 GMT

Hey, I've used Walk before. I'll give it a try in this situation.

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

PaulHuffman — Mon, 18 Dec 2017 23:36:41 GMT

Here's an idea. I just tried the three line version of the script from IDLE, and still get "ERROR 000229: Cannot open Database Connections/Streams as GisOwner.sde/Streams.GISOWNER.str37_route_rivers." Yet I can copy those three lines into ArcCatalog's python window and it successfully ran. And the model with iterator version runs successfully. Do you suppose that setting datasetName = "'Database Connections/Streams as GisOwner.sde/Streams.GISOWNER.str37_route_rivers'" in IDLE isn't working because that connection string doesn't mean anything to the OS, the OS can't find that connection, while running it in ArcCatalog can find the correct relative path and connection? Dropbox - Screenshot 2017-12-18 15.21.59.png

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

PaulHuffman — Sun, 12 Dec 2021 02:15:02 GMT

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")
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

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

PaulHuffman — Sun, 12 Dec 2021 02:15:04 GMT

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")
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

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

PaulHuffman — Sun, 12 Dec 2021 02:15:07 GMT

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")
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

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

PaulHuffman — Thu, 07 Feb 2019 19:07:06 GMT

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?

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

JakeSkinner — Thu, 07 Feb 2019 19:21:02 GMT

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

print("Print message")‍