[question] - How to get write operation details back

[andormarkus]

Hi All,

When I run a INSERT / DELETE / COPY operation in my IDE I get back the number of row affected. (see picture below)

How can I retrieve the same information with this driver as well?

Thanks,
Andor

[Brooke-white]

Hey @andormarkus , you should be able to retrieve this information,2515, in the example you provided, using cursor.fetchone() method following executing the copy command like cursor.execute("COPY ...") . For instructions on how to get and use a cursor object for the current connection, please see our API docs or connection tutorials.

[andormarkus]

Hi @Brooke-white

I tried to execute the following command and I got the following error.

sql = """
COPY "stg"."dim_wfm" 
FROM 's3://bucket' 
IAM_ROLE '<arn>' 
CSV DATEFORMAT AS 'YYYY-MM-DD HH:MI:SS' 
TIMEFORMAT AS 'YYYY-MM-DD HH:MI:SS' 
IGNOREHEADER 1; 
"""

redshift = helper.RedshiftHandler(secret_name=args.RedshiftSecret).connect()
redshift.cursor.execute(sql)
resp = redshift.cursor.fetchone()
print(resp)

2022-09-27 20:09:23 INFO     Getting Connection Info
2022-09-27 20:09:23 INFO     Secret is: REDSHIFT_SECRET
2022-09-27 20:09:23 INFO     Found credentials from IAM Role: SSMInstanceProfile
2022-09-27 20:09:23 INFO     Connecting to Redshift: redshift.amazonaws.com
2022-09-27 20:09:23 INFO     Successfully Connected to Cluster
Traceback (most recent call last):
  File "/home/ec2-user/.virtualenvs/lib/python3.9/site-packages/redshift_connector/cursor.py", line 487, in __next__
    return self._cached_rows.popleft()
IndexError: pop from an empty deque

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/tmp/pycharm_project_230/glue_jobs/etl/extract_test.py", line 48, in <module>
    resp = redshift.cursor.fetchone()
  File "/home/ec2-user/.virtualenvs/lib/python3.9/site-packages/redshift_connector/cursor.py", line 402, in fetchone
    return next(self)
  File "/home/ec2-user/.virtualenvs/lib/python3.9/site-packages/redshift_connector/cursor.py", line 492, in __next__
    raise ProgrammingError("no result set")
redshift_connector.error.ProgrammingError: no result set

Process finished with exit code 1