Fetch, parse, and store JSON in PostgreSQL

Question

I was given a task to read JSON from a URL and then parse it in python. Each field will be assigned to a variable to store the value. The value will then be stored into postgres table.

Example of JSON

{
 "forecasts": 
[
 {
 "class": "fod_long_range_daily",
 "expire_time_gmt": 1525126617,
 "night": {
 "fcst_valid": 1525158000,
 "fcst_valid_local": "2018-05-01T19:00:00+1200",
 "golf_category": ""
 },
 "day": {
 "uv_warning": 0,
 "uv_desc": "Moderate",
 "golf_index": 10,
 "golf_category": "Excellent"
 }
 }
]
}

I was told that this way i am able to parse JSON and read it into postgres. Is this the right way to do this? Will there be performance issues?

import urllib3
import psycopg2
import json
conn = psycopg2.connect(host="localhost", database="nzaus", 
user="admin", password="123321")
print("Database Connected")
cur = conn.cursor()
rowcount = cur.rowcount
http = urllib3.PoolManager()
url = "https://api.data.com/v1/geocode/-35.228208/174.095969/forecast/daily/15day.json?language=en-US&units=m&apiKey=1234"
try:
 response = http.request('GET', url)
 data = json.loads(response.data.decode('utf-8'))
 for item in data['forecasts']:
 class = None
 time = None
 fcst_valid = None
 golf_category = None
 result = []
 class = item['class']
 time = item['expire_time_gmt']
 fcst_valid = item['night']['fcst_valid']
 golf_category = item['morning']['golf_category']
 result = [class,time,fcst_valid,golf_category]
 cur.execute("""INSERT into datatable 
 VALUES
 ( %s,
 %s,
 %s,
 %s,
 )""",(result))
 conn.commit()
 cur.close()
 except IOError as io:
 print("cannot open")

• Derek: version 3, using urllib3

Answer 1

I would narrow down your try:except to only the statement(s) which could throw the error. Having such a large chunk of code in a try:except block could result in many exceptions thrown, none of which would be caught as you're only looking for IOError.

Also, for performance, you should build the statement and the data set from the loop, then throw the statement against cursor.executemany(statement, data) as a single database call instead.

Regarding construction, your code is just one huge blob. It's important that you refactor each operation into its own function, this saves on tracking down errors, and if you make changes in the future, your changes only affect one function. If the change is not liked by the rest of your code, it's easy to back out.
Can you imagine if you made 5 or 10 changes in your program as it is right now, and then it stops working? Which change was the breaking change? How would you find out? Why did it break? etc. Also, I'm not talking just simple syntax errors, I'm also talking about logic errors or data errors. These can be hard to track down if you don't have any tests validating your code.

Good luck!

• Derek: Thanks for the advice ! Does that mean that I should store the json results into an array/list, then when insert into postgres just read from the array/list?
• C. Harley: Perhaps a tuple added to a list? e.g.: [(class, time_, fcst_valid, category), (class, time_, fcst_valid, category), etc]
• Derek: So if i use psycopg2, just cur.execute(""" insert into table A( class, time_, fcst_valid, category) VALUES ( %s,%s,%s,%s)""",(list)) ?