0

I am trying to pass json object as a parameter to my stored procedure. I tried many things and solved couple of error atlast I am stuck with the error : Error : psycopg2 connect error: cannot pass more than 100 arguments to a function LINE 1: SELECT * FROM insert_usr_details_in_all_tables('{','"','N','... i tried json.dumps, json.loads etc but unfortunately nothing is working for me if i am executing the postgresql from scripts with same input it works fine. below is my stored procedure..



CREATE OR REPLACE PROCEDURE public.insert_usr_details_in_all_tables(
    jsonobject text)
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
    skill_s  Text[] := '{}';
    selectedSkill TEXT;
    edu TEXT;
    email TEXT;
    phone TEXT;
    usr_name TEXT;
    clg TEXT;
    lstEmployer TEXT;
    gendr TEXT;
    skill_row_count  INTEGER;
    usr_id INTEGER;
    skl_id INTEGER;
    intEmailExist INTEGER;
    expen DECIMAL;
BEGIN

    -- Getting name
    SELECT jsonobject->'Name' into usr_name;
    
    -- Getting education
    SELECT jsonobject->'Education' into edu;
    
    -- Getting phone number
    SELECT jsonobject->'phone_number' into phone;
    
    -- Getting experience
    SELECT (jsonobject->>'Exp')::DECIMAL INTO expen;
    
    --Getting college
    SELECT jsonobject->'College' into clg;
    
    
    -- Getting last employer
    SELECT jsonobject->'CurrentEmployer' into lstEmployer;
    
    --Getting gender
    SELECT jsonobject->'Gender' into gendr;
    
    -- Getting Email
    SELECT json_array_length(jsonobject->'Email') into intEmailExist;
    IF intEmailExist > 0 THEN 
        email:=array(SELECT json_array_elements_text(jsonobject->'Email'));
    END IF;
    
    -- Insert user details in 'Extractor_usr_details' table.
    INSERT INTO public."Extractor_usr_details"(name, phone_number, email, exp, education, college, "currentEmployer", gender)VALUES ( usr_name, phone, email, expen, edu, clg, lstEmployer, gendr) returning "id" into usr_id;
    
    skill_s := array(SELECT json_array_elements_text(jsonObject->'Skills'));
    FOREACH selectedSkill IN ARRAY skill_s LOOP
        INSERT INTO public."Extractor_skills" (skill) SELECT selectedSkill WHERE NOT EXISTS (SELECT id FROM public."Extractor_skills" WHERE skill = selectedSkill)RETURNING id into skl_id;
        INSERT INTO public."Extractor_usr_skills" (skill_id, user_id) SELECT skl_id, usr_id WHERE NOT EXISTS (SELECT id FROM public."Extractor_usr_skills" WHERE skill_id = skl_id AND user_id = usr_id);
    END LOOP;
    COMMIT;
END;
$BODY$;

and here is my python code to call the procedure..

   try:
   usrdetails = {'Name': 'Prashant Chandel', 'Education': 'B. Tech', 'Exp': 3.6, 'phone_number': '', 'College': '', 'CurrentEmployer': ['Tata Tele Services'], 'Gender': '', 'Skills': ['Spring boot', 'springioc', 'Css']}
        abc = json.dumps(usrdetails)
        print(type(abc))
        ps_connection = psycopg2.connect(user="postgres",
                                         password="demo",
                                         host="127.0.0.1",
                                         port="5432",
                                         database="postgres")

        cursor = ps_connection.cursor() 
        cursor.execute("call insert_usr_details_in_all_tables(?)" [abc])


    except (Exception) as err:
        print("\npsycopg2 connect error:", err)
        conn = None
        cur = None
11
  • I'm afraid psycopg2 is poorly equiped to handle json. Pass it as a string and let PostgreSQL convert it to json. – Gerard H. Pille Oct 20 '20 at 5:01
  • Ok. I will try to pass a string a parameter and then convert back to json, is this what you mean? – Mohammed zuhair Oct 20 '20 at 5:09
  • @GerardH.Pille It gives me same error for string type. how can i handle the error – Mohammed zuhair Oct 20 '20 at 5:32
  • Edit your question and show the full code. – Gerard H. Pille Oct 20 '20 at 6:50
  • @GerardH.Pille Question is updated can you please check. struggling since yesterday... i need some help badly – Mohammed zuhair Oct 20 '20 at 7:03
0

The argument to callproc needs to be a list of parameters. Therefore, if you pass in

abc=json.dumps(stringObject)
cursor.callproc('insert_usr_details_in_all_tables', abc)

It will split the string. Instead use:

cursor.callproc('insert_usr_details_in_all_tables', [abc,])
7
  • abc = json.dumps(jsonObject) cursor.callproc('insert_usr_details_in_all_tables', [abc,]) psycopg2 connect error: insert_usr_details_in_all_tables(unknown) is a procedure LINE 1: SELECT * FROM insert_usr_details_in_all_tables('{"Name": "Pr... HINT: To call a procedure, use CALL. – Mohammed zuhair Oct 20 '20 at 9:07
  • @Mohammedzuhair: it is not stated explicitly in the docs, but it seems that callproc, despite the name, only works with functions. Therefore, you'll have to execute on the cursor with e.g. execute('CALL ...', args). – Sebastian Dressler Oct 20 '20 at 9:19
  • I tried using cursor.execute("{call insert_usr_details_in_all_tables(?)}", [abc,]) // here i got error psycopg2 connect error: syntax error at or near "{" LINE 1: {call insert_usr_details_in_all_tables(?)} I removed the ',' in parameter brackets but still the same – Mohammed zuhair Oct 20 '20 at 9:23
  • @Mohammedzuhair: remove {} around, this is not SQL syntax and, the parameter must be denoted as $1, IIRC. – Sebastian Dressler Oct 20 '20 at 9:25
  • 1
    '$1' didn't worked for me instead I used '%s' which worked for me. This is update code to call the procedure 'cursor.execute("CALL insert_usr_details_in_all_tables ( %s )", [abc,])'..you helped me a lot.. thanks for your support.. – Mohammed zuhair Oct 21 '20 at 5:19
0

I prefer pg8000, because it knows how to handle bind variables.

A little example. Nothing gets inserted, no tables are used, just checking json and variable handling.

CREATE OR REPLACE PROCEDURE public.insert_subscriber (
    jsonobject inout json
)
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
    skill_s  Text[] := '{}';
    edu TEXT;
    email TEXT[] := '{}';
    phone TEXT;
    usr_name TEXT;
    clg TEXT;
    lstEmployer TEXT;
    gendr TEXT;
    intEmailExist INTEGER;
    expen DECIMAL;
BEGIN
    SELECT jsonobject->'Name',
           jsonobject->'Education',
           jsonobject->'phone_number',
           (jsonobject->>'Exp')::DECIMAL ,
           jsonobject->'College' ,
           jsonobject->'CurrentEmployer' ,
           jsonobject->'Gender' ,
           json_array_length(jsonobject->'Email'),
           ARRAY(select json_array_elements_text(jsonobject->'Email')),
           ARRAY(select json_array_elements_text(jsonObject->'Skills'))
      into usr_name,
           edu,
           phone,
           expen,
           clg,
           lstEmployer,
           gendr,
           intEmailExist,
           email,
           skill_s;

    jsonobject := to_json(email);

END;
$BODY$

Which is used in a python script as follows:

    #! /usr/bin/python3

    import pg8000
    import json

    con = pg8000.connect('ghp',password='yoEri')

    ps = con.prepare("call insert_subscriber(:v)")

    vv = {'Name': 'Prashant Chandel',
          'Education': 'B. Tech',
          'Exp': 3.6,
          'phone_number': '',
          'College': '',
          'CurrentEmployer': ['Tata Tele Services'],
          'Gender': '',
          'Email': ['ghp@goodl.uck','ghp@stradivari.us'],
          'Skills': ['Spring boot', 'springioc', 'Css']}

    vvv=json.dumps(vv);
    print(vvv)
    for row in ps.run(v=vvv):
      print(row)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.