The problem is fetchall(), is getting all the records to memory.
If you want to iterate a large dataset or not so heavy, but have a table
with many fields, perhaps you can do it by blocks.
For example using raw sql with dal: example: this fetch by 1000 records
queryraw = "select * from mytable %(limit_query)s"
def records(query):
rows = db.executesql(query)
if rows:
return True
else:
return False
rec = 0
stage = 0
block = 1000while True:
offset = stage * block
query = queryraw % {'limitquery': 'LIMIT %s, %s' % (offset, block)}
if not records(query):
break
rows = db.executesql(query)
for row in rows:
rec += 1
# do something
stage += 1
This is not fast but keep memory low.
2012/5/10 szimszon <szims...@gmail.com>
> Could it be related?
> https://groups.google.com/forum/#!topic/web2py/hmsupVHdDHo/discussion (Memory
> leak in standalone DAL (issue #731), can you please help test?)
>
> 2012. május 10., csütörtök 22:32:53 UTC+2 időpontban szimszon a következőt
> írta:
>
>> Okay. It's clear.
>>
>> I'm only puzzled about why the memory didn't get freed or reused after
>> execution is finished. And if I execute the controller function in 1-2min
>> interval mem is still not reused.
>>
>> So I understand it can eat up the memory but why is all memory locked
>> forever and didn't get reused - in my understanding (it's not much) in my
>> case GC do not free mem at all in python level. So some reference is still
>> intact after function is finished.
>>
>>
>> 2012. május 10., csütörtök 21:53:06 UTC+2 időpontban Richard a következőt
>> írta:
>>>
>>> Ok, you don't need it to works all the time.
>>>
>>> Did you get it to update your records?
>>>
>>> If not, and if as you said it is a one trip, you can just treat the
>>> whole records batch by batch...
>>>
>>> Look here : http://web2py.com/books/**default/chapter/29/14#**
>>> Populating-database-with-**dummy-data<http://web2py.com/books/default/chapter/29/14#Populating-database-with-dummy-data>
>>>
>>> for i in range(10):
>>>
>>> populate(db.mytable,100)
>>>
>>> db.commit()
>>>
>>>
>>> In this example only 100 records are populated at a time between
>>> db.commit()... So maybe you just have to wrap for loop that will
>>> db.commit() a couples of time during your processing.
>>>
>>> Richard
>>>
>>>
>>> On Thu, May 10, 2012 at 3:32 PM, szimszon <szims...@gmail.com> wrote:
>>>
>>>> I had to store files and a lot of properties for it. It was in csv. But
>>>> after I processed it we figured out that not all value was correct in csv
>>>> but it was a bit redundant. So I can correct it by go through all the
>>>> records row by row. So that was a one time trip.
>>>>
>>>> I just realized after the process I had no memory left. So now I'm
>>>> investigating what happened...
>>>>
>>>> 2012. május 10., csütörtök 21:00:05 UTC+2 időpontban Richard a
>>>> következőt írta:
>>>>>
>>>>> Yes but in this case it is not for the entire reecords...
>>>>>
>>>>> Why would you return a full list of all the records?
>>>>>
>>>>> I don't understand what is the purpose of listar that you return in
>>>>> the view under a html table, why do you need to return all the 100000+
>>>>> entries?
>>>>>
>>>>> Richard
>>>>>
>>>>> On Thu, May 10, 2012 at 2:56 PM, szimszon <szims...@gmail.com> wrote:
>>>>>
>>>>>> In book it is a recommended way to iterate over sql results:
>>>>>>
>>>>>> http://web2py.com/books/**defaul**t/chapter/29/6<http://web2py.com/books/default/chapter/29/6>
>>>>>>
>>>>>> You can do all the steps in one statement:
>>>>>>
>>>>>> 1.
>>>>>> 2.
>>>>>> 3.
>>>>>>
>>>>>> >>> for row in db(db.person.name=='Alex').sel****ect():
>>>>>>
>>>>>>
>>>>>> print row.name
>>>>>>
>>>>>> Alex
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> 2012. május 10., csütörtök 20:42:22 UTC+2 időpontban Bruce Wade a
>>>>>> következőt írta:
>>>>>>
>>>>>>> Sorry, you really need to read more about how python works. If you
>>>>>>> learn how for loops work and memory you will understand the problem.
>>>>>>>
>>>>>>> One solution do the query before the for loop then loop through the
>>>>>>> objects. This may help at bit. Research xrange vs range
>>>>>>>
>>>>>>>
>>>>>>> On Thu, May 10, 2012 at 11:30 AM, szimszon <szims...@gmail.com>wrote:
>>>>>>>
>>>>>>>> Sorry I don't understand. What do you mean "achieve with join"?
>>>>>>>>
>>>>>>>> There is an empty for loop with db.executesql() without join. And
>>>>>>>> it is eating up the memory. :(
>>>>>>>>
>>>>>>>> 2012. május 10., csütörtök 19:12:30 UTC+2 időpontban Richard a
>>>>>>>> következőt írta:
>>>>>>>>
>>>>>>>>> You can't manage what you want to achieve with join?
>>>>>>>>>
>>>>>>>>> Richard
>>>>>>>>>
>>>>>>>>> On Thu, May 10, 2012 at 10:48 AM, szimszon <szims...@gmail.com>wrote:
>>>>>>>>>
>>>>>>>>>> Sorry for my dumbness but if something is wrong with my code
>>>>>>>>>> please point me the right line. I'm not so good in English if it
>>>>>>>>>> comes to
>>>>>>>>>> "object instance count" and so. Yeah I know I should go and do some
>>>>>>>>>> milkmaid job :) but I'm curious.
>>>>>>>>>>
>>>>>>>>>> I'm just define some variable:
>>>>>>>>>>
>>>>>>>>>> lista = list()
>>>>>>>>>> last_row = None
>>>>>>>>>> next_page_number = 0
>>>>>>>>>>
>>>>>>>>>> Go in a for loop that just assign the db query result one-by-one
>>>>>>>>>> to row variable.
>>>>>>>>>>
>>>>>>>>>> After that I assign the TABLE() helper to a list variable. That
>>>>>>>>>> is it.
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> 2012. május 10., csütörtök 16:34:49 UTC+2 időpontban Bruce Wade a
>>>>>>>>>> következőt írta:
>>>>>>>>>>
>>>>>>>>>>> Using direct sql query or DAL is going to cause the exact same
>>>>>>>>>>> problem in this situation.
>>>>>>>>>>>
>>>>>>>>>>> On Thu, May 10, 2012 at 7:28 AM, szimszon <szims...@gmail.com>wrote:
>>>>>>>>>>>
>>>>>>>>>>>> It's postgres:// in a
>>>>>>>>>>>> Version 1.99.7 (2012-04-23 11:26:23) dev of web2py, and
>>>>>>>>>>>>
>>>>>>>>>>>> Python 2.7.3 (default, Apr 20 2012, 22:44:07)
>>>>>>>>>>>> [GCC 4.6.3] on linux2
>>>>>>>>>>>>
>>>>>>>>>>>> python-psycopg2 2.4.5-1
>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>> 2012. május 10., csütörtök 15:40:36 UTC+2 időpontban
>>>>>>>>>>>> rochacbruno a következőt írta:
>>>>>>>>>>>>
>>>>>>>>>>>>> Just for curiosity, what happens if you do it in pure sql?
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>> for row in db.executesql("**sele**********ct * from
>>>>>>>>>>>>> file_properties where id > 0"):
>>>>>>>>>>>>>
>>>>>>>>>>>>> # do something
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>> Does it have a lower memory usage?
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>> On Thu, May 10, 2012 at 4:14 AM, Bruce Wade
>>>>>>>>>>>>> <<bruce.w...@gmail.com>
>>>>>>>>>>>>> > wrote:
>>>>>>>>>>>>>
>>>>>>>>>>>>>> for row in db( db.file_**propert**********ies.id > 0 ).select
>>>>>>>>>>>>>> (
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>> --
>>>>>>>>>>>>>
>>>>>>>>>>>>> Bruno Rocha
>>>>>>>>>>>>> [http://rochacbruno.com.br]
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>> --
>>>>>>>>>>> --
>>>>>>>>>>> Regards,
>>>>>>>>>>> Bruce Wade
>>>>>>>>>>> http://ca.linkedin.com/in/**bruc********elwade<http://ca.linkedin.com/in/brucelwade>
>>>>>>>>>>> http://www.wadecybertech.com
>>>>>>>>>>> http://www.fittraineronline.**co********m<http://www.fittraineronline.com>-
>>>>>>>>>>> Fitness Personal Trainers Online
>>>>>>>>>>> http://www.warplydesigned.com
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> --
>>>>>>> --
>>>>>>> Regards,
>>>>>>> Bruce Wade
>>>>>>> http://ca.linkedin.com/in/**bruc****elwade<http://ca.linkedin.com/in/brucelwade>
>>>>>>> http://www.wadecybertech.com
>>>>>>> http://www.fittraineronline.**co****m<http://www.fittraineronline.com>-
>>>>>>> Fitness Personal Trainers Online
>>>>>>> http://www.warplydesigned.com
>>>>>>>
>>>>>>>
>>>>>
>>>
--
http://www.tecnodoc.com.ar
