PostgreSQL为记录插入多个JSONB列

Question

我无法将多个python字典插入到单独的JSONB列中，用于一个curd .execute使用python with pyscopg2。我可以单独插入每个字典，但不指定ID，它会创建新记录。

创建字典和表:

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("==============")

成功加载dict1>Col1_jsonb和dict2>单独col2_jsonb

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)

加载dict1>Col1_jsonb和dict2>Col2_jsonb在一个查询中执行命令

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)

pgadmin查询的截图

Answer 1

使用psycopg2内置的JSON适配并执行:

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)])

指出:

1. 不要使用F字符串，在这种情况下，它是不需要的。

2. 不需要%s::jsonb，类型适配由psycopg2处理

3. 使用psycopg2 JSON适配器Json来正确适配字典。