New Arcpy bug at 10.4

3838
13
07-12-2016 05:14 PM
KimOllivier
Regular Contributor II

(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.

0 Kudos
13 Replies
DanPatterson_Retired
MVP Esteemed Contributor

I am not sure whether your post is a question or a comment Kim.  I scoured the 10.3 and 10.4 topics for tabletotable changes and found none.  There no significant changes in python 2.7.x during this period.  So did you get an actual error and if so what was it.  FYI be prepared for some major changes in python if you haven't already moved to 3.4/5 since I note you aren't using print function yet and I assume none of your code is unicode safe.

0 Kudos
KimOllivier
Regular Contributor II

It is a comment. I am grumpy. There is no error message, it is a sudden crash. Windows managed to point the finger at GdbCore.dll.

Problem signature:

  Problem Event Name:    APPCRASH

  Application Name:    python.exe

  Application Version:    0.0.0.0

  Application Timestamp:    5560ad83

  Fault Module Name:    GdbCore.dll

  Fault Module Version:    10.4.1.5686

  Fault Module Timestamp:    57056049

  Exception Code:    c0000005

  Exception Offset:    0009e77e

  OS Version:    6.1.7601.2.1.0.256.48

  Locale ID:    5129

  Additional Information 1:    0a9e

  Additional Information 2:    0a9e372d3b4ad19135b953a78882e789

  Additional Information 3:    0a9e

  Additional Information 4:    0a9e372d3b4ad19135b953a78882e789

I have taken the time to submit a formal bug report with a script to demonstrate the problem. Yes my code is still 2.7 because it was written several years ago and was working just fine! Maybe it is a unicode error but it is not in Python because it does not get trapped with an exception. So I guess it must be deep inside GdbCore.dll which is mentioned in the windows traceback.

My first effort to generate a sample using python/sqlite3 worked, but with errors, the GetCount on a view with a filter returned the total table count. So I have had to provide a link to download a larger file (288 MB) that demonstrates the behaviour. It is not the only one.

I thought I was being conservative. I put 10.4 on a separate machine and used it for a week first. If it is a full release I simply buy a new machine, start again and leave the old one alone to easily go back to if there is an obscure bug. I suppose that is the purpose of a virtual environment, but I don't have any spare licences and I would expect it to be very slow.

JoshuaBixby
MVP Esteemed Contributor

Unfortunately, new bugs in new versions is nothing new with ArcGIS releases, especially ArcMap/ArcCatalog.

Are you running this as a stand-alone script, in the interactive Python windows in ArcMap, or a Python toolbox?  If a stand-alone script, what happens if you run it from within ArcMap?  I understand it should work regardless, but I have run into a couple bugs before that worked within ArcMap but failed when a stand-alone script.

0 Kudos
KimOllivier
Regular Contributor II

It fails in either environment. Standalone is a good idea these days if you are doing batch processing because it can use the 64 bit version of Python that has more memory for those crude memory hog routines that are now so popular. By the way, partitioning is the solution for this, cartographic partitioning is now used for some tools and one can adopt the same technique to reduce memory and speed up processing.

0 Kudos
BruceHarold
Esri Regular Contributor

Kimo

Assuming it is using a view in Table to Table that is the problem, just use your filter where clause in the T2T step and don't make a view.  I'll ask who owns Table to Table here.

0 Kudos
KimOllivier
Regular Contributor II

Using a SQL filter in the TableToTable tool fails in the same way.

0 Kudos
ChrisPedrezuela
Occasional Contributor III

Maybe you can just use a sqllite3 library, fetch all your required records and append to a staging featureclass, might be faster than T2T gp tool.

0 Kudos
KimOllivier
Regular Contributor II

It is a good idea.I am already using pyspatialite to handle sqlite based tables and featureclasses. But converting back to file geodatabases needs a conversion tool. In theory this is supported by the conversion utilities, otherwise I have to complicate the workflow by using FME or a complex dump/reload. Some tables have geometry so they turn into a featureclass. Most of my customers are still more comfortable with a file geodatabase. I cannot (yet?) use database relates or store metadata for example. But I have other customers that do not have a GIS so they need data in a database exchange format. What to use? Access database have been deprecated and anyway they have 2GB limits which I have hit. File geodatabases are not an option. I could use SQL Server and attached databases if I splashed out for that. Linux based customers do not use SQL Server. PostGIS only has a crude dump. So sqlite looks ideal as a modern, compact, open, supported(!) exchange format for tables and featureclasses.

Since spatialite (or geopackages) are being used for Esri Mobile applications this new 'personal' geodatabase format is gradually being supported. I have found spatialite much faster and more elegant for some operations.  I switched to that format for a mirror of external data held in PostGIS. The replication has to be a 'homemade' workflow which is difficult to make robust but was easier if I also went with a similar database to PostGIS. Maybe I should have used PostGIS locally, but Sqlite is much faster and easier for a non-distributed purpose. I also found WFS unreliable so I went with CSV extracts using WKT for the geometry (on advice).

JoshuaBixby
MVP Esteemed Contributor

SQLite and its derivatives are great, I only wish Esri would formalize and/or communicate its adoption strategy.  I was digging through style files the other day and realized that Esri switched from an Access/Jet-based format with ArcMap style files (*.style) to a SQLite-based format with ArcGIS Pro style files (*.stylex).  The change makes sense in every way, and it represents a continued move by Esri toward SQLite, but it would be nice to have better documentation on what Esri's next plans are for supporting SQLite and its derivatives.

0 Kudos