I am unable to insert multiple python dict into separate JSONB columns for a new record (auto ID) for one cur.execute using python with pyscopg2. I am able to insert each dict individually, but without specifying an ID, it creates new records.

Create Dictionaries and Table:

dict1 = {"dict1Key1":"dict1value1", "dict1Key2":"dict1value2"}
dict2 = {"dict2Key1":"dict2value1", "dict2Key2":"dict2value2"}

query_test_jsonb_many = """CREATE TABLE IF NOT EXISTS table_test_jsonb_many (id serial PRIMARY KEY NOT NULL, col1_jsonb JSONB, col2_jsonb JSONB);"""

try:
    with psycopg2.connect(dbname=dbname, user=user,
                                password=password, port=port, host=host) as con:
        cur = con.cursor()
        cur.execute(query_test_jsonb_many)
        print(query_test_jsonb_many)
        con.commit()
        
except psycopg2.Error as e:
    print("Fail to execute due to the error:", e)

print("==============")
print("dict1: "   str(dict1))
print("dict1 Type is: "   str(type(dict1)))
print("==============")
print("dict2: "   str(dict2))
print("dict1 Type is: "   str(type(dict1)))
print("==============")

Successfully load dict1 > col1_jsonb and dict2 > col2_jsonb individually

try:
    with psycopg2.connect(dbname=dbname, user=user,
                                password=password, port=port, host=host) as con:
        cur = con.cursor()

        cur.execute(f'''INSERT INTO table_test_jsonb_many (col1_jsonb) VALUES (%s::jsonb);''',([json.dumps(dict(dict1))]))
        cur.execute(f'''INSERT INTO table_test_jsonb_many (col2_jsonb) VALUES (%s::jsonb);''',([json.dumps(dict(dict2))]))
except psycopg2.Error as e:
    print("Fail to execute due to the error:", e)

Fail to load dict1 > col1_jsonb and dict2 > col2_jsonb in one query execute command

try:
    with psycopg2.connect(dbname=dbname, user=user,
                                password=password, port=port, host=host) as con:
        cur = con.cursor()

        cur.execute(f'''INSERT INTO table_test_jsonb_many (col1_jsonb, col2_jsonb) VALUES (%s::jsonb, %s::jsonb);''',([json.dumps(dict(dict1))],[json.dumps(dict(dict2))]))
except psycopg2.Error as e:
    print("Fail to execute due to the error:", e)

screenshot of pgadmin query

CodePudding user response：

Use the psycopg2 built in JSON adaption and do:

import psycopg2
from psycopg2.extras import Json 

cur.execute("INSERT INTO table_test_jsonb_many (col1_jsonb, col2_jsonb) VALUES (%s, %s)",[Json(dict1), Json(dict2)])

Notes:

1. Do not use F strings and in this case it was not needed anyway.

2. No need for %s::jsonb, the type adaption is handled by psycopg2

3. Use the psycopg2 JSON adapter Json to properly adapt the dicts.