[GIS] Delete table rows in non-spatial database using arcpy

arcgis-10.3arcpydatabasesql server

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.

Best Answer

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.

Related Solutions

ArcPy Insert Cursor – Not Inserting All Rows Issue

I had initially neglected to delete my irow object at the end of the script; after updating my code the insert cursor appears to be adding all rows (even the last one!) the way it should be. Here is the final code:

import arcpy, os

months = {'JAN':1,'FEB':2,'MAR':3,'APR':4,'MAY':5,'JUN':6,'JUL':7,'AUG':8,
          'SEP':9,'OCT':10,'NOV':11,'DEC':12}
ignore = ['Arbitrary_count','TOTAL_MGY','JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','SEP','OCT','NOV','DEC']

ws = r'D:\Data\Users\jbellino\Project\faswam\data\water_use\SC\FromTomAb\SC_WELLS_data_jcb.mdb'
arcpy.env.workspace = ws
arcpy.env.overwriteOutput = True
tbl = 'ORIGINAL_DHEC_WELL DATA'
itbl = 'monthly_dhec_well_data'

fields = arcpy.ListFields(tbl)

rows = arcpy.SearchCursor(os.path.join(ws,tbl))
irows = arcpy.InsertCursor(os.path.join(ws,itbl))
for row in rows:
    for month in months:
        #--for each row in the original table, and for each month stored in that row, 
        # create a new record in 'itbl'
        irow = irows.newRow()
        for field in fields:
            if field.name == month:
                #--if the field name refers to a month abbreviation it contains data
                # first convert the month abbreviation to month number
                irow.cn_mo = months[month]
                try:
                    # then grab the data in the field and process it into the appropriate 
                    # fields of the new table in the correct units
                    irow.cn_qnty_mo_va = row.getValue(field.name)*1000000
                    irow.cn_qnty_mo_va_mega = row.getValue(field.name)
                except:
                    #--skip null values
                    pass
            elif field.name not in ignore:
                #--if the field name is not a month abbreviation, just copy the data
                # to the new table
                irow.setValue(field.name,row.getValue(field.name))
        irows.insertRow(irow)
del irow,irows
del row,rows

[GIS] Edit SQL Server table (not registered with geodatabase) with arcpy

If you need just basic SQL access on something that is not a registered ArcSDE table, I recommend using a simple ODBC connection object rather than mucking about with the overhead of ArcPy. ArcPy adds so many objects which you likely do not need that it slows down what should be a quick and simple process. My personal choice is pyodbc which can be found at http://code.google.com/p/pyodbc/.

If you just want to delete a row you can use simple SQL statements

import pyodbc
cn = pyodbc.connect('DRIVER={SQL Server};SERVER=YourServerName;DATABASE=YourDatabase;UID=UserName;PWD=Password')
sql = "DELETE FROM table_name WHERE some_column=some_value"
cur = cn.cursor()
cur.execute(sql)
cn.commit()

If your data is registered as an SDE table then you will need to stick with an ArcPy/ArcObjects method to add, delete or update a record to prevent corrupting any part of the database. You can still use the pyodbc (or other odbc connectors) to read the SDE tables.

Best Answer

Related Solutions

ArcPy Insert Cursor – Not Inserting All Rows Issue

[GIS] Edit SQL Server table (not registered with geodatabase) with arcpy

Related Question