1

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:

  1. yes, I have to use psycopg2

1 Answer 1

1

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.

Sign up to request clarification or add additional context in comments.

1 Comment

Tnx! The part about how Json works was key. I'd seen many examples and even the docs but somehow that nuance didn't register at all.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.