5

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.

  • Are you unable to connect to this database using ArcCatalog? If you can, you should be able to register as versioned there. – MaryBeth May 2 '16 at 2:00
  • 2
    This appears to be more of a pure Python issue. Have you tried using ODBC (pyodbc) to this external database? I've used ArcSDESQLExecute to issue arbitrary SQL to a database, but only to geodatabase-enabled databases. – Vince May 2 '16 at 2:24
  • 1
    DeleteRows uses the registered rowid from the selection set. No rowid, no selection set, no delete. Nothing odd about that. – Vince May 2 '16 at 2:59
  • 1
    I have to agree with @Vince that for a non-ArcGIS table it doesn't really make sense to use arcpy. Instead use the tools specifically written for this purpose, eg pyodbc – Stephen Lead May 2 '16 at 3:18
  • 1
    If you want to delete all rows, you should issue TRUNCATE TABLE tablename -- way faster – Vince May 2 '16 at 4:56
2

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.

  • 1
    I'd still recommend TRUNCATE TABLE tablename as the SQL to delete all rows. – Vince Jun 16 '16 at 0:10
  • @Vince unfortunately you need ALTER permissions to perform a TRUNCATE TABLE – Midavalo Jun 16 '16 at 0:21
1

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.

enter image description here

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.