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.
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 <javascript:>>
> 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 <javascript:>.
> > To post to this group, send email to sqlal...@googlegroups.com
> <javascript:>.
> > 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.
