postgres plpgsql 插入 jsonb 而不转义双引号

Question

我正在使用 psycopg2 与 postgres (v13) 数据库交互。我打算动态创建sql脚本来创建表、函数等以在数据库中执行。

我创建了一个脚本来对包含两个 boolean 列和一个 jsonb 列的表进行更新插入。脚本和玩具表位于此db<>fiddle。它工作完美。

问题是当我尝试使用 psycopg2 动态获得相同的结果时。我创建了一个“玩具”示例。下面的设置代码创建一个简单的 3 列表，其中包含 2 个布尔值和 1 个 jsonb 列。设置连接以及一些 sql 脚本来创建 upsert 函数（如上面的小提琴中使用的）并调用/测试该函数：

from psycopg2 import connect
from psycopg2.extras import Json
import pandas as pd


conn_graphstruct = connect(host='localhost', database='graphstruct_data',
                                            port='5665', user='postgres', password='postgres')

cursor_graphstruct = conn_graphstruct.cursor()

def graph_script(x):
    cursor = cursor_graphstruct
    conn = conn_graphstruct
    try:
        cursor.execute(x)
        conn.commit()
    except Exception as e:
        print(e)
        conn.rollback()

def graph_query(x):
    temp = pd.read_sql(x, conn_graphstruct)
    if not temp.empty:
        return temp
    
def graph_fetch(x):
    cursor = cursor_graphstruct
    conn = conn_graphstruct
    try:
        cursor.execute(x)
        records = cursor.fetchall()
        return records
    except Exception as e:
        print(e)

make_table = '''
    drop table if exists graphy;
    create temporary table graphy (
          directed boolean, 
          multigraph boolean,
          graph jsonb);
    create unique index unique_db_name on graphy ((graph->>'name'));

    insert into graphy(directed, multigraph, graph) values(FALSE,FALSE, '{"node_default": {},
    "edge_default": {}, "name": "test_8"}');
'''

make_procedure = '''
    drop procedure if exists p_upsert_meta();
    CREATE OR REPLACE PROCEDURE  p_upsert_meta(x bool, y bool, z jsonb) LANGUAGE plpgsql
    as $$
    begin
        INSERT into graphy (directed, multigraph, graph) values (x,y,z)
        ON CONFLICT ((graph->>'name'))
        DO update
        set (directed, multigraph, graph) = (x,y,z);
    END 
    $$;
'''

run_procedure = '''
    call p_upsert_meta(FALSE,TRUE, '{"node_default": {}, "edge_default": {},
    "name": "test_10"}');
    call p_upsert_meta(FALSE,TRUE, '{"node_default": {}, "edge_default": {},
    "name": "test_10"}');    
'''

然后我运行脚本。首先使用定义的脚本，然后使用动态 SQL 方法来编写查询。

graph_script(make_table)
graph_script(make_procedure)
graph_script(run_procedure)

directed = False
multi = False
graph_name = 'proto_1'

graph = '{"node_default": Null, "edge_default": True,"name": "' + graph_name + '"}'
print(graph)
graph_script('call p_upsert_meta({},{},{})'.format(directed, multi, Json(graph)))
graph_script('call p_upsert_meta({},{},{})'.format(directed, multi, Json(graph)))

应该结果是表中的三个条目。相反，对 jsonb 列（graph）进行查询：

query = '''
        select graph
        from graphy
    '''

graph_fetch(query)

结果是：

{"node_default": Null, "edge_default": True,"name": "proto_1"}  -- composed string

[({'name': 'test_8', 'edge_default': {}, 'node_default': {}},),
 ({'name': 'test_10', 'edge_default': {}, 'node_default': {}},),
 ('{"node_default": Null, "edge_default": True,"name": "proto_1"}',), -- text not jsonb
 ('{"node_default": Null, "edge_default": True,"name": "proto_1"}',)] -- text not jsonb

如果我不使用 psycopg2.extras.Json，我会收到此错误 - syntax error at or close "{" LINE 1 ：调用 p_upsert_meta(False,False,{"node_default": Null, "edge_...。但是使用 Json 转义所有双引号。当发生这种情况时CONFLICT 不起作用。我尝试在函数内转换 z 和 graph 并使用 import json 方法。 .

我缺少什么才能正确插入没有转义双引号的 jsonb ？

注意：

1. 是的，我必须使用 psycopg2

Answer 1

发生这种情况是因为您将字符串传递给 Json。 Json 将 Python 类型适配为 Postgres 中的 Json(b)。一个简单的例子：

cur.execute('insert into json_test (fld_jsonb) values(%s)', [Json({"node_default": None, "edge_default": True,"name": "graph_name"})])

cur.execute('insert into json_test (fld_jsonb) values(%s)', [Json('{"node_default": None, "edge_default": True,"name": "graph_name"}')]) 

con.commit()

cur.execute("select fld_jsonb from json_test")                                                                                                                            

rs = cur.fetchall()                          

# Python dict as Json value
rs[0][0]                                                                                                                                                                  
{'name': 'graph_name', 'edge_default': True, 'node_default': None}

# Python string as Json value
rs[1][0]                                                                                                                                                                  
'{"node_default": None, "edge_default": True,"name": "graph_name"}'

如果您希望 Json 适配正常工作，则使用适当的 Python 类型：list 用于 json array 和 dict< /code> 用于 json 对象。这也适用于包含的类型，因此在 Python 端使用 None 而不是 Null。

更新

如果您想使用字符串，则不要使用 Json 改编：

cur.execute('insert into json_test (fld_jsonb) values(%s)', ['{"node_default": null, "edge_default": true,"name": "graph_name"}'])
con.commit()

rs[2][0]                                                                                                                                                                  
{'name': 'graph_name', 'edge_default': True, 'node_default': None}

尽管如此，您将需要使用 JSON 值，例如 null 和true 而不是 None 和 True。