How to insert/update JSON data into postgreSQL

Question

This is my table schema,

[column] [type]
tablename json  
word  varchar  
text  json

I implemented using psycopg2 with Python,

cur.execute("INSERT INTO json (word,text) VALUES (%s,%s);",(word,text))

word contains list object type but inside are string,

['a','b','c']

text contains list object type but inside is dict (json),

[{'a':'b'},{'c':'d'}]

When I run the function. I got this error wanring below,

" can't adapt type 'dict' "

The question is, How to insert json into postgreSQL, As you see type of text. It's look like dict, But how to assign text variable is json?. or I'm missing something?

Jesse Reza Khorasanee · Accepted Answer · 2019-05-29 05:03:11Z

2

json.dumps() could be used to switch to a string for the database.

import json
postgres_string = json.dumps(text)
# Save postres_string into postgress here

# When you want to retrieve the dictionary, do so as below:
text = json.loads(postgres_string)

answered May 29, 2019 at 5:03

Jesse Reza Khorasanee

3,5514 gold badges40 silver badges56 bronze badges

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

Comments

Shawn.X · Accepted Answer · 2019-05-29 08:05:10Z

0

Well,you use execute function to execute a SQL, just construct the right SQL, it would get success.You want insert a json type data, just use "::" to transform a string type into a json type, like below, it works:

postgres=# insert into json_test(word,text) values('abcd_word','[{"a":"b"},{"c":"d"}]'::json);
INSERT 0 1
postgres=# 
postgres=# select * from json_test ;
 tablename |   word    |         text          
-----------+-----------+-----------------------
           | abcd_word | [{"a":"b"},{"c":"d"}]

answered May 29, 2019 at 8:05

Shawn.X

1,3637 silver badges17 bronze badges

Collectives™ on Stack Overflow

How to insert/update JSON data into postgreSQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related