Python insert json to Postgres

Question

I’m inserting json data into a jsonb column. when I use python to perform inserts by reading from a file , the data is inserted as string but when I insert directly by assigning json to a variable(commented below) then it is inserted as an object . What am I Doing wrong below ?

import json
import psycopg2
data = open('C:\\xx\\json\.     \temp1.json').read()
my_json = json.dumps(data)
connection = psycopg2.connect(user = 
"xxxxx",
password = 
"xxxxx",
host = "exx",
port = "54xx",
database = 
"dxxx")
cursor = connection.cursor()
insert_query = "insert into gna       (data) values (%s)"
cursor.execute(insert_query,     (my_json,))
connection.commit()
count = cursor.rowcount
print (count, 
"Record inserted successfully into xx table")

Raphael · Accepted Answer · 2019-07-12 21:43:14Z

1

you escape your json :)

data = open('C:\\xx\\json\. \temp1.json').read() is a json string. Your can pass that to postgres and it should be interpreted as json as long as your file really contains a json string.

my_json = json.dumps(data) data is a string. If you dump a string you escape it explicitly saying "this is not a json object"

import json
from io import StringIO

f = StringIO('{"key": "value"}')

data = f.read() 
print(data)  # {"key": "value"}

data = json.dumps(data)
print(data)  # "{\"key\": \"value\"}"

data = json.dumps({"key": "value"})
print(data) # {"key": "value"}

edited Jul 12, 2019 at 21:43

answered Jul 12, 2019 at 21:18

Raphael

1,8112 gold badges10 silver badges27 bronze badges

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

Collectives™ on Stack Overflow

Python insert json to Postgres

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