On Sep 19, 2012, at 8:53 AM, Ryan Kelly wrote:
> Is there a way to do the same thing that yield_per does for queries
> generated with Session.query but for queries performed using
> Session.execute (e.g. built with text() or select())? Is fetchmany the
> right function? I don't see it fetching forward on the cursor as I would
> expect.
>
> I'm using postgresql 9.1 and psycopg2, FWIW.
there's two sides to this. One is the Python API you use to fetch the rows.
When you run execute(), you get a ResultProxy back, which presents an interface
much like a DBAPI cursor (fetchone(), fetchmany(), fetchall()). On that side,
fetchmany() or fetchone() will do the job of fetching just some of the rows at
a time.
The other side is how the DBAPI itself buffers rows. These days a lot of them
buffer the entire result set in memory as soon as execute() is completed,
before any rows are fetched. psycopg2 is one of them, however it offers an
API to disable the buffering for some statements, and SQLAlchemy supports this
using an execution option.
Given a Session you'd do it like this:
result =
session.connection().execution_options(stream_results=True).execute(stmt)
while True:
rows = result.fetchmany(100)
if not rows:
break
stmt is any SQL string or expression, i.e. select(), text(), "select * from
table".
The yield_per() feature on Query usually does the stream_results=True part for
you. When using the psycopg2 dialect, SQLAlchemy interprets the
"stream_results" option as an instruction to use a "named cursor" with
psycopg2, which is how psycopg2 switches into "unbuffered row" mode.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
