ProgrammingError while running SQL DDL/DML Statements

avneeshn commented 6 months ago

Driver version

2.0.913

Redshift version

PostgreSQL 8.0.2 on i686-pc-linux-gnu

Client Operating System

Linux

Python version

3.8

Table schema

Problem description

Expected behaviour: Redshift python driver should not throw any error for cursor.fetchall() while executing SQL DML statements.
Actual behaviour: Redshift python driver throws ProgrammingError for cursor.fetchall() while executing SQL DML statements. For eg: Run:
```
res_cursor.execute("create table sample_table_from_sm (col1 int);")
data = res_cursor.fetchall()
```

Output:

ERROR:c8e60ff5311f4a709f6ea83e092d3d4c:Error while executing query no result set
Traceback (most recent call last):
  File "/opt/conda/lib/python3.8/site-packages/redshift_connector/cursor.py", line 498, 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 "/opt/conda/lib/python3.8/site-packages/**********_sql_execution/redshift/connection.py", line 61, in execute
    data = res_cursor.fetchall()
  File "/opt/conda/lib/python3.8/site-packages/redshift_connector/cursor.py", line 456, in fetchall
    return tuple(self)
  File "/opt/conda/lib/python3.8/site-packages/redshift_connector/cursor.py", line 503, in __next__
    raise ProgrammingError("no result set")
redshift_connector.error.ProgrammingError: no result set
ERROR:root:Error executing SQL query: 
create table sample_table_from_sm (col1 int);

no result set

P.S. the same issue occurs for an UPDATE query.

Error message/stack trace: Attached above
Any other details that can be helpful:

Python Driver trace logs

Reproduction code

Brooke-white commented 6 months ago

Hi @avneeshn, are you familiar with the Cursor.rowcount property? I believe this may be what you're looking for. After executing a UPDATE or CREATE statement, Cusor.rowcount will provide the number of rows produced by the last call to execute(). Cursor.fetch__ methods return the result set returned from statement execution. Given CREATE and UPDATE statements do not produce a result set, raising an exception is the expected behavior per the DB-API spec

avneeshn commented 6 months ago

Thanks for the comment and quick-response @Brooke-white. So, we are developing an SQL client for our users to run queries. So we have no way of knowing in advance if it is a DML/DDL statement, or SELECT query. That is why we are using cursor.fetchall() everytime.

Regarding your approach regarding Cusor.rowcount, I believe this property will be non-zero for both an UPDATE query and a SELECT query. Do you know how can we use this property (or another approach) to identify whether a call to cursor.fetchall() needs to be made or not?

Brooke-white commented 6 months ago

I'd recommend adding try/catch logic around the call to fetchall(), where the catch statement catches redshift_connector.ProgrammingError

aws / amazon-redshift-python-driver