Postgresql – psycopg2 connect error: cannot pass more than 100 arguments to a function

jsonpostgresqlpythonstored-procedures

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

Best Answer

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,])
Related Question