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.
