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

Question

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.

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 — Joe
– Joe, Commented Aug 10, 2018 at 11:01
cursor.callproc('external."spAppeUpdatetTimes"', (record[2], )) — Joe
– Joe, Commented Aug 11, 2018 at 20:27
You should have a button titled something like 'Answer your own question'. — Keith John Hutchison
– Keith John Hutchison, Commented Aug 12, 2018 at 0:09

Joe · Accepted Answer · 2018-08-12 09:38:49Z

1

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

answered Aug 12, 2018 at 9:38

Joe

215 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Keith John Hutchison · Accepted Answer · 2018-08-09 22:51:21Z

0

Try explicitly joining the data.

Something like

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

answered Aug 9, 2018 at 22:51

Keith John Hutchison

5,34711 gold badges49 silver badges66 bronze badges

1 Comment

Joe Over a year ago

Set the data type of the text in the function, but nothing changes.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related