How to parse a nested json data file iand insert it into sqlite database using python3

Question

I am trying to insert data from the json file into the sqlite database in python.

The json file format is:

   {
"metadata": {
    "start_at": "2016-01-27 20:01:14",
    "end_at": "2016-08-23 20:01:14",
    "act_count": 1
},
"act_data": [{
    "performed_at": "2013-04-24 20:01:14",
    "ticket_id": 531,
    "performer_type": "user",
    "performer_id": 7687,
    "activity": {
        "shipping_address": "N/A",
        "priority": 3,
        "agent_id": 8505,
        "requester": 12559

    }
}, {
    "performed_at": "2012-03-14 20:01:14.340099",
    "ticket_id": 235,
    "performer_type": "user",
    "performer_id": 11829,
    "activity": {
        "note": {
            "id": 22257,
            "type": 1
        }
    }

    }]
}

I want to insert all the columns into the same table.I have already created a table in sqlite database.

I want to populate an additional column "activity_type" as "Note" for the item which has "note" key and others as "ticket".I ran the below code:

import json
import sqlite3

 with open("tickets1.json") as data_file:
    data = json.load(data_file)


 conn = sqlite3.connect('tickets.db')
 c = conn.cursor()

  c.execute(""" CREATE TABLE activitydata1( 
        performed_at VARCHAR(50) NOT NULL,
         ticket_id INTEGER NOT NULL,
         performed_type VARCHAR(10) NOT NULL ,
         performer_id INTEGER NOT NULL,
         acitivity_type VARCHAR(10) NOT NULL,
         note_id INTEGER NULL,
         note_type INTEGER NULL,
        shipping_address   NVARCHAR(50)  NULL,
      shipment_date  NVARCHAR(50)  NULL,
        category  VARCHAR(10)  NULL,
        contacted_customer VARCHAR(10) NULL,
        issue_type VARCHAR(10) NULL,
        source INTEGER NULL,
        status VARCHAR(10) NULL,
        priority INTEGER NULL,
        groups VARCHAR(10) NULL,
        agent_id  INTEGER NULL,
        requester INTEGER NULL,
        product VARCHAR(10) NULL)
  """)

  for record in data["act_data"]:
     for key,val in record["activity"].items():

        if ( key == 'note'):
           activity_type="note"
           c.execute("INSERT INTO activitydata1(performed_at, ticket_id,performed_type,performer_id,acitivity_type,note_id,note_type) VALUES (?,?,?,?,?,?,?)",(record["performed_at"], record["ticket_id"],record["performer_type"],record["performer_id"],activity_type,val["id"],val["type"]))
        else:
           activity_type="ticket"
           c.execute("INSERT INTO activitydata1(performed_at, ticket_id,performed_type,performer_id,acitivity_type,shipping_address,priority,agent_id,requester) VALUES (?,?,?,?,?,?,?,?,?)",(record["performed_at"], record["ticket_id"],record["performer_type"],record["performer_id"],activity_type,record["activity"]["shipping_address"],record["activity"]["priority"],record["activity"]["agent_id"],record["activity"]["requester"]))

conn.commit()
conn.close()

This code inserts four rows of the same ticket and one row of the note. In total 5 rows. The required output is just two rows- One for ticket and other one for note.

Please find the image of the database attached. Please help me correct the code. Please advice some other better approach to handle this problem. Thanks in advance.

Answer 1

This for key,val in record["activity"].items(): will execute 4 times because this

"activity": {
        "shipping_address": "N/A",
        "priority": 3,
        "agent_id": 8505,
        "requester": 12559

    }

has four items.

Maybe you just need something like

if "note" in record["activity"]:
    # do the notes thing
else:
    # do the other thing

From the python doc:

x in dictview

Return True if x is in the underlying dictionary’s keys, values or items (in the latter case, x should be a (key, value) tuple).