Delete table rows in non-spatial database using arcpy

Question

Working on a python script that does a bit of spatial analysis on a few layers, a spatial join of those layers, and then pulls data from a couple of other tables and holds them all in an in_memory table to be written out at the end. The idea is that this script would be run on a schedule every morning.

After all the processing is done, the in_memory table needs to be written to an existing table in a non-spatial database to be consumed by a third-party application. I also need to be able to clear out that table before the new data is written.

I have found that UpdateCursor, Truncate, DeleteRows etc. don't work for me in this other database as the table is not registered with Geodatabase. I am unable to do this as it's not stored in a geodatabase and I can't turn it into one.

How can I delete rows in this table in the non-spatial third-party database using arcpy? Once the table is empty I can use arcpy.da.InsertCursor to insert new rows without any issue.

ArcGIS Desktop 10.3
Both the non-spatial and spatial database servers are running SQL Server 2012

# Delete/Truncate/Delete Rows of third-party DB table
nonGeoDB = "Database Connections/NonGeoDB.sde/NonGeoDB.dbo.ALoad"

# Delete Rows doesn't work
arcpy.DeleteRows_management(nonGeoDB)

# Update Cursor with deleteRows doesn't work
with arcpy.da.UpdateCursor(nonGeoDB, 'SEQ') as delcursor:
    for row in delcursor:
        delcursor.deleteRow()

001323 : Input rows must be stored as a registered object in a geodatabase.

---

EDIT The main reason I'm trying to use arcpy over pyodbc is that I can then just use read-made database connection files that are stored securely on the server so that updates can be made to those connection files without having to modify connection settings within the .py directly. If pyodbc can read the connection settings from one of those files, then that possibly would be the best option.

Answer 1

Finally figured this one out. And apologies to @Vince - I see you mentioned this in a comment but I completely missed it until right now when I came to post this answer.

The tool arcpy.ArcSDESQLExecute() is the arcpy equivalent of pyodbc, but unlike pyodbc, ArcSDESQLExecute will in fact read a .sde connection file (so I don't need to pass login details through to the script).

# Delete/Truncate/Delete Rows of third-party DB table
nonGeoDB = "Database Connections/NonGeoDB.sde"

sqlConn = arcpy.ArcSDESQLExecute(nonGeoDB)

sqlQuery = """DELETE FROM [NonGeoDB].[dbo].[ALoad]"""

sqlConn.execute(sqlQuery)

I can then follow this up with the arcpy.da.InsertCursor() to insert the new records into that same table, as the Insert Cursor works to non GDB tables, it's just the Update Cursor that doesn't work.

Answer 2

If you don't need the old copy of the table anymore you could use:

if arcpy.Exists(table): arcpy.Delete_management(table)

to get rid of the old table, then use arcpy.TableToTable_conversion() (http://pro.arcgis.com/en/pro-app/tool-reference/conversion/table-to-table.htm) to convert your attributes to a dbase table in your SDE.

Alternatively you'd have to register/version your table and set permissions that allow you to make edits on that table. When making edits on a versioned table in an SDE you have to start an editing session as well (I've had to do this for update/insert cursors) (http://desktop.arcgis.com/en/arcmap/10.3/analyze/arcpy-data-access/editor.htm)

Also, if you can create a database connection in python, you can use the same info to set up your pyodbc connection.

server_name = "YOUR.SERVER.NAME"
database = "YOUR_DATABASE"
connection = pyodbc.connect('DRIVER={SQL Server Native Client 11.0};SERVER={};DATABASE={};TRUSTED_CONNECTION=yes'.format(server_name, database))

The server_name and database correspond to the "Instance" and "Database" in the "Add Database Connection" window through ArcCatalog. You may have to change the "Driver" string for the pyodbc connection, but that will correspond to the "Database Platform" in the add database connection window.

PYODBC: https://code.google.com/archive/p/pyodbc/wikis/GettingStarted.wiki