如何在python中迭代JSON列表并插入PostgreSQL？

Question

我有点卡住了，我能想到的解决办法都会以意大利面代码结束。你知道吗

我有下面的JSON，我在python3中创建了一个对象，并且我必须将值插入到PostgreSQL中。你知道吗

{
    "name": "test",
    "country": "DE",
    "time": "21-Oct-2019 (09:58:01.694763)",
    "toCurrency": ["EUR", "USD", "GBP"],
    "fromCurrency": ["DKK", "DKK", "DKK"],
    "buyMargin": ["1.2800", "1.2800", "2.0000"],
    "sellMargin": ["1.2800", "1.2800", "2.0000"],
    "unit": "M100" 
}

我得到了一个例外：can only concatenate str (not "list") to str

• 如何在Python中遍历这些列表并一次插入一个值？你知道吗

同时，每次都应插入'name'、'country'、'time'的其他值。你知道吗

目前我可以插入以下JSON：

{
"name": "test",
"country": "DE",
"time": "21-Oct-2019 (09:58:01.694763)",
"toCurrency": "EUR",
"fromCurrency": "DKK",
"buyMargin": "1.2800",
"sellMargin": "1.2800",
"unit": "M100" 
}

Answer 1

使用pandas将数据转换为数据帧。然后使用sqlalchemy，将这个数据帧作为表存储在PostGre中。你知道吗

import pandas as pd
from sqlalchemy import create_engine
d1 = {
    "name": "test",
    "country": "DE",
    "time": "21-Oct-2019 (09:58:01.694763)",
    "toCurrency": ["EUR", "USD", "GBP"],
    "fromCurrency": ["DKK", "DKK", "DKK"],
    "buyMargin": ["1.2800", "1.2800", "2.0000"],
    "sellMargin": ["1.2800", "1.2800", "2.0000"],
    "unit": "M100" 
}
df1 = pd.DataFrame(d1)
engine = create_engine(
        'postgresql://username:password@host:port/database')
df1.to_sql(tablename, engine, if_exists='append', index=False)

您的数据帧如下所示。并将类似地存储为PostGre中的表

Answer 2

如果我理解正确，并且您的toCurrency、fromCurrency、buyMargin和sellMargin的元素数应该相等，那么以下应该对您有效：

j = {
    "name": "test",
    "country": "DE",
    "time": "21-Oct-2019 (09:58:01.694763)",
    "toCurrency": ["EUR", "USD", "GBP"],
    "fromCurrency": ["DKK", "DKK", "DKK"],
    "buyMargin": ["1.2800", "1.2800", "2.0000"],
    "sellMargin": ["1.2800", "1.2800", "2.0000"],
    "unit": "M100"
}

for idx, val in enumerate(j['toCurrency']):
    print(j['toCurrency'][idx], j['fromCurrency'][idx], j['buyMargin'][idx], j['sellMargin'][idx])

因此，insert语句应该位于print的位置，列也应该相应地位于print的位置。你知道吗