4

I need to import an SQLite database generated by ESRI Survey123 to a csv or some other useful format. I modified this script so that I can use arcToolbox to acquire input and output parameters. I am not familiar enough with the SQLite Survey123 format to understand how to address the error below the script:

import csv, sqlite3, json, os, sys, arcpy


def readS123db(inDB):
    conn = sqlite3.connect(inDB)
    cur = conn.cursor()
    surveys = {}

    for row in cur.execute('SELECT name, feature, status from Surveys where status = 1 or status = 3 or status = 2'):
        arcpy.AddMessage(row)
        arcpy.AddMessage ('-----------------')
        surveyName = row[0]
        if surveyName not in surveys.keys():
            surveys[surveyName] = {"adds":[], "updates":[]}
        jTransaction = json.loads(json.loads(row[1]))[0]
        arcpy.AddMessage(jTransaction)
        #Case for Adds
        if "adds" in jTransaction.keys():
            jRow = jTransaction["adds"][0]
            outRow = jRow["attributes"]
            #Add Geometry
            outRow[u"x_geometry"] = jRow["geometry"]["x"]
            outRow[u"y_geometry"] = jRow["geometry"]["y"]
            outRow[u"z_geometry"] = jRow["geometry"]["z"]
            #Add Attachments
            if "attachments" in jTransaction:
                jAttach = jTransaction["attachments"]
                for jAttRow in jAttach:
                    if jAttRow != None:
                        for jAtt in jAttRow:
                            outRow[jAtt["fieldName"]] = jAtt["fileName"]
            surveys[surveyName]["adds"].append(outRow)
        if "updates" in jTransaction.keys():
            jRow = jTransaction["updates"][0]
            outRow = jRow["attributes"]
            #Add Geometry
            outRow[u"x_geometry"] = jRow["geometry"]["x"]
            outRow[u"y_geometry"] = jRow["geometry"]["y"]
            outRow[u"z_geometry"] = jRow["geometry"]["z"]
            if "attachments" in jTransaction:
                jAttach = jTransaction["attachments"]
                for jAttRow in jAttach:
                    if jAttRow != None:
                        for jAtt in jAttRow:
                            outRow[jAtt["fieldName"]] = jAtt["fileName"]
            surveys[surveyName]["adds"].append(outRow)
        #arcpy.AddMessage(outRow)
    return surveys

if __name__ == '__main__':

    inDB = arcpy.GetParameterAsText(0)
    outDir = arcpy.GetParameterAsText(1)

    surveys = readS123db(inDB)

This gives me the following error:

Traceback (most recent call last):
  File "T:\Projects\Work_in_Progress\Guphy\2017\GRPAppData\GRPAppData\ReadDB_tool\ReadDB\readDb.py", line 100, in <module>
    surveys = readS123db(inDB)
  File "T:\Projects\Work_in_Progress\Guphy\2017\GRPAppData\GRPAppData\ReadDB_tool\ReadDB\readDb.py", line 21, in readS123db
    jTransaction = json.loads(json.loads(row[1]))[0]
KeyError: 0

Failed to execute (ImportSurvey123).

Is this a problem with my code, the SQLite database, or both?

  • 1
    Hi, a couple of things to help people answer the question: first, can you please shorten the code to a Minimum Complete, Verifyable Example. Just the exercise of doing so might help you sold the issue. Second, if at all possible are you able to add a link to a data sample so people can try running the code themselves? I'd like to see the question answered as I think its a useful one, but it needs to be narrowed down first. – om_henners Aug 15 '18 at 7:54
  • Thanks, I removed code irrelevant to the error. I am not sure how I can share the sample data. – spaine Aug 16 '18 at 16:46
  • The script works perfectly, but only extracts them as tables not feature classes, have you thought about how to bring down all the attachments associated with the surveys too? – daniel franklin Nov 30 '18 at 11:47
  • 1
    This does not provide an answer to the question. Once you have sufficient reputation you will be able to comment on any post; instead, provide answers that don't require clarification from the asker. - From Review – BERA Nov 30 '18 at 12:33
  • 1
    If you have a new question, please ask it by clicking the Ask Question button. Include a link to this question if it helps provide context. - From Review – Erik Nov 30 '18 at 13:09
5

