Insert json data into postgres table using python

We have a python script which pulls data form an API endpoint this way:

import urllib, json
url = "http://api.exchangeratesapi.io/v1/latest?access_key={test}"
response = urllib.urlopen(url)
record_list = json.loads(response.read())

We got to this point and we do see the data in record_list, however we don’t know how to parse the data and insert it into a table.

We tried:

if type(record_list) == list:
    first_record = record_list[0]

but it seems it is not a list, so what it is and how to import it into the DB?

The table has only 2 fields (currency_name and rate)

record_list sample:

{"success":true,"timestamp":1619448844,"base":"EUR","date":"2021-04-26","rates":{"AED":4.438765,"AFN":93.597683,"ALL":123.266852,"AMD":628.973606,"ANG":2.169505,"AOA":793.273462,"ARS":112.71963,"AUD":1.547521,"AWG":2.173782,"AZN":2.056227,"BAM":1.954933,"BBD":2.440381,"BDT":102.484579,"BGN":1.956421,"BHD":0.455567,"BIF":2377.111091,"BMD":1.208496,"BND":1.602106,"BOB":8.333368,"BRL":6.583276,"BSD":1.208646,"BTC":2.2483098e-5,"BTN":90.32643,"BWP":13.045517}}

Thanks!

Answer

Based on assumptions about what you are trying to achieve, an example:

create table currency_rate(currency varchar, rate numeric);

import psycopg2
import json
con = psycopg2.connect("dbname=test user=aklaver host=localhost")
cur = con.cursor()
insert_sql = """INSERT INTO currency_rate VALUES(%s, %s)"""

record_list = '{"success":true,"timestamp":1619448844,"base":"EUR","date":"2021-04-26","rates":{"AED":4.438765,"AFN":93.597683,"ALL":123.266852,"AMD":628.973606,"ANG":2.169505,"AOA":793.273462,"ARS":112.71963,"AUD":1.547521,"AWG":2.173782,"AZN":2.056227,"BAM":1.954933,"BBD":2.440381,"BDT":102.484579,"BGN":1.956421,"BHD":0.455567,"BIF":2377.111091,"BMD":1.208496,"BND":1.602106,"BOB":8.333368,"BRL":6.583276,"BSD":1.208646,"BTC":2.2483098e-5,"BTN":90.32643,"BWP":13.045517}}'

record_dict = json.loads(record_list)
for record in record_dict["rates"].items():
    cur.execute(insert_sql, [record[0], record[1]])
con.commit()

select * from currency_rate ;
 currency |      rate      
----------+----------------
 AED      |       4.438765
 AFN      |      93.597683
 ALL      |     123.266852
 AMD      |     628.973606
 ANG      |       2.169505
 AOA      |     793.273462
 ARS      |      112.71963
 AUD      |       1.547521
 AWG      |       2.173782
 AZN      |       2.056227
 BAM      |       1.954933
 BBD      |       2.440381
 BDT      |     102.484579
 BGN      |       1.956421
 BHD      |       0.455567
 BIF      |    2377.111091
 BMD      |       1.208496
 BND      |       1.602106
 BOB      |       8.333368
 BRL      |       6.583276
 BSD      |       1.208646
 BTC      | 0.000022483098
 BTN      |       90.32643
 BWP      |      13.045517

record_list is a JSON object as string. Use json.loads to convert to Python dictionary. Then use psycopg2 to INSERT records. This is done using a parameterized query(insert_sql) which is executed in the for loop using the dictionary items() from the record_dict['rates]` dictionary. The transactions are the then committed to have the data persist to the table.