如何用Python在MySQL表中插入嵌套dictionary/json

Question

我已连接到https://exchangeratesapi.io/api，如下所示：

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()和{}如下所示：

^{pr2}$

我有一个空的MySQL表，其中包含date、currency和rate列，我想用上面的代码填充这些列，代码如下：

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()

显然它不起作用，需要事先进行一些操作。。。 我试过data = json.dumps(data)，但不知道下一步该做什么。。。在

Answer 1

好吧，你不能简单地向数据库抛出JSON，然后期望它像那样工作。您必须将其展平为准备插入的行。在

看看我用SQLite编写的示例。它使用相同的DB API v2，因此它应该与MySQL驱动程序相当兼容。在

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()