I've run into an issue with Redshift and I thought I would post it here
before logging a bug. If I run a query against a Redshift database table
that happens to return 0 rows I get an error when I try to iterate over the
result set. I run a pretty simple script that is looking for rows where a
timestamp field contains a date time that is greater than the last time the
query was run. Most of the time there are several thousand rows, but at
times there are no rows that have been updated and the query returns a
result set with no records.
It is simple enough to reproduce simply be generating a query that always
returns 0 records.
from sqlalchemy import create_engine
engine = create_engine(
'postgres+psycopg2://username:password@redshifthost:port/db_name')
with engine.connect().execute_options(stream_results=True) as conn:
results = conn.execute(query)
data = [r for r in results]
Traceback (most recent call last):
File
"/Users/dstovall/.virtualenvs/connectors/lib/python3.6/site-packages/sqlalchemy/engine/result.py",
line 1174, in fetchone
row = self._fetchone_impl()
File
"/Users/dstovall/.virtualenvs/connectors/lib/python3.6/site-packages/sqlalchemy/engine/result.py",
line 1296, in _fetchone_impl
self.__buffer_rows()
File
"/Users/dstovall/.virtualenvs/connectors/lib/python3.6/site-packages/sqlalchemy/engine/result.py",
line 1283, in __buffer_rows
self.__rowbuffer = collections.deque(self.cursor.fetchmany(size))
psycopg2.InternalError: opening multiple cursors from within the same
client connection is not allowed.
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "<input>", line 3, in <module>
File "<input>", line 3, in <listcomp>
File
"/Users/dstovall/.virtualenvs/connectors/lib/python3.6/site-packages/sqlalchemy/engine/result.py",
line 870, in __iter__
row = self.fetchone()
File
"/Users/dstovall/.virtualenvs/connectors/lib/python3.6/site-packages/sqlalchemy/engine/result.py",
line 1183, in fetchone
self.cursor, self.context)
File
"/Users/dstovall/.virtualenvs/connectors/lib/python3.6/site-packages/sqlalchemy/engine/base.py",
line 1402, in _handle_dbapi_exception
exc_info
File
"/Users/dstovall/.virtualenvs/connectors/lib/python3.6/site-packages/sqlalchemy/util/compat.py",
line 203, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=cause)
File
"/Users/dstovall/.virtualenvs/connectors/lib/python3.6/site-packages/sqlalchemy/util/compat.py",
line 186, in reraise
raise value.with_traceback(tb)
File
"/Users/dstovall/.virtualenvs/connectors/lib/python3.6/site-packages/sqlalchemy/engine/result.py",
line 1174, in fetchone
row = self._fetchone_impl()
File
"/Users/dstovall/.virtualenvs/connectors/lib/python3.6/site-packages/sqlalchemy/engine/result.py",
line 1296, in _fetchone_impl
self.__buffer_rows()
File
"/Users/dstovall/.virtualenvs/connectors/lib/python3.6/site-packages/sqlalchemy/engine/result.py",
line 1283, in __buffer_rows
self.__rowbuffer = collections.deque(self.cursor.fetchmany(size))
sqlalchemy.exc.InternalError: (psycopg2.InternalError) opening multiple
cursors from within the same client connection is not allowed.
I get the same problem using fetchone() or fetchmany() on the result set.
If I don't use .execute_options(stream_results=True) to create the
connection then I don't get the error. It seems that option somehow ends
up creating multiple server-side cursors when the are no records in the
result set. If any records are returned in the result set then I don't get
the error. Right now, as a work around I simply check the results.rowcount
attribute
before iterating through results, but I don't really like that work around.
Has anyone else encountered this problem with streamed results from
Redshift?
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
