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

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.

protected by PolyGeo Dec 2 '18 at 22:14

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

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