时间戳

首页 > 解决方案 > 将json插入postgres python

python - 将json插入postgres python

问题描述

嗨，我想从输出为 JSON 格式的 api 请求数据。我想从 API 中获取数据并将其插入到 postgress 数据库中，但出现以下错误

Traceback (most recent call last):
  File "/Users/xxx/text.py", line 17, in <module>
    cur.execute("INSERT INTO Boliga VALUES (%s)", [Json(data)])
psycopg2.errors.InvalidTextRepresentation: invalid input syntax for type double precision: ""[\n {\n  \"estateId\": 1735758,\n  \"address\": \"Dysseaasen 15A\",\n  \"zipCode\": 2600,\n  \"price\": 5603000,\n  \"soldDate\": \"2021-05-31T22:00:00.000Z\",\n  \"propertyType\": 1,\n  \"saleType\": \"Alm. Salg\",\n  \"sqmPrice\": 35018.75,\n  \"rooms\": 6.0,\n  \"size\": 160,\n  \"buildYear\": 2019,\n  \"change\": 2.9017446,\n  \"guid\": \"18C3917E-E80F-4FF1-BAAA-05DCFF36938C\",\n  \"latitude\": 55.683464,\n  \"longitude\": 12.424398,\n  \"municipalityCode\": 161,\n  \"estateCode\": 319,\n  \"city\": \"Glostrup\",\n  \"groupKey\": null,\n  \"canGetVR\": true\n },\n {\n  \"estateId\": 0,\n  \"address\": \"Skovbrynet 34\",\n  \"zipCode\": 9330,\n  \"price\": 1111111,\n  \"soldDate\": \"2021-05-28T22:00:00.000Z\",\n  \"propertyType\": 1,\n  \"saleType\": \"Alm. Salg\",\n  \"sqmPrice\": 7507.507,\n  \"rooms\": 5.0,\n  \"size\": 148,\n  \"buildYear\": 1977,\n  \"change\": 0.0,\n  \"guid\": \"94199131-1023-496C-89F4-0FA101D80269\",\n  \"latitude\": 57.160492,\n  \"longitude\": 10.28083,\n  \"municipalityCode\": 810,\n  \"estateCode\": 14258,\n  \"city\": \"Dronninglund\",\n  \"groupKey\": null,\n  \"canGetVR\": true\n }\n]""

我有以下python代码

import requests as r
import json
import psycopg2
import psycopg2.extras
from psycopg2.extras import Json
import pandas as pd


d=r.get('https://api.boliga.dk/api/v2/sold/search/results?pageSize=2').json()
data=json.dumps(d['results'], indent=1,ensure_ascii=False)
DB_HOST = "localhost"
DB_NAME = "bolig"
DB_USER = "postgres"
DB_PASS = "Password"
con = psycopg2.connect(dbname=DB_NAME, user=DB_USER, password=DB_PASS, host=DB_HOST)
cur= con.cursor() 
cur.execute("INSERT INTO Boliga VALUES (%s)", [Json(data)])

我的桌子看起来像这样

 estateid | address | zipcode | price | solddate | propertytype | saletype | sqmprice | rooms | size | buildyear | change | guid | latitude | longitude | municipalitycode | estatecode | city | groupkey | cangetvr 
----------+---------+---------+-------+----------+--------------+----------+----------+-------+------+-----------+--------+------+----------+-----------+------------------+------------+------+----------+----------

标签： pythonjsonpostgresql

解决方案

  1. 不要重新字符串化请求

  2. 您的响应似乎是一个列表，而不是可以展平为列的单个对象。

查阅文档，您可以看到他们Json()围绕标准 Python 字典而不是列表而不是字符串调用。

所以，试试下面的

d = r.get('https://api.boliga.dk/api/v2/sold/search/results?pageSize=2').json()

con = psycopg2.connect(dbname=DB_NAME, user=DB_USER, password=DB_PASS, host=DB_HOST)
cur = con.cursor() 
for obj in d:
    cur.execute("INSERT INTO Boliga VALUES (%s)", [ Json(obj) ])

# be sure to commit/close the cursor

如果这不起作用，您可能需要调整某些数据库字段的大小写以匹配 JSON 响应，或者自己解析响应并修复字段名称

推荐阅读