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
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.
TRUNCATE TABLE tablename-- way faster