On Dec 10, 2009, at 8:16 PM, Feng wrote: > Thanks Alex and Michael. :) > > I tried to use all_records = session.query(Variation).yield_per > (1000) , > > but it still lead to the memory error, so it seems yield_per is not > working with MySQLdb ?
When you emit the SELECT statement to the database via MySQLdb, MySQLdb fetches all rows into memory, before SQLAlchemy has any chance to do anything with them. yield_per() doesn't help here, since yield_per() only limits how many ORM objects are generated at once - it doesn't break your query into smaller queries that request less rows from the database. The only option you have here, unless you want to try out SQLA 0.6 with the "OurSQL" dialect (all very new and alpha), is to apply LIMIT and OFFSET to your query to fetch smaller numbers of rows at a time. > > > On Dec 10, 3:50 pm, Feng <[email protected]> wrote: >> Hi all, when I query into a big table, it lead to memory error. >> >> Here is the small script which lead to the memory error: >> --------------------------------------------------------- >> engine = sqlalchemy.create_engine("mysql://user:[email protected]/ >> homo_sapiens_variation_56_37a") >> meta = sqlalchemy.MetaData() >> meta.bind = engine >> Session = sessionmaker(bind = engine) >> table_variation = sqlalchemy.Table('variation', meta, autoload=True) >> class Variation(object): pass >> mapper(Variation, table_variation) >> session = Session() >> al_records = session.query(Variation) >> for record in al_records: #there are 18 million records in the table >> variation >> pass >> >> --------------------------------------------------------- >> And here are the error information. I am using the SQLAlchemy of 0.56, >> python 2.5, and OS X. Is it a bug of SQLAlchemy? Could anyone please >> help me out? Thanks. >> >> Python(43867) malloc: *** mmap(size=262144) failed (error code=12) >> *** error: can't allocate region >> *** set a breakpoint in malloc_error_break to debug >> ... >> Python(43867) malloc: *** mmap(size=262144) failed (error code=12) >> *** error: can't allocate region >> *** set a breakpoint in malloc_error_break to debug >> Python(43867) malloc: *** mmap(size=2097152) failed (error code=12) >> *** error: can't allocate region >> *** set a breakpoint in malloc_error_break to debug >> Traceback (most recent call last): >> File "test_memory_leak.py", line 28, in <module> >> for record in al_records: # load all 18 million records into the >> memory >> File "/Library/Python/2.5/site-packages/SQLAlchemy-0.5.6-py2.5.egg/ >> sqlalchemy/orm/query.py", line 1361, in __iter__ >> return self._execute_and_instances(context) >> File "/Library/Python/2.5/site-packages/SQLAlchemy-0.5.6-py2.5.egg/ >> sqlalchemy/orm/query.py", line 1364, in _execute_and_instances >> result = self.session.execute(querycontext.statement, >> params=self._params, mapper=self._mapper_zero_or_none()) >> File "/Library/Python/2.5/site-packages/SQLAlchemy-0.5.6-py2.5.egg/ >> sqlalchemy/orm/session.py", line 755, in execute >> clause, params or {}) >> File "/Library/Python/2.5/site-packages/SQLAlchemy-0.5.6-py2.5.egg/ >> sqlalchemy/engine/base.py", line 824, in execute >> return Connection.executors[c](self, object, multiparams, params) >> File "/Library/Python/2.5/site-packages/SQLAlchemy-0.5.6-py2.5.egg/ >> sqlalchemy/engine/base.py", line 874, in _execute_clauseelement >> return self.__execute_context(context) >> File "/Library/Python/2.5/site-packages/SQLAlchemy-0.5.6-py2.5.egg/ >> sqlalchemy/engine/base.py", line 896, in __execute_context >> self._cursor_execute(context.cursor, context.statement, >> context.parameters[0], context=context) >> File "/Library/Python/2.5/site-packages/SQLAlchemy-0.5.6-py2.5.egg/ >> sqlalchemy/engine/base.py", line 948, in _cursor_execute >> self.dialect.do_execute(cursor, statement, parameters, >> context=context) >> File "/Library/Python/2.5/site-packages/SQLAlchemy-0.5.6-py2.5.egg/ >> sqlalchemy/engine/default.py", line 123, in do_execute >> cursor.execute(statement, parameters) >> File "build/bdist.macosx-10.5-i386/egg/MySQLdb/cursors.py", line >> 173, in execute >> File "build/bdist.macosx-10.5-i386/egg/MySQLdb/connections.py", line >> 36, in defaulterrorhandler >> MemoryError > > -- > > 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. > > -- 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.
