Update shape with arcpy.management.CalculateField SQL expression

[EugeneSosnin_GSI]

I have a local mobile geodatabase with a polyline featureclass (with Z and M values). The database has loaded stgeometry_sqlite.dll extension and OGC Tables created. 

I am trying to update SHAPE values with arcpy.management.CalculateField method and SQL expression. The function to build expression:

def make_case_expression(oid_field, oid_to_wkt, wkid):
    """
    oid_field - string, e.g. 'OBJECTID'
    oids_to_wkt - dict, with structure: {int OBJECTID: text WKT shape}
    wkid - int, WKID number, e.g. 3500
    """
    lines = ["CASE"]
    for oid, wkt in oid_to_wkt.items():
        lines.append(f"  WHEN {oid_field} = {int(oid)} THEN st_geometry('{wkt}', {wkid})")
    lines.append("  ELSE SHAPE")
    lines.append("END")
    return "\n".join(lines)

It creates an expression like (fake coords):

"CASE\n  WHEN OBJECTID = 12 THEN st_geometry('MULTILINESTRING Z ((6250000.0 1956000.0 0, 6250800.0 1956000.0 0))', 3500)\n  WHEN OBJECTID = 13 THEN st_geometry('MULTILINESTRING Z ((6250900.0 1956000.0 0, 6250800.0 1956000.0 0))', 3500)\n ELSE SHAPE\nEND"

Then I run function to Calculate Field:

arcpy.management.CalculateField(
    in_table=path_lines,
    field="SHAPE",
    expression=exp,
    expression_type="SQL"
)

...and catch an error:

File C:\Program Files\ArcGIS\Pro\Resources\ArcPy\arcpy\management.py:8504, in CalculateField(in_table, field, expression, expression_type, code_block, field_type, enforce_domains)
   8500 from arcpy.arcobjects.arcobjectconversion import convertArcObjectToPythonObject
   8502 try:
   8503     retval = convertArcObjectToPythonObject(
-> 8504         gp.CalculateField_management(
   8505             *gp_fixargs(
   8506                 (in_table, field, expression, expression_type, code_block, field_type, enforce_domains), True
   8507             )
   8508         )
   8509     )
   8510     return retval
   8511 except Exception as e:

File C:\Program Files\ArcGIS\Pro\Resources\ArcPy\arcpy\geoprocessing\_base.py:533, in Geoprocessor.__getattr__.<locals>.<lambda>(*args)
    531 val = getattr(self._gp, attr)
    532 if callable(val):
--> 533     return lambda *args: val(*gp_fixargs(args, True))
    534 else:
    535     return convertArcObjectToPythonObject(val)

ExecuteError: ERROR 999999: Something unexpected caused the tool to fail. Contact Esri Technical Support (http://esriurl.com/support) to Report a Bug, and refer to the error help for potential solutions or workarounds.
User transaction in progress -- operation not allowed
Failed to execute (CalculateField).

Appreciate any ideas

[DanPatterson]

any pending edits on the file that haven't been saved?

[EugeneSosnin_GSI]

No pending edits

[HaydenWelch]

If you don't initiate a transaction, there can be 'pending edits' because another part of your code grabbed a lock and never released it. The error you're getting seems to be some uncleared lock on the table.

[EugeneSosnin_GSI]

CalculateField operation is not allowed during Editing operation. There is no lock I am sure 100%. PS. if there's lock it returns error: 464.

[TonyAlmeida]

Wrap your calculation in an edit session.

import arcpy

# Start an edit session
edit = arcpy.da.Editor(arcpy.env.workspace)
edit.startEditing(False, True)  # False for non-versioned, True for multiuser

try:
    edit.startOperation()
    
    arcpy.management.CalculateField(
        in_table=path_lines,
        field="SHAPE",
        expression=exp,
        expression_type="SQL"
    )
    
    edit.stopOperation()
    edit.stopEditing(True)  # True to save changes
    
except Exception as e:
    edit.stopOperation()
    edit.stopEditing(False)  # False to discard changes
    raise e

or Instead of CalculateField, use an UpdateCursor 

with arcpy.da.UpdateCursor(feature_class, ["OBJECTID", "SHAPE@"]) as cursor:
    for oid, shape in cursor:
        if oid in oid_to_wkt:
            geom = arcpy.FromWKT(oid_to_wkt[oid], arcpy.SpatialReference(wkid))
            cursor.updateRow((oid, geom))

[EugeneSosnin_GSI]

Sadly to say, but your code is wrong. CalculateField operation is not allowed during Editing operation.

There is a reason, I tried to use CalculateField with SQL parameter, as it's more performative. Current solution I have: split edits into chunks and run UpdateCursor. But I am still interested in CalculateField with SQL.

Also your code would be better (more Pythonic) if formatted:

with arcpy.da.Editor(workspace):

    with arcpy.da.UpdateCursor(path, fields, qry) as cur:

        for row in cur:

                 <edits>

           .....

If there's an error, edits won't be saved.