search

aws / amazon-redshift-python-driver

Redshift Python Connector. It supports Python Database API Specification v2.0.
Apache License 2.0
204 stars 76 forks source link

cursor.callproc #54

Closed dburtsev closed 3 years ago

dburtsev commented 3 years ago

Driver version

2.0.866

Redshift version

PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.29551

Client Operating System

Win10 64bit

Python version

3.7

Table schema

CREATE OR REPLACE PROCEDURE stage.sp_truncate (tables IN VARCHAR(65535)) LANGUAGE PLPGSQL AS $plpgsql$ -- usage: CALL stage.sp_truncate ('bi_report.combined,bi_report.modifications,bi_report.msub_on_mprime,dev.test2'); DECLARE cnt INTEGER := 0; str TEXT; loop_var INTEGER := 0; tblname TEXT; schemaname TEXT; tablexists BOOLEAN := FALSE; BEGIN cnt := (SELECT regexp_count(tables, ',')) + 1; FOR loop_var IN 1..cnt LOOP str := (SPLIT_PART(tables,',', loop_var)); schemaname := SPLIT_PART(str, '.', 1); tblname := SPLIT_PART(str, '.', 2); tablexists = (SELECT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = schemaname AND table_name = tblname));

IF tablexists
  THEN EXECUTE 'TRUNCATE ' || str;
ELSE 
  RAISE EXCEPTION 'Table % not found', str;
END IF;

END LOOP; END; $plpgsql$;

Problem description

cursor.callproc('stage.sp_truncate', tables)

  1. Expected behaviour: run procedure
  2. Actual behaviour: error
  3. Error message/stack trace:
  4. Any other details that can be helpful:

Python Driver trace logs

Traceback (most recent call last): File "C:\Users\Dmitriy.Burtsev\AppData\Local\Programs\Python\Python37\lib\site-packages\redshift_connector\core.py", line 1119, in execute ps = cache["ps"][key] KeyError: ('CALL stage.sp_truncate($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28, $29, $30, $31, $32, $33, $34, $35, $36, $37, $38, $39, $40, $41, $42, $43, $44, $45, $46, $47, $48, $49, $50, $51, $52, $53, $54, $55, $56, $57, $58, $59, $60, $61, $62, $63, $64, $65, $66, $67, $68, $69, $70, $71, $72, $73, $74, $75, $76, $77, $78, $79, $80, $81, $82, $83, $84, $85, $86, $87, $88, $89, $90, $91, $92, $93, $94, $95, $96, $97, $98, $99, $100, $101, $102, $103, $104, $105, $106, $107, $108, $109, $110, $111, $112, $113, $114, $115, $116, $117, $118, $119, $120, $121, $122, $123, $124, $125, $126, $127, $128, $129, $130, $131, $132, $133, $134, $135, $136, $137, $138, $139, $140, $141, $142, $143, $144, $145, $146, $147, $148, $149, $150, $151, $152, $153, $154, $155, $156, $157, $158, $159, $160, $161, $162, $163, $164, $165, $166, $167, $168, $169, $170, $171, $172, $173, $174, $175, $176, $177, $178, $179, $180, $181, $182, $183, $184, $185, $186, $187, $188, $189, $190, $191, $192, $193, $194, $195, $196, $197, $198, $199, $200, $201, $202, $203, $204, $205, $206, $207, $208, $209, $210, $211, $212, $213, $214, $215, $216, $217, $218, $219, $220, $221, $222)', ((705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>), (705, 0, <function text_out at 0x0000029CCC9760D0>)))

During handling of the above exception, another exception occurred:

Traceback (most recent call last): File "C:\Work\Glue\ODS_RPT_COGNOS.py", line 37, in cursor.callproc('stage.sp_truncate', tables) File "C:\Users\Dmitriy.Burtsev\AppData\Local\Programs\Python\Python37\lib\site-packages\redshift_connector\cursor.py", line 249, in callproc self.execute(statement, vals) File "C:\Users\Dmitriy.Burtsev\AppData\Local\Programs\Python\Python37\lib\site-packages\redshift_connector\cursor.py", line 211, in execute self._c.execute(self, operation, args) File "C:\Users\Dmitriy.Burtsev\AppData\Local\Programs\Python\Python37\lib\site-packages\redshift_connector\core.py", line 1189, in execute self.handle_messages(cursor) File "C:\Users\Dmitriy.Burtsev\AppData\Local\Programs\Python\Python37\lib\site-packages\redshift_connector\core.py", line 1353, in handle_messages raise self.error redshift_connector.error.ProgrammingError: {'S': 'ERROR', 'C': '42883', 'M': 'procedure stage.sp_truncate with given arguments does not exist', 'F': '/home/ec2-user/padb/src/pg/src/backend/parser/parse_func.c', 'L': '1179', 'R': 'ParseFuncOrColumn'}

Reproduction code

conn = redshift_connector.connect(host=... conn.autocommit = True cursor: redshift_connector.Cursor = conn.cursor() tables = 'bi_report.combined,bi_report.modifications' cursor.callproc('stage.sp_truncate', tables)

Brooke-white commented 3 years ago

Hi @dburtsev ,

Thanks for reaching out. The team will work on reproducing this and follow up with our findings shortly.

Brooke-white commented 3 years ago

Hi @dburtsev ,

Thank you for your patience. Per the DB-API,

Call a stored database procedure with the given name. The sequence of parameters must contain one entry for each argument that the procedure expects. The result of the call is returned as modified copy of the input sequence. Input parameters are left untouched, output and input/output parameters replaced with possibly new values.

ref

It looks like the stored procedure stage.sp_truncate takes in one parameter, tables, which should look something like this: "table1, table2, table3".

Based on the error output provided I would guess callproc is being passed a list of strings, like shown below: cursor.callproc('sp_truncate', parameters=('test1', 'test2', 'test3',))

The error message is correct here, because stage.sp_truncate only expects one parameter. Modifying the parameters value like shown below should resolve this issue: cursor.callproc('sp_truncate', parameters=','.join(('test1', 'test2', 'test3')))

Please re-open this issue if you continue to experience issues. I was able to call this stored procedure without issue when using the modified approach above.