How to insert nested dictionary/json into MySQL table with Python

Question

I've connected to the https://exchangeratesapi.io/ api like so:

import requests, json, pymysql
from db_credentials import db_config

get_url = "https://api.exchangeratesapi.io/history?start_at=2018-01-01&end_at=2018-02-02&base=GBP"
response = requests.get(get_url)
results = response.content
data = json.loads(results.decode('utf-8'))['rates']

print(data.keys())
print(data)

data.keys() and data looks like this:

dict_keys(['2018-01-22', '2018-01-09', '2018-01-24', '2018-01-03', '2018-01-23', '2018-01-25', '2018-01-18', '2018-02-02', '2018-01-15', '2018-01-02', '2018-01-16', '2018-01-11', '2018-01-17', '2018-01-12', '2018-01-30', '2018-02-01', '2018-01-29', '2018-01-08', '2018-01-10', '2018-01-05', '2018-01-04', '2018-01-26', '2018-01-31', '2018-01-19'])

{'2018-01-02': {'AUD': 1.7327127809,
                'BGN': 2.1986891954,
                'BRL': 4.440996931,
                'CAD': 1.7006733893,
                ...},
 '2018-01-03': {'AUD': 1.730482852,
                'BGN': 2.2064530686,
                'BRL': 4.4264440433,
                ...},
 ...}

I have an empty MySQL table with date, currency and rate columns which I'd like to populate with the above using something along the lines of the following code:

connection = pymysql.connect(**db_config, cursorclass=pymysql.cursors.DictCursor)
with connection.cursor() as cursor:
    insert_statement = "INSERT INTO gbpExchangeRates VALUES (%s)" % data
    cursor.execute(insert_statement)
connection.commit()
connection.close()

Obviously it doesn't work and requires some manipulation beforehand... I tried data = json.dumps(data) but not sure what to do next...

Klemen Tusar · Accepted Answer · 2019-11-08 09:17:28Z

3

Well you can't simply throw JSON at a database and expect it to work like that. You have to flatten it to rows prepared for insertion.

Take a look at the example I wrote using SQLite. It uses the same DB API v2 so it should be fairly compatible with your MySQL driver.

import requests
import json
import sqlite3

get_url = "https://api.exchangeratesapi.io/history?start_at=2018-01-01&end_at=2018-02-02&base=GBP"
response = requests.get(get_url)
results = response.content
data = json.loads(results.decode('utf-8'))['rates']

if data:
    conn = sqlite3.connect('rates.db')

    c = conn.cursor()

    # here I simply created an example table containing all the fields you specified above
    c.execute("""
        CREATE TABLE exchange_rates (
            "id" INTEGER PRIMARY KEY,
            "date" DATE, 
            "currency" TEXT, 
            "rate" NUMERIC
        )
    """)

    c.executemany(
        """INSERT INTO exchange_rates ("date", "currency", "rate") VALUES (?,?,?)""",
        # this is where you iterate over the data and flatten it for SQL
        ((date, currency, value) for currency, value in rates.items() for date, rates in data.items())
    )

    conn.commit()

    conn.close()

edited Nov 8, 2019 at 9:17

answered Nov 8, 2019 at 9:02

Klemen Tusar

9,7494 gold badges36 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

AK91 Over a year ago

Getting duplicated rates for each date and currency

AK91 · Accepted Answer · 2019-11-25 17:44:16Z

0

Building on techouse answer.

Removed quotes around the column names i.e. date, currency, rate in insert statement
Replaced ((date, currency, value) for currency, value in rates.items() for date, rates in data.items()) with insert_data

...

insert_data = []
for date, rates in data.items():
    for currency, value in rates.items():
        list_data = []
        list_data.append(date)
        list_data.append(currency)
        list_data.append(value)
        insert_data.append(list_data)

insert_data

answered Nov 25, 2019 at 17:44

AK91

7412 gold badges17 silver badges45 bronze badges

Collectives™ on Stack Overflow

How to insert nested dictionary/json into MySQL table with Python

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related