python - 从 json 文件到 postgres 表的数据

Question

我有一个 json 文件，我想从中将数据放入我的 postgres 表中。文件链接 http://tempsend.com/0013F2BE73 .
我的 json 结构如下:

{"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":     ....

我的数据库表结构是:

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)

我在 ubuntu 14.04 上，文件在我的下载中。如何将数据从此文件插入到我的 postgres 表中？

编辑:

我写了一个 python 代码来解析 json 并将所需的值存储在不同的列表中。

我的代码:

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'])

我可以使用 psycopg2 连接到我的数据库，但我需要将这些列表作为参数传递。怎么做？
另外，是否有人可以通过存储过程帮助我做到这一点？这将有助于减少脚本的负载和依赖性。

Answer 1

PostgreSQL 没有直接从文件导入原始 json 数据的函数。所以你应该编写一个 php/python/etc 脚本，它从 json 中读取行并将其插入到 PostgreSQL。