On Wed, Sep 6, 2017 at 2:42 PM, <d...@numetric.com> wrote: > Yes, it is a select query. I tried to re-create this just using psycopg2 > and named cursors and didn't get an error so I thought it had to be > somewhere in sqlalchemy > > import psycopg2 > from datetime import datetime > conn_str = 'host=redshift_host port=redshift_port username=username > password=password' > query = 'SELECT * FROM test WHERE timestamp > %s' > params = (datetime(2020, 12, 31, 0, 0),) > with psycopg2.connect(conn_str) as conn: > with conn.cursor(name='test_cursor') as cur: > cur.itersize=1000 # default is 2000, but the server I connected to > only allowed 1000 > cur.execute(query, params) > results = [r for r in cur] > > > I tried connecting to the same server and running the same query. No error > is returned and I end up with an results as an empty list.
where's the fetchmany() call ? > > > On Wednesday, September 6, 2017 at 12:27:13 PM UTC-6, Mike Bayer wrote: >> >> On Wed, Sep 6, 2017 at 12:21 PM, <d...@numetric.com> wrote: >> > 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. >> >> is it a SELECT query? >> >> I don't see anything on the SQLAlchemy side that's doing the wrong >> thing here, the server-side cursors option just means we say >> cursor.fetchmany(<num>) one or more times to pre-buffer rows from the >> cursor. We do not "open" a second server side cursor. psycopg2 is >> reporting the error at the moment the code is calling fetchmany(), >> which does not advertise as opening a new cursor. >> >> >> > >> > 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+...@googlegroups.com. >> > To post to this group, send email to sqlal...@googlegroups.com. >> > Visit this group at https://groups.google.com/group/sqlalchemy. >> > For more options, visit https://groups.google.com/d/optout. > > -- > 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. -- 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.