我试图在python脚本中使用PostgreSQL在JSONB列中插入一个JSON文件
首先,我将csv文件加载到数据帧中,填充了一些NaN并将其转储到JSON中:
df = pd.read_csv("casos.csv", names=["casos"], encoding ="utf-8", engine='python')
df2 = pd.read_csv("corregimientos.csv", encoding="utf-8", engine='python')
df2 = df2.replace(np.nan, None, regex=True)
df2 = pd.concat([df2, df], axis = 1)
for column in range(0,3,1):
for row in range(len(df2)):
if df2.iloc[row,column] is not None:
val = df2.iloc[row,column]
elif df2.iloc[row,column] is None:
df2.iloc[row,column] = val
jCorreg = df2.to_json(orient = "records", lines = True)
jCorreg = [json.loads(line) for line in open('corregimientos.json', 'r')]
这是我的密码:
cur.execute(''' CREATE TABLE casos (
id serial NOT NULL PRIMARY KEY,
nlcasos JSONB NOT NULL);''')
cur.execute('INSERT INTO casos (nlcasos) VALUES (%s)',jCorreg)
当我尝试从JSON插入数据时,我得到了上面提到的错误
TypeError: not all arguments converted during string formatting
我不知道如何解决这个问题。我也试着这样插入:
cur.execute('INSERT INTO casos (nlcasos) VALUES ("'+jCorreg+'")')
但最后出现了另一个错误,我假设这是因为它认为字符串的结尾是另一个(“)
SyntaxError: syntax error at or near "provincia"
LINE 3: "provincia": "Bocas del Toro",
JSON jCorreg文件如下所示:
{"provincia":"Bocas del Toro","distrito":"Almirante","corregimientos":"Puerto Almirante","sede del distrito":"Puerto Almirante","casos":303}
{"provincia":"Bocas del Toro","distrito":"Almirante","corregimientos":"Barriada Guaym\u00ed","sede del distrito":"Puerto Almirante","casos":386}
{"provincia":"Bocas del Toro","distrito":"Almirante","corregimientos":"Barrio Franc\u00e9s","sede del distrito":"Puerto Almirante","casos":104}
等等
我通过改变一些事情设法解决了这个问题。 我使用json.loads()将jCorreg json转换为dict
之后,我在[]中为数据库的insert语句添加了一个json.dumps(),其中包含for:
我还尝试将插入句改为:
没有将JSON更改为dict,它也可以工作
相关问题 更多 >
编程相关推荐