postgres plpgsql insert jsonb without escaping double quotes-CodePudding

I am using psycopg2 to interact with a postgres (v13) data base. I intend to dynamically create sql scripts to create tables, functions, etc. for execution in the db.

I created as script to do an upsert to a table with two boolean and one jsonb columns. The script and a toy table are at this db<>fiddle. It works perfectly.

The problem is when I try to get the same results dynamically with psycopg2. I created a 'toy' example. The set up code below creates a simple 3 column table with 2 boolean and one jsonb column. Connectivity is set up plus some sql scripts to create the upsert functions (as used in the fiddle above) and to call/test the function:

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"}');    
'''

I then run the scripts. First using the scripts as defined and then using a dynamic sql approach where I compose the query.

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

What should result are three entries in the table. Instead a query on the jsonb column (graph):

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

graph_fetch(query)

Results in:

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

If I don't use psycopg2.extras.Json I get this error - syntax error at or near "{" LINE 1: call p_upsert_meta(False,False,{"node_default": Null, "edge_.... But using Json escapes all the double quotes. When that happens CONFLICT won't work. I've tried casting z and graph within the function and using import json methods.

What am I missing to properly insert a jsonb without the escaped double quotes?

Notes:

yes, I have to use psycopg2

CodePudding user response：

This is happening because you are passing a string to Json. Json does adaptation of Python types to Json(b) in Postgres. A quick example:

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

If you want the Json adaptation to work properly then use appropriate Python types: list for json array and dict for json object. This also applies to the contained types so None instead of Null on the Python side.

UPDATE

If you want to use a string then don't use the Json adaptation:

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}

Though then you will need to use JSON values e.g. null and true instead of None and True.