-1

[ { "id": 1, "name": "Lea", "username": "Bret", "email": "hhaa@gma", "address": { "street": "Light", "suite": "Apt. 5", "city": "Gwen", "zipcode": "3874", "geo": { "lat": "-37.3159", "lng": "81.1496" } }, "phone": "1-770", "website": "hilde.org", "company": { "name": "Roma", "catchPhrase": "net", "bs": "markets" } }, { "id": 2, "name": "Er", "username": "Ant", "email": "Sh", "address": { "street": "Vis", "suite": "89", "city": "Wibrugh", "zipcode": "905", "geo": { "lat": "-43.9509", "lng": "-34.4618" } }, "phone": "010-69", "website": "ansia.net", "company": { "name": "Deist", "catchPhrase": "contingency", "bs": " supply-chains" } } ]

I am getting this data from webscraping and I would like to store this data into netezza database. Can you Please give me sample code? Do I need to correct Json before? If yes, How would I do it? And when I am trying to use items iterate in list, I am only getting the last user id details.

Dedipya B
  • 1
  • 3
  • 1
    are you sharing actual user data here? – gold_cy Jan 20 '19 at 23:21
  • No But I changed it to make it easy to have just two users now. – Dedipya B Jan 20 '19 at 23:52
  • http://dwgeek.com/connecting-netezza-using-python-pyodbc-working-example.html/ – gold_cy Jan 21 '19 at 00:08
  • I would like to know how I insert the data into exact key value pair. I am having issue with that. But I am having issue with the insert with the for loop with key value pairs.. Or is there any other way that I can handle it? – Dedipya B Jan 21 '19 at 19:38
  • import pandas as pd import pyodbc import json import urllib.request from bs4 import BeautifulSoup as bs import requests r=requests.get('https://jsonplaceholder.com/users') data = r.text print (data) data = json.loads(data) conn = pyodbc.connect("DRIVER={NetezzaSQL};SERVER=dw; PORT=5;DATABASE=SYSTEM; UID=3;PWD=N!;") cus=conn.cursor() for json in data: for (k, v) in json.items(): cus.execute("INSERT INTO table_name (k, v) VALUES ('{0}', '{1}')") conn.commit() – Dedipya B Jan 21 '19 at 19:42
  • Can someone help me with this code? importing the list into netezza? for json in data: for (k, v) in json.items(): cus.execute("INSERT INTO table_name (k, v) VALUES ('{0}', '{1}')")... This is not working.. – Dedipya B Jan 22 '19 at 04:42

1 Answers1

0

I would suggest a different approach, due to better scalability:

1) load the raw txt data into a (temporary) table with the ‘external table’ syntax of Netezza

2) use these functions to parse the Json data into table columns: https://developer.ibm.com/articles/i-json-table-trs/

Lars G Olsen
  • 1,093
  • 8
  • 11