Search code examples
pythonsqlprocedures

An error occurred when passing a string argument to a sql-procedure (postgreSQL) using python psycopg

I’m trying to create a SQL-procedure spAppeUpdatetTimes() using SQL which accepts a string argument of the UPC item on the input. But it does not work correctly. At the output, it occurs that the procedure takes as an input not a solid string but several arguments. It is because the UPC value of the item becomes splited (divided) into individual characters. What should I do to avoid this bug?

CREATE OR REPLACE FUNCTION external.spAppeUpdatetTimes(
upc character varying)

  RETURNS void
  LANGUAGE 'sql'

  COST 100
  VOLATILE 
AS $BODY$

    update external."tbProducts" as pu
            set "eBayDiscontinued"= current_timestamp
    from external."tbProducts" as ps 
    where pu."eBayUPC"=upc;
    end;

  $BODY$;

  ALTER FUNCTION external.spappeupdatettimes(character varying)
       OWNER TO postgreadmin;

Calling the procedure in Python:

 cursor.callproc('external."spAppeUpdatetTimes"', (record[2]))

The table that contains data:

CREATE TABLE external."tbProducts"
("UPC" character varying(255) COLLATE pg_catalog."default",
    CONSTRAINT "tbProducts_pkey" PRIMARY KEY ("CWRPartNumber"))

However, when I try to use the function, I get the following error:

ProgrammingError('function external.spAppeUpdatetTimes(unknown, 
unknown, unknown, unknown, unknown, unknown) does not exist\nLINE 1: 
SELECT * FROM external."spAppeUpdatetTimes" 
 (\'1\',\'9\',\'3\',\'0\',...\n                      ^\nHINT:  No 
  function matches the given name and argument types. You might need 
  to add explicit type casts.
Solution

  • The problem is solved. When doing a procedure call in python code, the arguments 'callproc' must be tuple or list. mysqltutorial.org/calling-mysql-stored-procedures-python.

    cursor.callproc('external."spAppeUpdatetTimes"', (record[2], ))