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

EugeneSosnin_GSI

Sadly to say, but your code is wrong. CalculateField operation with SQL parameter 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.