Insert JSON String in to PostgreSQL table column

Question

Trying to insert following JSON String as is without formatting

str={
    "Trial": {
        "@id": "xxxxx",
        "key1": "aaaaaaaa (BAL-8557) aaaaaaaaaaaaaaaaaa",
        "key2": "aaaaaaaaaaaaa , aaaaaaaaaaaaaaaaaaa",
        "key3": "Yes",
        "key4": "No",
        "key5": {
        "key5": [{
                    "@type": "ABC",
                    "$": "ABC121 "
                },
                {
                    "@type": "ABC",
                    "$": "ABC12133 "
                }]
        },
        "Indications": {
            "Indication": {
                "@id": "1308",
                "$": "aaaaaa"
            }
        }
    }
}

in to following PostgreSQL DB

CREATE TABLE records
(
  id text,
  record json
)

Code I am using to perform the insert operation

cur = conn.cursor()
cur.execute("INSERT INTO trial_records(id, record) VALUES (%s, %s)", ('1', json.dumps(json.loads(str))))

Error I am getting

the JSON object must be str, bytes or bytearray, not 'dict'

Is this the right syntax to store json string as part of the column

You should remove your json.loads call. So it will be json.dumps(str). — sashaaero
– sashaaero, Commented Nov 12, 2018 at 21:19
@sashaaero put it as your answer.. So this question will not show in unanswered.. — dwir182
– dwir182, Commented Nov 13, 2018 at 0:03

sashaaero · Accepted Answer · 2018-11-13 00:35:30Z

2

json.loads(obj) creates json object from string data.

json.dumps(obj) creates string from json object.

So you should change json.dumps(json.loads(str)) to json.dumps(str).

P.S.: I'd also don't recommend to call your variables as type names like str, int. You are making str() and int() call unable from current scope.

answered Nov 13, 2018 at 0:35

sashaaero

2,9388 gold badges26 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Insert JSON String in to PostgreSQL table column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related