alexa_snippet.py

# Open the 'LocalData.sqlite file
with local_data_db:
    c = local_data_db.cursor()

    # Select the rows where ZKEY starts with 'ToDoCollections' - there should only be two, ToDoCollection.TASK and
    # ToDoCollection.SHOPPING_ITEM
    c.execute("SELECT ZVALUE FROM ZDATAITEM WHERE ZKEY LIKE 'ToDoCollection%'")

    # For both the rows we selected with the above query, we want to:
    for row in c.fetchall():
        # load the contents of ZDATAITEM as a JSON, since it makes it easy to work with
        row_json = json.loads(row[0])

        # for each item in the JSON, write values to XLSX file
        for item in row_json:
            # the text that was added to the ToDoCollection
            w.write(row_number, 0, item['text'], black_type_format)

            # the nbestItems seem to be the "runner-up" translations that Alexa heard but decided against using. If text
            # was entered via app, this will be empty.
            if item['nbestItems']:
                # there are often more than one of these, so join them all together for display
                nbestItems_string = ", ".join(item['nbestItems'])
                w.write(row_number, 1, nbestItems_string, black_type_format)

            # if the item has been completed - TRUE or FALSE
            w.write(row_number, 2, item['complete'], black_type_format)

            # if the item has been deleted - TRUE or FALSE
            w.write(row_number, 3, item['deleted'], black_type_format)

            # the item type - either TASK or SHOPPING_ITEM
            w.write(row_number, 4, item['type'], black_type_format)

            # item creation timestamp - in JSON as 1463950942522, but gets converted to 2016-05-22 21:02:22.522
            w.write(row_number, 5, to_human_timestamp(item['createdDate']), black_date_format)

            # item update timestamp - in JSON as 1463950942522, but gets converted to 2016-05-22 21:02:22.522
            w.write(row_number, 6, to_human_timestamp(item['lastUpdatedDate']), black_date_format)

            # item local update timestamp - presumably  is set if the item is checked off/changed on the mobile device
            w.write(row_number, 7, to_human_timestamp(item['lastLocalUpdatedDate']), black_date_format)

            # reminder time - I presume this is the same as others, but I hadn't used this feature so no data
            w.write(row_number, 8, to_human_timestamp(item['reminderTime']), black_date_format)

            # long string, appears to be customer ID concatenated with a GUID separated by #
            # example: A1C9VTA5F7ZW1N#28a70937-7525-313f-a58c-374d73f91505
            w.write(row_number, 9, item['itemId'], black_type_format)

            # customer ID, same as above - A1C9VTA5F7ZW1N - was static for all my test data (which is expected)
            w.write(row_number, 10, item['customerId'], black_type_format)

            # not quite sure - was null for all my entries
            w.write(row_number, 11, item['utteranceId'], black_type_format)   # record_type

            # originalAudioId - looks to be unique per entry. Not sure of make up; 2016/04/18/19 correspond to
            # YYYY/MM/DD/HH of createdDate, first string (up to #) is static but != customerId, and GUID toward
            # end != itemId. Example:
            # AB72C64C86AW2:1.0/2016/04/18/19/B0F00715549602C4/04:29::TNIH_2V.275ba59c-49a7-45fa-b484-b21435c8ebc7ZXV/0
            w.write(row_number, 12, item['originalAudioId'], black_type_format)   # record_type

            row_number += 1