Question 249290 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – psycopg3 inserting dict into JSONB field

I have a table with a JSONB field and would like to insert into it using a named dict like so:

sql = "INSERT INTO tbl (id, json_fld) VALUES (%(id)s, %(json_fld)s)"
conn.execute(sql, {'id':1, 'json_fld': {'a':1,'b':false, 'c': 'yes'}});

I tried the answers in this question but those all apply to psycopg2 and NOT psycopg3 and they do not work here (notably I tried):

conn.execute(sql, {'id':1, 'json_fld': json.dumps({'a':1,'b':false, 'c': 'yes'})});

The error remains the same:

psycopg.ProgrammingError: cannot adapt type ‘dict’ using placeholder
‘%s’ (format: AUTO)

Tags:

2

Answers


  1. 0

    You can achieve this in psycopg3 by converting the dictionary to JSON first using json.dumps function.

    data = {
    'id': 1,
    'json_fld': {'a': 1, 'b': False, 'c': 'yes'}
}

json_data = json.dumps(data['json_fld'])

sql = "INSERT INTO tbl (id, json_fld) VALUES (%(id)s, %(json_fld)s)"

cursor.execute(sql, {'id': data['id'], 'json_fld': json_data})

conn.commit()
    Login or Signup to reply.
  2. 0

    Python code to convert dict to jsonb using psycopg JSON adapters described here JSON adaptation section JSON adaptation.

    import psycopg
from psycopg.types.json import Jsonb

con = psycopg.connect("dbname=test user=postgres")
cur = con.cursor()
cur.execute("insert into json_test values(%s, %s)", 
            [1, Jsonb({'a':1,'b': False, 'c': 'yes'})])
con.commit()

    This results in:

    select * from json_test ;

 id |              js_fld              
----+----------------------------------
  1 | {"a": 1, "b": false, "c": "yes"}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search