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.

Can you include your table definition and what you want the resulting rows to look like after inserting the values in that json? — Shawn
– Shawn, Commented May 4, 2019 at 1:20
@Shawn I have updated the question with table definition and image of the database.Appreciate your help. — user8720570
– user8720570, Commented May 4, 2019 at 1:37

DinoCoderSaurus · Accepted Answer · 2019-05-04 01:28:50Z

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

answered May 4, 2019 at 1:28

DinoCoderSaurus

6,5502 gold badges13 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related