New Arcpy bug at 10.4

[KimOllivier]

(Sigh) I thought that a minor upgrade from 10.3 to 10.4 would be uneventful, but unfortunately I have hit a new bug that is painful to work around. I am copying a subset from a spatialite database to a file geodatabase using a View and SQL filter. This was very elegant but now it crashes completely. The tables are large so it is time consuming to create a repeatable standalone demonstration, but if I cannot do that I can appreciate it is hard to track down so here goes as a warning to others.

<CODE>

db = "e:/parcel.sqlite"

pre = '/main.'

for src,filter,tab in [['Legal_Description',"ttl_title_no is not null","Legal_Description"]]:

    if not arcpy.Exists(sqlite+"/"+tab):

        try:

            print tab, filter, db+pre+src, sqlite+"/"+tab

            arcpy.management.MakeTableView(db+pre+src, 'view', filter)

            count_view = int(arcpy.management.GetCount("view").getOutput(0))

            print count_view,"count_view"

            if not arcpy.Exists(intermediate_db+'/'+tab):

                arcpy.conversion.TableToTable('view', intermediate_db, tab) # crashes here

            count_table = int(arcpy.management.GetCount(intermediate_db+'/'+tab).getOutput(0))

            print "intermediate table count",tab, count_table

            if count_table != count_view:

                sys.exit() # give up here assuming that the TableToTable does not crash first

            arcpy.conversion.TableToTable(intermediate_db+'/'+tab, sqlite, tab)

            print tab, "copied",

        except Exception, errmsg:

            arcpy.AddError("Error {}\n {}".format(tab,errmsg))

            print "Error {}\n {}".format(tab,errmsg), arcpy.GetMessages()

</CODE>

The problem is deadlines, not reporting bugs. I have thought of three workarounds:

Use another system with SQL functions that work on featureclasses eg SQLiteExpertPro, but this is a bit manual.

Copy all the tables into a filegeodatabase (Table to Table works on full tables), very slow

Add pyspatialite and compose sql queries in python. Works but takes time to write.

[DanPatterson_Retired]

Joshua  is it this one you are referring to? SQLite Home Page

It seems strange they haven't included it in their conda distribution package for Pro 1.3 as I noted in my last blog post.

[Luke_Pinner]

Dan Patterson sqlite (non-spatial) is in the standard library​.

Joshua Bixby​ SQLite and ArcGIS—Help | ArcGIS for Desktop

[KimOllivier]

Ok, so you can create a Spatialite database in Arcpy. But what can you do with it? Where is the documentation on which functions and tools support Spatialite? The help is completely silent on this. The reason I even put in 10.4 was because of the new tool arcpy.management.CreateSQLiteDatabase. I assumed that there would be lots of new (hidden) support for spatialite in arcpy. But maybe I was too hopeful.

Could we users need to set up a Wiki for information on this? Any suggestions for the most suitable platform?

My experiments are a mishmash of SQL calls using a number of python APIs to sqlite or spatialite plus some arcpy calls. Some are excellent, others are very slow or impossible. I switch between the obvious and obscure as I explore the limits. It would be nice to have a consistent simple interface that worked the same way as the venerable file geodatabase.

Perhaps a new thread discussing the limits would be better.

[JoshuaBixby]

I think Kim Ollivier​ covered the gist of it, i.e., Esri's support for SQLite and its derivatives is poorly documented, at best.  Another example is raster support.  The page you reference doesn't even mention raster support, although raster data is supported to a limited extent.