Streaming results with Blaze and SqlAlchemy
<p>I am trying to use Blaze/Odo to read a large (~70M rows) result set from Redshift. By default SqlAlchemy witll try to read the whole result into memory, before starting to process it. This can be prevented by either 
<code>execution_options(stream_results=True)</code> on the engine/session or <code>yield_per(sane_number)</code> on the query. When working from Blaze SqlAchemy queries are generated behind the covers, leaving the <code>execution_options</code> approach. Unfortunately the following throws and error.</p>

<pre><code>from sqlalchemy import create_engine
from blaze import Data

redshift_params = (redshift_user, redshift_pass, redshift_endpoint, port, dbname)
engine_string = "redshift+psycopg2://%s:%s@%s:%d/%s" % redshift_params

engine = create_engine(engine_string,
       execution_options=dict(stream_results=True)
       )

db = Data(engine)
</code></pre>

<p>The exception is:</p>

<pre><code>...
/home/mahler/anaconda/lib/python2.7/site-packages/sqlalchemy/engine/result.pyc in __buffer_rows(self)
   1124             return
   1125         size = getattr(self, '_bufsize', 1)
-&gt; 1126         self.__rowbuffer = collections.deque(self.cursor.fetchmany(size))
   1127         self._bufsize = self.size_growth.get(size, size)
   1128         if self._max_row_buffer is not None:

InternalError: (psycopg2.InternalError) opening multiple cursors from within the same client connection is not allowed.
</code></pre>

<p>If I leave out the <code>execution_options=dict(stream_results=True)</code> then the above works, but doing something like</p>

<pre><code>odo(db.mytable, 'mytable.bcolz')
</code></pre>

<p>will run out of memory for large tables.</p>

<p>Using <code>execution_options(stream_results=True)</code> does work with <code>pandas.read_csv</code>. The following code works fine, using only moderate amounts of memory:</p>

<pre><code>from sqlalchemy import create_engine
import pandas as pd

redshift_params = (redshift_user, redshift_pass, redshift_endpoint, port, dbname)
engine_string = "postgresql+psycopg2://%s:%s@%s:%d/%s" % redshift_params

engine = create_engine(engine_string,
       execution_options=dict(stream_results=True)
       )
compression='bz2'
res = pd.read_sql_query(queryString
            engine,
            chunksize=2**20)
for i, df in enumerate(res):
    df.to_csv('results-%s.csv.%s' % (i, compression), compression=compression)
</code></pre>

<hr>

<p>This is the complete stack trace:</p>

<pre><code>...

Data(engine)

No handlers could be found for logger "sqlalchemy.pool.QueuePool"
Traceback (most recent call last):
  File "&lt;stdin&gt;", line 1, in &lt;module&gt;
  File "/home/mahler/anaconda/lib/python2.7/site-packages/blaze/interactive.py", line 122, in Data
    dshape = discover(data)
  File "/home/mahler/anaconda/lib/python2.7/site-packages/multipledispatch/dispatcher.py", line 164, in __call__
    return func(*args, **kwargs)
  File "/home/mahler/anaconda/lib/python2.7/site-packages/odo/backends/sql.py", line 242, in discover
    return discover(metadata)
  File "/home/mahler/anaconda/lib/python2.7/site-packages/multipledispatch/dispatcher.py", line 164, in __call__
    return func(*args, **kwargs)
  File "/home/mahler/anaconda/lib/python2.7/site-packages/odo/backends/sql.py", line 248, in discover
    metadata.reflect(views=metadata.bind.dialect.supports_views)
  File "/home/mahler/anaconda/lib/python2.7/site-packages/sqlalchemy/sql/schema.py", line 3623, in reflect
    bind.dialect.get_view_names(conn, schema)
  File "&lt;string&gt;", line 2, in get_view_names
  File "/home/mahler/anaconda/lib/python2.7/site-packages/sqlalchemy/engine/reflection.py", line 42, in cache
    return fn(self, con, *args, **kw)
  File "/home/mahler/anaconda/lib/python2.7/site-packages/sqlalchemy/dialects/postgresql/base.py", line 2347, in get_view_names
    for row in connection.execute(s)]
  File "/home/mahler/anaconda/lib/python2.7/site-packages/sqlalchemy/engine/result.py", line 713, in __iter__
    row = self.fetchone()
  File "/home/mahler/anaconda/lib/python2.7/site-packages/sqlalchemy/engine/result.py", line 1026, in fetchone
    self.cursor, self.context)
  File "/home/mahler/anaconda/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1341, in _handle_dbapi_exception
    exc_info
  File "/home/mahler/anaconda/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 200, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/home/mahler/anaconda/lib/python2.7/site-packages/sqlalchemy/engine/result.py", line 1017, in fetchone
    row = self._fetchone_impl()
  File "/home/mahler/anaconda/lib/python2.7/site-packages/sqlalchemy/engine/result.py", line 1139, in _fetchone_impl
    self.__buffer_rows()
  File "/home/mahler/anaconda/lib/python2.7/site-packages/sqlalchemy/engine/result.py", line 1126, 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.
</code></pre>