3
\$\begingroup\$

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")
\$\endgroup\$
  • \$\begingroup\$ version 3, using urllib3 \$\endgroup\$ – Derek May 1 '18 at 23:33
1
\$\begingroup\$

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!

\$\endgroup\$
  • \$\begingroup\$ 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? \$\endgroup\$ – Derek May 4 '18 at 9:15
  • \$\begingroup\$ Perhaps a tuple added to a list? e.g.: [(class, time_, fcst_valid, category), (class, time_, fcst_valid, category), etc] \$\endgroup\$ – C. Harley May 5 '18 at 11:55
  • \$\begingroup\$ So if i use psycopg2, just cur.execute(""" insert into table A( class, time_, fcst_valid, category) VALUES ( %s,%s,%s,%s)""",(list)) ? \$\endgroup\$ – Derek May 6 '18 at 20:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.