CODEWITHSUNDEEP
pythonjsonpostgresql
Manish Gupta
Manish Gupta

Reputation: 4666

data from json file to postgres table

I have a json file from which i want to put data in my postgres table. Link to file http://tempsend.com/0013F2BE73.
The structure of my json is like:

{"url": someurl,
 "hasMore": false, 
 "orderItems": [{"orderId": "OD003663828138911500", 
                 "status": "APPROVED", 
                 "subItems": [], 
                 "priceComponents": {"shippingCharge": 30.0, 
                                     "customerPrice": 417.0, 
                                     "totalPrice": 447.0,
                                     "sellingPrice": 439.0}, 
                 "orderItemId": 234207779, 
                 "listingId": "LSTNDNEFJK8E4YZEGPJP2EBHU", 
                 "title": "Masha Women's Nighty", 
                 "price": 447.0, 
                 "dispatchByDate": "2015-08-21T09:13:34+05:30", 
                 "shippingPincode": "700027", 
                 "fsn": "NDNEFJK8E4YZEGPJ", 
                 "sla": 2, 
                 "sku": "NT55-188", 
                 "updatedAt": "2015-08-17T09:13:37+05:30", 
                 "stateDocuments": [""], 
                 "shippingFee": 30.0, 
                 "hold": false,
                 "orderDate": "2015-08-17T09:12:46+05:30", 
                 "quantity": 1},

                 {"orderId":     ....

My database table structure is:

Create table If Not Exists temp_fk_sale_order(order_id varchar,
status varchar,sub_items varchar,shipping_charge float,customer_price float,
total_price float,selling_price float,order_item_id varchar,
listing_id varchar,title varchar,price float,dispatch_by_date timestamp,
shipping_pincode integer,fsn varchar,sla integer,sku varchar,updated_at timestamp,
state_documents varchar,shipping_fee varchar,hold varchar,order_date timestamp,
quantity integer)

I am on ubuntu 14.04 and file is in my downloads. how to insert data from this file to my postgres table?

EDIT:

I wrote a python code which parse the json and store the required values in different lists.

My code:

import json
import csv

'''Store data in a string and then convert it to json'''
with open('data.json','rb') as data:
    data_str = data.read()
data_dict = json.loads(data_str)

'''create a list of orders'''
orders_len = len(data_dict['orderItems'])

order_id = []
status = []
sub_items = []
shipping_charge = []
customer_price = []
total_price = []
selling_price = []
order_item_id = []
listing_id = []
title = []
price = []
dispatch_by_date = []
shipping_pincode = []
fsn = []
sla = []
sku = []
updated_at = []
state_documents = []
shipping_fee = []
hold = []
order_date = []
quantity = []

for num in range(0,orders_len):
    order_id.append(data_dict['orderItems'][num]['orderId'])
    status.append(data_dict['orderItems'][num]['status'])
    sub_items.append(data_dict['orderItems'][num]['subItems'])
    shipping_charge.append(data_dict['orderItems'][num]['priceComponents']['shippingCharge'])
    customer_price.append(data_dict['orderItems'][num]['priceComponents']['customerPrice'])
    total_price.append(data_dict['orderItems'][num]['priceComponents']['totalPrice'])
    selling_price.append(data_dict['orderItems'][num]['priceComponents']['sellingPrice'])
    order_item_id.append(data_dict['orderItems'][num]['orderItemId'])
    listing_id.append(data_dict['orderItems'][num]['listingId'])
    title.append(data_dict['orderItems'][num]['title'])
    price.append(data_dict['orderItems'][num]['price'])
    dispatch_by_date.append(data_dict['orderItems'][num]['dispatchByDate'])
    shipping_pincode.append(data_dict['orderItems'][num]['shippingPincode'])
    fsn.append(data_dict['orderItems'][num]['fsn'])
    sla.append(data_dict['orderItems'][num]['sla'])
    sku.append(data_dict['orderItems'][num]['sku'])
    updated_at.append(data_dict['orderItems'][num]['updatedAt'])
    state_documents.append(data_dict['orderItems'][num]['stateDocuments'])
    shipping_fee.append(data_dict['orderItems'][num]['shippingFee'])
    hold.append(data_dict['orderItems'][num]['hold'])
    order_date.append(data_dict['orderItems'][num]['orderDate'])
    quantity.append(data_dict['orderItems'][num]['quantity'])

I can connect to my database using psycopg2 but i need to pass these lists as parametres. How to do that?
Also if anyone can help me do that through a stored procedure? that would help reduce the load and dependency on the script.

Upvotes: 2

Views: 3353

Answers (1)

Valery Viktorovsky
Valery Viktorovsky

Reputation: 6736

PostgreSQL doesn't have functions to import raw json data directly from file. So you should write a php/python/etc script which reads row from json and insert it to PostgreSQL.

Upvotes: 2

Related Questions