[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