psycopg2 連接錯誤：不能將超過 100 個參數傳遞給函式？

我正在嘗試將 json 對像作為參數傳遞給我的儲存過程。我嘗試了很多事情並最終解決了幾個錯誤我遇到了錯誤： 錯誤：psycopg2 連接錯誤：不能將超過 100 個參數傳遞給函式第 1 行：SELECT * FROM insert_usr_details_in_all_tables(’{’,’"’,‘N ‘,’… 我嘗試了 json.dumps、json.loads 等，但不幸的是，如果我從具有相同輸入的腳本中執行 postgresql，它對我沒有任何作用。下面是我的儲存過程..

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$;

這是我呼叫該過程的python程式碼..

  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

to 的參數callproc必須是參數列表。因此，如果您通過

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

它將拆分字元串。而是使用：

cursor.callproc('insert_usr_details_in_all_tables', [abc,])

我更喜歡 pg8000，因為它知道如何處理綁定變數。

一個小例子。什麼都沒有插入，沒有使用表，只是檢查 json 和變數處理。

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$

在python腳本中使用如下：

   #! /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)

引用自：https://dba.stackexchange.com/questions/278333