mysql + query.execute memory usage

[James Casbon]

Hi,

I'm using sqlalchemy to generate a query that returns lots of data.
The trouble is, when calling "query.execute()" instead of returning
the resultproxy straight away and allowing me to fetch data as I would
like, query.execute blocks and the memory usage grows to gigabytes
before getting killed for too much memory. This looks to me like
execute is prefetching the entire result.

Is there any way to prevent query.execute loading the entire result?

Thanks,
James

[Michael Bayer]

for ORM look into using yield_per() or applying limit()/offset(). without the ORM no rows are buffered on the SQLA side. Note however that MySQLdb is likely prefetching the entire result set in any case (this is psycopg2s behavior but haven't confirmed for MySQLdb).

[James Casbon]

Thanks, but not using the ORM.

Looks like you have to specify a server side cursor - see SSCursor in
http://mysql-python.sourceforge.net/MySQLdb.html

I don't recall any way of forcing sqlalchemy to use a particular
cursor?

James

[Michael Bayer]

Here's a comment from Jason Kirtland on this feature:

http://www.sqlalchemy.org/trac/ticket/1089

AFAIK mysqldb (as of 1.2.2 anyhow) doesn't support mysql server side
cursors. it's SSCursor fetches rows in 'use_result' mode, which basically
just directly streams out the query results on demand, holding locks on
the tables for the entire duration. it's IMHO nigh useless if you have
multiple database readers. mysql has real server side cursors that
materialize as temporary tables and hold no locks, but mysqldb doesn't use
them.

ticket #1619 discusses an enhancement to select(), query() and text() that
would instruct SQLA to use "streaming results" as available for that
particular execution. right now the feature is only available on the PG
dialect as an "always on" feature.


>
> James
>
> --
>
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=.
>
>
>