python - How To Preserve A Json Like Substring While Inserting Data To Postgres Using PyGresSQL(using psycopg)

Question

[Please visit the last section(second edit) for code example]

I am modeling a postgres solution in which I have to insert a data in such a way that one piece of python dictionay should not get converted to json and be treated as a raw string.

Database has people table like below

create table if not exists people(
  name text,
  metadata text
);

I have a custom postgres procedure insert_people(json) to insert multiple rows at once to the postgres database with an input python dictionary as

{
  'people': [
     {'name': 'Johnathan', 'metadata': {'height': 6, 'nickname': 'john'}},
     {'name': 'David', 'metadata': 'any random string.'},
  ]
}

Please pay attention to metadata key, in first item it contains a dictionary type while in second instance it contains a plain string.

I want to convert the above dictionary to json in turn, feed it to insert_people(json) while keeping metadata value type as string even for the dictionary value. When I run json.dumps the process converts metadata value to json too (like, {"height": 6, "nickname": "john"}) instead I want it to get remain as a raw string ('{'height': 6, 'nickname': 'john'}'). Is there a way achieve this, I tried parametrization but I am not sure how can I parametrize multiple instances of same metadata key for all the list items.

[edit] calling script is

people = json.dumps({
  'people': [
     {'name': 'Johnathan', 'metadata': {'height': 6, 'nickname': 'john'}},
     {'name': 'David', 'metadata': 'any random string.'},
  ]
})
db_cur.execute(
            """call insert_people(
                   people=>%s
            )""",
            [people]
            )

which fails with Token "john" is invalid. Any help would be really appreciated.

Cheers, DD

---

[second edit with all the components involved, code and error]

people: table creation

create table if not exists people(
  name text,
  metadata text
);

_insert_person_record: internal function to insert record to the people table

create or replace procedure _insert_person_record
  (
    name text,
    metadata text
  )
  language plpgsql
  as
  $ipr$
  declare
    insert_query text;
  begin
    insert_query := '
      insert into people(name, metadata) values
        (''' || name || ''', ''' || metadata ''')
    ';
    execute insert_query;
  end
  $ipr$
;

insert_people: public-facing DB procedure which will get invoked from the python client

create or replace procedure insert_people
  (
    people json
  )
  language plpgsql
  as
  $ip$
  declare
    insert_query text;
    person_entries record;
    person_attr text;
    arg text;
    args text [];
    args_str text;
    person_value text;

  begin
    for person_entries in (select json_array_elements(people->'people') as pe) loop

      args := '{}';

      for person_attr, person_value in select * from json_each(person_entries.pe) loop

        person_attr := trim(both '"' from person_attr);
        person_value := '''' || trim(both '"' from person_value) || '''';

        args := args || (person_attr || '=>' || person_value);
        
      end loop;

      args_str := array_to_string(args, ', ');

      insert_query := '
        call _insert_person_record(
        ' || args_str ||
        ');'
      ;

      execute insert_query;

    end loop;

  end
  $ip$
;

populate_poeple.py: to insert entry into DB

people = [
    {'name': 'Johnathan', 'metadata': {'height': 6, 'nickname': 'john'}},
    {'name': 'David', 'metadata': 'any "random" string.'},
]
people = json.dumps({'people': people})

db_conn, db_cur = get_db_objs()

try:
  db_cur.execute(
  """call insert_people(
           people=>%s
  )""",
  [people]
  )
  db_conn.commit()
except Exception, e:
  db_conn.rollback()
  print str(e)
finally:
  db_conn.close()

this results into an error:

error 'ERROR:  invalid input syntax for type json
LINE 2:                  people=>'{"people": [{"name": "Johnathan", ...
                                 ^
DETAIL:  Token "random" is invalid.
CONTEXT:  JSON data, line 1: ...6}}, {"name": "David", "metadata": "any \"random...
' in 'call insert_people(
                 people=>'{"people": [{"name": "Johnathan", "metadata": {"nickname": "john", "height": 6}}, {"name": "David", "metadata": "any \"random\" strin
g."}]}'
        )'

basically, anywhere where quotes(single or double) exists in string is resulting in an error, here the first record is fine.

question from:https://stackoverflow.com/questions/65906869/how-to-preserve-a-json-like-substring-while-inserting-data-to-postgres-using-pyg

Answer 1

Waitting for answers