[SOLVED] psycopg3 inserting dict into JSONB field

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)

Solution

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