Insert json data into postgres table using python

Question

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!

You haven't included any information about how the database is configured or what tables it has. If you want to know what structure record_list is, you could try printing it. If your question is actually about postgres, please include some code showing what you've done so far with postgres. — blueteeth
– blueteeth, Commented Apr 26, 2021 at 14:52
Edit your question to show value of record_list. Also what do you want to do with the data, insert it into a single JSON(B) type field or insert across multiple fields? Edit your question to indicate the previous as well as include the schema for the table. — Adrian Klaver
– Adrian Klaver, Commented Apr 26, 2021 at 15:20
So it started as JSON object string and got converted by json.loads into Python dictionary. You can the rates by doing record_list["rates"] which will yield a dictionary of currency/rate values. You can iterate over that to get the values to insert into the table. It would help if you edit your question to show the exact data you want to pull out of the dictionary and into which field the values should go. — Adrian Klaver
– Adrian Klaver, Commented Apr 26, 2021 at 16:10
Oops, record_list is actually JSON. See my answer below to see how to deal with this. — Adrian Klaver
– Adrian Klaver, Commented Apr 26, 2021 at 16:30

Adrian Klaver · Accepted Answer · 2021-04-26 16:29:04Z

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.

Thanks! I was able to get the keys and values doing this: url = "api.exchangeratesapi.io/v1/…{test}" response = urllib.request.urlopen(url) parsed = json.loads(response.read()) rates=parsed['rates'] for key, value in record_list.items(): print (key, 'has a rate of', rates[key])

Collectives™ on Stack Overflow

Insert json data into postgres table using python

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related