CODEWITHSUNDEEP
pythonjsonpostgresqlpsycopg2
zzob
zzob

Reputation: 1124

How to insert/update JSON data into postgreSQL

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?

Upvotes: 1

Views: 1322

Answers (2)

Shawn.X
Shawn.X

Reputation: 1353

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"}]

Upvotes: 0

Jesse Reza Khorasanee
Jesse Reza Khorasanee

Reputation: 3471

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)

Upvotes: 2

Related Questions