尝试将字典传递给数据帧时，如何解决：ValueError：如果使用所有标量值，则必须传递索引

Question

我正在从RESTAPI提取数据，需要将其写入SQL表。 我的方法是将JSON数据添加到字典中，将字典传递到dataframe，然后将dataframe写入SQL

将balances()函数传递给数据帧时，我遇到以下错误：

ValueError: If using all scalar values, you must pass an index

我做错了什么？还可以自由地提供关于代码结构的反馈，我觉得有更简单的方法可以用更少的代码提取数据

def balances():   
    for b in get_balances["balances"]:
        result = {}
        result["employeeID"] = int(b.get("employeeID"))
        result["resourceID"] = int(b.get("resourceID"))
        result["resourceType"] = int(b.get("resourceType"))
        if b.get("startDate") is None:
            pass
        else:
            result["startDate"] = b.get("startDate").split("#")[0]
        if b.get("endDate") is None:
            pass
        else:
            result["endDate"] = b.get("endDate").split("#")[0]
        result["minutesLeft"] = b.get("minutesLeft")
        result["minutestoTake"] = b.get("minutestoTake")
        result["minutesTaken"] = b.get("minutesTaken")
        result["minutesTakenPast"] = b.get("minutesTakenPast")
        result["minutestakenFuture"] = b.get("minutesTakenFuture")
        result["periodMinutesToTake"] = b.get("periodMinutesToTake")
        result["periodMinutesTaken"] = b.get("periodMinutesTaken")
        for h in b.get("history"):
            if h.get("planningDate") is None:
                pass
            else:
                result["planningDate"] = h.get("planningDate").split("#")[0]
            result["resourceTypeHistory"] = h.get("resourceType")
            result["resourceIDHistory"] = h.get("resourceID")
            result["minutes"] = h.get("minutes")
            result["balanceMinutes"] = h.get("balanceMinutes")
            result["remark"] = h.get("remark")
            yield result

print(pd.DataFrame(balances()))

#ValueError: If using all scalar values, you must pass an index

两行的输出数据示例：

{'employeeID': 569, 'resourceID': 230, 'resourceType': 144, 'startDate': '2020-01-01', 'endDate': '2020-12-31', 'minutesLeft': 11281, 'minutestoTake': None, 'minutesTaken': 960, 'minutesTakenPast': 0, 'minutestakenFuture': -960, 'periodMinutesToTake': 0, 'periodMinutesTaken': 0, 'planningDate': '2020-01-01', 'resourceTypeHistory': 15, 'resourceIDHistory': 3, 'minutes': 12000, 'balanceMinutes': 12000, 'remark': ''}
{'employeeID': 877, 'resourceID': 33, 'resourceType': 125, 'startDate': '2020-01-01', 'endDate': '2020-12-31', 'minutesLeft': 11281, 'minutestoTake': None, 'minutesTaken': 960, 'minutesTakenPast': 0, 'minutestakenFuture': -960, 'periodMinutesToTake': 0, 'periodMinutesTaken': 0, 'planningDate': '2020-06-05', 'resourceTypeHistory': 2, 'resourceIDHistory': 3, 'minutes': -480, 'balanceMinutes': 11281, 'remark': ''}

Answer 1

使用json_normalize处理示例JSON：

import pandas as pd
import json

with open('1.json', 'r+') as f:
    data = json.load(f)

df = pd.json_normalize(data)
print(df)

   employeeID  resourceID  resourceType   startDate     endDate  ...  resourceTypeHistory resourceIDHistory  minutes  balanceMinutes  remark
0         569         230           144  2020-01-01  2020-12-31  ...                   15                 3    12000           12000
1         877          33           125  2020-01-01  2020-12-31  ...                    2                 3     -480           11281