4

I am trying to update some rows in a SQL Server database table (not registered with geodatabase) with arcpy.da.UpdateCursor. However, when running the deleteRow(), I get the error return without exception set error. Updating geodatabase tables works fine.

I guess it might be a limitation of arcpy which can't edit a non-registered with geodatabase RDBMS table (in this case, SQL Server), but I want to make sure I don't miss anything.

Can ArcSDESQLExecute class help me to update SQL Server table that are not registered in geodatabase (have found no info on that in the Help)? Any other workarounds?

EDIT:

I have ended up using ArcSDESQLExecute which works fine for executing SQL commands (including deleting rows) for tables that are not registered with geodatabase.

sde_conn = arcpy.ArcSDESQLExecute(r"C:\GIS\Temp\nongeo.sde")
sql_statement = """delete from added_table WHERE AddID = 2 """
6

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.

  • Thanks for the tip. I use ArcPy because my processing has geographic context and I am tied to ArcGIS in this particular case. Is there any other way to edit a table not registered with geodatabase without using 3rd party modules (not included with Python and ArcPy install)? – Alex Tereshenkov Dec 11 '13 at 19:37
  • 1
    The base Python installed with ArcGIS does not have any native SQL processing ability which is why one of the 3rd party modules is needed. One of the reasons I finally chose pyodbc over the other modules is because it was easy to install. I use it daily in conjunction with ArcPy to process our spatial data. By cutting out the ArcPy where possible the scripts run faster. Plus, if your DBMS can store geometry in a native spatial format it should also have functions you can call through a sql statement to be able to manipulate the data spatially such as intersects and buffers with out ArcPy. – Lemur Dec 12 '13 at 16:01
  • Thanks for the info. I have ended up using ArcSDESQLExecute which works fine for executing SQL commands (including deleting rows) for tables that are not registered with geodatabase. – Alex Tereshenkov Dec 13 '13 at 14:19

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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