I built this tool to export Survey123 data from a SQLite database (downloaded off the device used to capture the data) to a file geodatabase. It creates one feature class for each type of survey in the SQLite database. The script requires that you run it from an ArcToolbox tool with 2 parameters: [0] the input .sqlite file, and 1 the output file geodatabase. Here is the code:

import csv, sqlite3, json, os, arcpy
arcpy.env.overwriteOutput = True

def readS123db(inDB):
    conn = sqlite3.connect(inDB)
    cur = conn.cursor()
    #conn.text_factory = lambda x: x.decode("utf-16")
    #Status indicates which 'box' teh Survey is in
    #0 - Drafts
    #1 - Outbox
    #2 - Sent
    #3 - Submission Error
    #4 - Inbox

    for row in cur.execute('SELECT name, feature, status, data, snippet from Surveys where status = 1 or status = 3 or status = 2'):
        #arcpy.AddMessage(row)

        dataField = json.loads(row[3])
        dataFieldKeys = dataField.keys()
        #arcpy.AddMessage(str(dataFieldKeys))
        for key in dataField:
            masterKey = key
            #arcpy.AddMessage(str(masterKey))

            fcPath = os.path.join(gdb, str(masterKey))
            if arcpy.Exists(fcPath):
                arcpy.AddMessage("\nDeleting prior datasets")
                arcpy.Delete_management(fcPath)  #Delete Existing feature classes with these survey names
            if arcpy.Exists(fcPath + "_tbl"):
                arcpy.Delete_management(fcPath + "_tbl")


    rowNum = 0
    alternateFcNameList = []
    for row in cur.execute('SELECT name, feature, status, data, snippet from Surveys where status = 1 or status = 3 or status = 2'):
        rowNum = rowNum + 1

        dataField = json.loads(row[3])
        snippetField = str(row[4])[:255]

        dataFieldKeys = dataField.keys()
        for masterKey in dataField:
            #output = os.path.split(outFC)

            arcpy.AddMessage("\n\nRow " + str(rowNum) +"\nSurvey:  " + masterKey)
            arcpy.AddMessage("Snippet: " + snippetField)
            tier2keys = dataField[masterKey].keys()
            #arcpy.AddMessage(str(tier2keys))
            gpsField = None
            for tier2key in tier2keys:  #Get the spatial reference

                #arcpy.AddWarning("TIER2KEY: " + tier2key)
                if tier2key == "GPS":
                    #gpsField = tier2key
                    gpsKeys = dataField[masterKey][tier2key]
                    for gpsKey in gpsKeys:
                        arcpy.AddMessage("GPS Key: " + gpsKey)
                        if gpsKey == "spatialReference":
                            sr = dataField[masterKey][tier2key][gpsKey]["wkid"]
                            arcpy.AddMessage("Spatial Reference: WKID " + str(sr))
                            spatial_reference = arcpy.SpatialReference(sr)
                            gpsField = tier2key
                elif tier2key == "site_point": #In one case the GPS field was mis-named as field_2
                    #gpsField = tier2key
                    gpsKeys = dataField[masterKey][tier2key]
                    if gpsKeys != None:
                        for gpsKey in gpsKeys:
                            arcpy.AddMessage("GPS Key: " + gpsKey)
                            if gpsKey == "spatialReference":
                                sr = dataField[masterKey][tier2key][gpsKey]["wkid"]
                                arcpy.AddMessage("Spatial Reference: WKID " + str(sr))
                                spatial_reference = arcpy.SpatialReference(sr)
                                gpsField = tier2key


            arcpy.AddMessage("GPS Field: " + str(gpsField))
            if gpsField != None and dataField[masterKey][gpsField]["x"] != None and dataField[masterKey][gpsField]["y"] != None and dataField[masterKey][gpsField]["z"] != None:

                #Create feature class if necessary
                fcPath = os.path.join(gdb, str(masterKey))
                if not arcpy.Exists(fcPath):
                    arcpy.AddMessage("\nCreating output feature class")
                    try:
                        arcpy.CreateFeatureclass_management(gdb, str(masterKey), "POINT", "", "DISABLED", "ENABLED", spatial_reference)
                    except:
                        masterKeyString = str(masterKey)
                        alternateFcName = masterKeyString.translate(None, '!@#$-&*^+=`~?/;:[]{}.,<>') #remove characters not valid in FC name
                        fcPath = os.path.join(gdb, alternateFcName)
                        if not arcpy.Exists(os.path.join(gdb, alternateFcName)):
                            alternateFcNameList.append(alternateFcName)
                            arcpy.AddMessage(masterKey + " is not a valid feature class name.\nReplacing with " + alternateFcName + "\n")
                            arcpy.CreateFeatureclass_management(gdb, alternateFcName, "POINT", "", "DISABLED", "ENABLED",
                                                                spatial_reference)

                #Get data per row
                fieldList = []
                rowValues = []
                for tier2key in tier2keys:
                    if tier2key != "GPS":
                        if tier2key != "objectid" and tier2key != "ObjectId":
                            arcpy.AddMessage("Import Field: " + tier2key)
                            arcpy.AddField_management(fcPath, str(tier2key), "TEXT")
                            fieldList.append(tier2key)
                            rowValues.append(str(dataField[masterKey][tier2key]))
                arcpy.AddField_management(fcPath, "Snippet", "TEXT") #Adds snippet field from original database

                fieldList.extend(("Snippet", "SHAPE@X", "SHAPE@Y", "SHAPE@Z"))

                xCoord = dataField[masterKey][gpsField]["x"]
                yCoord = dataField[masterKey][gpsField]["y"]
                zCoord = dataField[masterKey][gpsField]["z"]

                rowValues.extend((snippetField, xCoord, yCoord, zCoord))
                rowValuesTuple = tuple(rowValues) #Create tuple from list of row values
                rowValues = [rowValuesTuple] #because insert cursor expects a list of tuples (one tuple per data row)

                with arcpy.da.InsertCursor(fcPath, fieldList) as cursor:
                    for row in rowValues:
                        arcpy.AddMessage("\nAppending data from row " + str(rowNum) + ": \n" + str(row))
                        cursor.insertRow(row)


            elif gpsField == None:
                #break (only enable if you want to omit non-spatial tables)
                # Create feature class if necessary
                fcPath = os.path.join(gdb, str(masterKey) + "_tbl")
                if not arcpy.Exists(fcPath):
                    arcpy.AddMessage("\nCreating output table")
                    try:
                        arcpy.CreateTable_management(gdb, str(masterKey) + "_tbl")
                    except:
                        masterKeyString = str(masterKey)
                        alternateFcName = masterKeyString.translate(None,
                                                                    '!@#$-&*^+=`~?/;:[]{}.,<>')  + "_tbl"# remove characters not valid in FC name
                        fcPath = os.path.join(gdb, alternateFcName)
                        if not arcpy.Exists(os.path.join(gdb, alternateFcName)):
                            alternateFcNameList.append(alternateFcName)
                            arcpy.AddMessage(
                                masterKey + " is not a valid table name.\nReplacing with " + alternateFcName + "\n")
                            arcpy.CreateTable_management(gdb, alternateFcName)

                # Get data per row
                fieldList = []
                rowValues = []
                for tier2key in tier2keys:
                    if tier2key != "GPS":
                        if tier2key != "objectid" and tier2key != "ObjectId" and tier2key != "OBJECTID":
                            arcpy.AddMessage("Import Field: " + tier2key)
                            arcpy.AddField_management(fcPath, str(tier2key), "TEXT")
                            fieldList.append(tier2key)
                            rowValues.append(str(dataField[masterKey][tier2key]))

                rowValuesTuple = tuple(rowValues)  # Create tuple from list of row values
                rowValues = [rowValuesTuple]  # because insert cursor expects a list of tuples (one tuple per data row)

                with arcpy.da.InsertCursor(fcPath, fieldList) as cursor:
                    for row in rowValues:
                        arcpy.AddMessage("\nAppending data from row " + str(rowNum) + ": \n" + str(row))
                        cursor.insertRow(row)



if __name__ == '__main__':

    inDB = arcpy.GetParameterAsText(0)
    gdb = arcpy.GetParameterAsText(1)

    surveys = readS123db(inDB)

    arcpy.AddMessage("\nOh Joy!!!\nAll Surveys Have Been Exported!\n")

This is what the ArcMap tool pointing to the above script should look like: This is what the ArcMap tool should look like

|improve this answer|||||
1

Your json object is a dictionary that does not have a key named 0. If you are trying to pull an entry based on position beware.

|improve this answer|||||

Not the answer you're looking for? Browse other questions tagged or ask your own question.