7

There are a handful of ArcPy methods for truncating data in a table. It's also possible to do a direct SQL command to truncate a table (e.g. TRUNCATE TABLE DBO.VALVES) via a Python library (e.g. cx_Oracle or pyodbc).

What are advantages or disadvantages to ArcPy methods over SQL alternatives?

Do these considerations change when data is versioned or not?

If it's relevant, right now we're working with Python 2.7 and database 10.2.1; we're transitioning to Python 3 and database 10.6+ sometime in the next year.

Note: For the purposes of this question, the ability of Delete Rows to work on a subset is not relevant — we're interested in completely emptying the table to repopulate with data, and debating what method(s) would be best to standardize on.

Improve this question
4
  • 2
    If you have a versioned database, truncating outside of Arc could get you in to big trouble.
    – Emily
    Commented Jan 16, 2018 at 20:57
  • +1 I also am pretty sure the answer is "Use ArcPy because reasons" but I don't know and am interested in hearing what those reasons are.
    – Dan C
    Commented Jan 16, 2018 at 21:02
  • 1
    @notkilroy please consider expanding that into an answer :)
    – Erica
    Commented Jan 16, 2018 at 21:03
  • Good question, I've often wondered if you could safely by: check to make sure there are no sde_table_locks, and if not then truncate base table, then use the registration_id from table_registry to also truncate the adds and deletes tables. Then delete the row from SDE_mvtables_modified. I guess this could result in orphaned sde_states, but wouldn't compress fix that later?
    – Kirk Kuykendall
    Commented Jan 16, 2018 at 22:45

1 Answer 1

Reset to default
1

Arcpy has a sql library within it so there is no need to use pyodbc or cx_oracle directly if you don't want to. I've used this with sql spatial databases that do not run SDE.

Reading the Discussion of that above article basically answers most of your concerns.

Copy pasta for longevity...

  • Enterprise geodatabase system tables should not be altered using anything other than ArcGIS software. Corruption can occur if these system tables are edited directly using SQL.
  • Edits on versioned data performed using SQL should only be done through versioned views.
  • For geodatabases implemented in a relational database management system (DBMS) using DBMS data types and table formats, the DBMS's own SQL may be used to work with the information stored in the database.
  • Accessing the information in a geodatabase via SQL allows external applications to access the tabular data managed by the geodatabase. These external applications may be nonspatial database applications or custom spatial applications developed in an environment other than ArcObjects. Be aware, though, that SQL access to the geodatabase bypasses geodatabase functionality, such as topology, networks, terrains, or other class or workspace extensions.
  • It may be possible to use DBMS features such as triggers and stored procedures to maintain the relationships between tables needed for certain geodatabase functionality. However, executing SQL commands against the database without taking this extra functionality into account—for example, issuing INSERT statements to add records to a business table—will circumvent geodatabase functionality and possibly corrupt the relationships between data in your geodatabase.
  • Before attempting to access or modify any enterprise geodatabase objects, read all enterprise geodatabase documentation about using SQL against geodatabase objects in the DBMS.
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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