Editing feature class in SDE using arcpy

[Yik-ChingTsui]

I need to use arcpy to update (insert and edit) rows of a feature class in an SDE. I tried the following:

import arcpy
ed = arcpy.da.Editor("C:\\...\\my_sde.sde\\TEST_20230925.USER_20230925.TEST")
ed.startEditing(with_undo=False, multiuser_mode=True)
ed.startOperation()
cur = arcpy.da.InsertCursor("C:\\...\\my_sde.sde\\TEST_20230925.USER_20230925.TEST", '*',)

 And I get the error:

Traceback (most recent call last):
  File "<string>", line 2, in <module>
RuntimeError: cannot open workspace

The feature class is actually named "test", but the SDE is renaming it apparently. Using "test" instead has no effect on the result.

I followed this https://community.esri.com/t5/python-questions/updatecursor-on-versioned-sde-data-failing/m-p/120137... for this part, but I get the same error

ed.startEditing(with_undo=False, multiuser_mode=True)

I followed https://community.esri.com/t5/python-questions/trying-to-edit-a-layer-in-a-versioned-sde-gdb-in-a/m-... to set the workspace to my feature dataset, instead of the SDE, but I get "runtime error: cannot open workspace" like JaimeMcKeown. I'm not sure what MathewCoyle means by "Are you connected with a version user that can make edits?" Apparently JaimeMcKeown resolved that but without explaining how

If I right click on the feature dataset in the catalog pane and choose Privileges, the user "sde" has select, insert, and update privileges. But I'm not sure if arcpy would need to "sign in" to the "sde" user.

Making the feature class versioned has no effect other than not letting me manually add rows.

Please let me know if you have any ideas. Many thanks.

[AllenDailey1]

Hello,

Does your sde connection use stored credentials or operating system authentication?  If the latter, are you running the code as the user "sde"?  For example, something I often do is right-click on an application, like ArcGIS Pro or Windows Command Prompt, select "run as different user," and then sign in as the desired account.  You'd have to do something like this if the sde connection is operating system authenticated.  I apologize if you already know this!

On a different topic, I noticed that you're assigning the cursor to a variable.  Just in case you're not familiar with this - I apologize if you are - you would then need to delete the cursor at the end of your code to avoid leaving a schema lock:

del cur

The more preferred way of dealing with a cursor is to use "with," and then you don't need to delete the cursor:

with arcpy.da.InsertCursor(fc, fields) as cur:

[Yik-ChingTsui]

Thanks for your reply. The SDE should be using stored credentials. If I right click on the SDE and select Connection properties, the authentication type is Database authentication with the username User_20230925 and 'Database' of 'Test_20230925'. That explains the full path of the feature class. I've never needed to sign in to a different user when working on different SDEs.

As for deleting the cursor, I was aware of the purpose of Python's with statement, though I didn't connect it to closing the schema lock. I don't think it has ever succeeded (meaning the `cur` variable was never bound as the exception interrupted it) so it shouldn't cause a schema lock. When the REPL or script exits, would the GC would run the destructor/finalizer and close the schema lock?

[VinceAngelo]

If the user is really "User_20230925" and database is really "Test_20230925", you might be having issues due to the upper-case characters. The connection code recases object names to lowercase. I've never been able to connect to mixed case named databases or as a mixed case username.

- V

[forestknutsen1]

I often use the arcpy exists to help debug access issues for my scripts. It is a quick way for me to identify if the script can "see" the target of you edits.

https://pro.arcgis.com/en/pro-app/latest/arcpy/functions/exists.htm

What is the database is your SED on?

[Yik-ChingTsui]

I'm not quite sure about what database my SDE is on. Connection Properties says Database Platform is SQL Server. It's also connected to my company's ArcGIS Online portal but I don't think it's relevant in this situation. It's a SDE for us to do random testing on. I suppose I should've created a new clean SDE but eventually I want my code to work on a SDE that's created by a subcontractor.

As for Exists, it returns True for both my SDE and the feature class.

[RhettZufelt]

You are setting the editing workspace to the feature class.

Try:

ed = arcpy.da.Editor("C:\\...\\my_sde.sde")

R_

[Yik-ChingTsui]

I actually tried this first, but it failed with the error:

Traceback (most recent call last):
File "<stdin>", line 1, in <module>
TypeError: cannot update the table

Then I tried the second link in the post (https://community.esri.com/t5/python-questions/trying-to-edit-a-layer-in-a-versioned-sde-gdb-in-a/m-...), which was to use the path to the feature dataset. (The link was broken previously). Sorry for the confusion.

[Yik-ChingTsui · Accepted solution]

I decided to use the REST API instead of arcpy. An OAuth token is needed for editing features (not the token from generateToken). The code I used is here: https://gist.github.com/YikChingTsui/ff6d7b6126db73a77e5845576250177d

[forestknutsen1]

I would go with arcpy over the rest api if possible. I will also make edits with pure sql to our sde data in oracle. Normally, I am targeting the attribute data with the edits.

https://desktop.arcgis.com/en/arcmap/latest/manage-data/using-sql-with-gdbs/overview-edit-versioned-...