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.
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).
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments