topic Re: Insert geometry from ESRI feature class to MSSQL spatial table using ArcPy InsertCursor() in Python Questions

Insert geometry from ESRI feature class to MSSQL spatial table using ArcPy InsertCursor()

BarryO__Neill — Sat, 11 Dec 2021 23:51:28 GMT

I am trying to do what seems to be a simple operation:

1. Iterate through a feature class
2. Insert found rows to an MSSQL spatial table.

However I keep getting the following error No support for this geometry type.

Strangely I get this error even if I only try to insert and int or string with no shapes involved. It seems that Arc does not understand the table which is strange because I can run select operations and Describe() on the table without issue.

Does Arc support inserting spatial data to MSSQL in this manner?

In the code snipped below:

selected_grid_fc: File Geodatabase Feature Class, Geometry Type: Polygon
selected_grids_mssql: MSSQL table with a geometry column. The geometry column stores Polygon grid squares from a 100m Fishnet in EPSG:2157

try:
    # Start an edit session. Must provide the workspace.
    edit_session = arcpy.da.Editor(env.workspace)
    # Edit session is started without an undo/redo stack for versioned data (for second argument, use False for unversioned data)
    edit_session.startEditing(False, False)
    # Start an edit operation
    edit_session.startOperation()

    search_cols = ['OBJECTID', 'GridId', 'SHAPE@']
    insert_cols = ['ObjectId', 'GridId', 'Shape'] # If I user 'Shape@', the error is the same.
    selected_grids_mssql = os.path.join(self.sde_path, 'dbo.SelectedGrids')
    with arcpy.da.SearchCursor(selected_grid_fc, search_cols) as search_cursor:
        with arcpy.da.InsertCursor(selected_grids_mssql, insert_cols) as insert_cursor:
            for row in search_cursor:
                insert_cursor.insertRow(row)
except Exception as ex:
    arcpy.AddError("Something went wrong.")
    raise
else:
    # Stop the edit operation.
    edit_session.stopOperation()
    # Stop the edit session and save the changes
    edit_session.stopEditing(True)
    arcpy.AddMessage("Import successful.")‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

For reference, the following works fine via pyodbc, but I wanted to see if it could be done without the need for an external library.

selected_grids = []
selected_grid_cols = ['OBJECTID', 'GridId', 'SHAPE@WKT']
with arcpy.da.SearchCursor(selected_grid_fc, selected_grid_cols) as cursor:
    for row in cursor:
        selected_grids.append(row)
with Helpers.db_connect().cursor() as cursor:
    query = "INSERT INTO [dbo].[SelectedGrids](ObjectId, GridId, Shape) VALUES (?,?,geometry::STGeomFromText(?, 2157));"
    cursor.executemany(query, selected_grids)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Insert geometry from ESRI feature class to MSSQL spatial table using ArcPy InsertCursor()

ModyBuchbinder — Wed, 23 Sep 2020 05:47:38 GMT

You cannot write directly from Desktop to enterprise tables that are not register in the Geodatabase.

You cannot start edit in ArcMap and you cannot do it in arcpy too.

You must register the table or use external software (pyodbc)

Have fun

Re: Insert geometry from ESRI feature class to MSSQL spatial table using ArcPy InsertCursor()

BarryO__Neill — Wed, 23 Sep 2020 14:15:14 GMT

Thanks very much for letting me know. I suspected as much but the error message was unclear and I had wondered if I was making a mistake in some manner. Not to worry, I will continute with pyODBC.

Thanks again!

Re: Insert geometry from ESRI feature class to MSSQL spatial table using ArcPy InsertCursor()

MarcoBoeringa — Wed, 23 Sep 2020 14:31:47 GMT

If you intend to only create read-only feature classes that do not need any ESRI Geodatabase functionality, then I second Mody Buchbinder‌ suggestion of using pyodbc to insert the records into the MSSQL table.

This means you need to replace the nested arcpy.InsertCursor with an appropriate pyodbc Cursor object.

I have written a Python multi-threaded application doing just that, and successfully used it to insert > 100M(!) records from a File Geodatabase into a PostgreSQL table.

Note that pyodbc is available in the ArcGIS Pro Conda environment. Just select it from there and install it to start working with it.

By the way, I recommend inserting as WKT (Well Know Text) geometries using the "SHAPE@WKT" token, and not use pyodbc "parameter binding" but just concatenate all fields to transfer into a long INSERT string statement. I have seen some instabilities with WKB and using parameter binding on ultra large datasets, that maybe related to some known unresolved issues with pyodbc and parameter binding (but this is speculation from my side at this point in time... you can try WKB and parameter binding on smaller datasets and see if it works for you).

Re: Insert geometry from ESRI feature class to MSSQL spatial table using ArcPy InsertCursor()

nzjs — Fri, 21 May 2021 05:03:03 GMT

I know this is an older post, but I just wanted to reply that the suggestion above has serious security implications.

Writing SQL statements in Python (or any language) without binding input parameters opens your database and tool to SQL injection vulnerabilities.

Parameter binding is included in libraries for a reason. I would always recommend to use them.