Re: [sqlalchemy] Re: why this script lead to memory error?
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 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:passw...@127.0.0.1/
>> 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
>> 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 sqlalch...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> 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 sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: why this script lead to memory error?
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 ?
On Dec 10, 3:50 pm, Feng 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:passw...@127.0.0.1/
> 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
> 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 sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: func.sum
Thank you! I think I get it now, I was applying the func.sum to the wrong part. This is what I needed: qty = Session.query(model.Y).filter(model.Y.id_X==x.id).value(unc.sum (model.Y.change)) On Dec 10, 3:20 pm, Michael Bayer wrote: > On Dec 10, 2009, at 5:05 PM, dw wrote: > > > > > I'm trying to port my project from ver 0.4 to ver 0.5 and I can't seem > > to figure out how to implement the new sum functions. Here's a > > simplified version of my model: > > object X: > > id, > > description > > > object Y: (transactions of object X) > > id, > > id_X, > > change > > > (this is basically an inventory system) > > > To find out the current number of X's I have I was running this under > > 0.4: > > qty = Session.query(model.Y).filter(model.Y.id_X==x.id).sum > > (model.Y.change) > > > This returned a float of how many X's there were. With 0.5, the best > > I can do is: > > qty = Session.query(func.sum(model.Y.change)).filter > > (model.Y.id_X==x.id).all() > > > The problem here is it's returning a list containing a single tuple > > containing a decimal of my result. I'd like to get just the result > > without having to do something ugly like qty = qty[0][0] every time. > > > My SQL is pretty lacking so I'm sure I'm missing something. Any > > advice is much appreciated. Thanks. > > try query.value(func.sum(col)) - will return a scalar result. > > > > > -- > > > You received this message because you are subscribed to the Google Groups > > "sqlalchemy" group. > > To post to this group, send email to sqlalch...@googlegroups.com. > > To unsubscribe from this group, send email to > > sqlalchemy+unsubscr...@googlegroups.com. > > For more options, visit this group > > athttp://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 sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] func.sum
On Dec 10, 2009, at 5:05 PM, dw wrote: > I'm trying to port my project from ver 0.4 to ver 0.5 and I can't seem > to figure out how to implement the new sum functions. Here's a > simplified version of my model: > object X: > id, > description > > object Y: (transactions of object X) > id, > id_X, > change > > (this is basically an inventory system) > > To find out the current number of X's I have I was running this under > 0.4: > qty = Session.query(model.Y).filter(model.Y.id_X==x.id).sum > (model.Y.change) > > This returned a float of how many X's there were. With 0.5, the best > I can do is: > qty = Session.query(func.sum(model.Y.change)).filter > (model.Y.id_X==x.id).all() > > The problem here is it's returning a list containing a single tuple > containing a decimal of my result. I'd like to get just the result > without having to do something ugly like qty = qty[0][0] every time. > > My SQL is pretty lacking so I'm sure I'm missing something. Any > advice is much appreciated. Thanks. try query.value(func.sum(col)) - will return a scalar result. > > -- > > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalch...@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > 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 sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] func.sum
I'm trying to port my project from ver 0.4 to ver 0.5 and I can't seem to figure out how to implement the new sum functions. Here's a simplified version of my model: object X: id, description object Y: (transactions of object X) id, id_X, change (this is basically an inventory system) To find out the current number of X's I have I was running this under 0.4: qty = Session.query(model.Y).filter(model.Y.id_X==x.id).sum (model.Y.change) This returned a float of how many X's there were. With 0.5, the best I can do is: qty = Session.query(func.sum(model.Y.change)).filter (model.Y.id_X==x.id).all() The problem here is it's returning a list containing a single tuple containing a decimal of my result. I'd like to get just the result without having to do something ugly like qty = qty[0][0] every time. My SQL is pretty lacking so I'm sure I'm missing something. Any advice is much appreciated. Thanks. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] ** TO THE MODERATORS ** - are we accidentally letting these through ? or is this a google vulnerability
angrej atri wrote: > hi > how are you > > -- > > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalch...@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > 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 sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] why this script lead to memory error?
Feng wrote:
> Hi all, when I query into a big table, it lead to memory error.
MySQL-python fully buffers a result set into memory before providing it to
the client via fetchone(). Its a behavior of the DBAPI.
>
> Here is the small script which lead to the memory error:
> -
> engine = sqlalchemy.create_engine("mysql://user:passw...@127.0.0.1/
> 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
> 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 sqlalch...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> 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 sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Getting relation type from mapper
On Wed, Dec 9, 2009 at 10:11 AM, Sergey Koval wrote: > Hi, > > I'm trying to get relation type from the mapper, but having problems > detecting 'one to one' relation. > > Pseudo code: > for p in mapper.iterate_properties: > if isinstance(p, RelationProperty): > ... > > p.uselist is False, p.backref is available, but does not have any > information on it's type (should it use list or not). > p.direction is set to 'MANYTOONE'. > > How it can be accomplished? > > Thanks, > Serge. > > Interesting question. It looks like you can use the backref property to discover the other side of the relation via the backref.prop attribute. From there you should be able to find the the property on the other side by examining the mapper on that class and view it's uselist property also. I'm not sure how you would find the right relation if there were 2 relations between the classes. There might be a simpler approach, but it looks like this one should work. -- Mike Conley -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Getting relation type from mapper
hi how are you -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] clear_mappers does not really clear
Michael Bayer wrote:
> On Dec 9, 2009, at 6:56 PM, Peter wrote:
>
>
>> Hi
>>
>> I am sorry I misinterpreted the error message that I mentioned in my
>> previous mail:
>>
>> InvalidRequestError: Table 'accounts' is already defined for this
>> MetaData instance. Specify 'useexisting=True' to redefine options and
>> columns on an existing Table object.
>>
>> Apparently, the error is not raised by the mapper command , but by the
>> Table creation command :
>>
>> accounts_table = Table('accounts', metadata,
>> Column('guid', String(36),
>> autoload=False )
>>
>> So how do I get rid of the Table instances ( without dropping the tables
>> in the database ) ?
>>
>>
>> I tried this one:
>>
>> for table in metadata.sorted_tables:
>> metadata.remove(table)
>>
>> but same problem.
>>
>
> easiest is to start with a new MetaData object, or call clear() on the
> MetaData() you have. Surgically removing individual tables from a MetaData
> is not really supported since it would involve removing related foreign key
> information on other Table objects (and there's not much use for such a
> feature).
>
> --
>
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalch...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
>
>
Thanks a lot, I settled for the new-metadata-object and it works fine (
this is for independent unit tests )
Peter
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
