On Dec 21, 2010, at 6:59 AM, Diego Woitasen wrote:
> If I set expire_on_commit=False only the modified object will be written?
>
> I don't know if there is a better way to do this (short version):
>
> mail_items = mboxgroup_db.mail_item.filter(msg_filter).all() <--- this
> returns 636000 objects
> for mail_item in mail_items:
> do_something(mail_item)
> mail_item.volume_id = 3
> mboxgroup_db.commit()
>
> Is it possible to get an iterator querying with Sqlsoup?
you can use yield_per() which will cause the Query to return objects in batches:
http://www.sqlalchemy.org/docs/orm/query.html?highlight=yield_per#sqlalchemy.orm.query.Query.yield_per
you can also do several queries with a limit, or other filtering criterion:
i = 0
while True:
rows = object.limit(100).offset(i).all()
i = i + 100
if not rows:
break
for row in rows:
# etc
>
> Regards,
> Diego
>
> On Tue, Dec 21, 2010 at 3:29 AM, Michael Bayer <[email protected]>
> wrote:
> You've got 636000 objects in your session which is quite large. The commit
> would like to expire them when the transaction ends and in this case it's the
> sheer number of objects present taking up the time spent. You should try to
> keep present in a session only those objects which you are working with
> currently, and for large jobs to batch the current set of objects into groups
> of no more than a few thousand. Failing that, you can set
> expire_on_commit=False for the session which will prevent the expire step
> from occurring. Note that clean objects leave the session as soon as you
> dereference them everywhere else.
>
> Sent from my iPhone
>
> On Dec 20, 2010, at 10:37 PM, Diego Woitasen <[email protected]> wrote:
>
>>
>>
>> On Tue, Dec 21, 2010 at 12:35 AM, Diego Woitasen <[email protected]>
>> wrote:
>> Hi,
>> I have a python script that uses Sqlsoup to iterate over some rows of table
>> to modify them. The script is running really slow and I ran cProfile to see
>> where is the bottleneck. I've got this:
>>
>> 20549870 function calls (20523056 primitive calls) in 34.800 CPU
>> seconds
>>
>> Ordered by: cumulative time
>>
>> ncalls tottime percall cumtime percall filename:lineno(function)
>> 1 0.000 0.000 34.810 34.810 <string>:1(<module>)
>> 1 0.002 0.002 34.810 34.810 {execfile}
>> 1 0.095 0.095 34.808 34.808 zimbra-archive.py:34(<module>)
>> 20 0.000 0.000 25.618 1.281 sqlsoup.py:504(commit)
>> 20 0.000 0.000 25.618 1.281 scoping.py:128(do)
>> 20 0.000 0.000 25.618 1.281 session.py:631(commit)
>> 40/20 0.000 0.000 25.617 1.281 session.py:361(commit)
>> 20 1.262 0.063 25.565 1.278
>> session.py:290(_remove_snapshot)
>> 636600 20.002 0.000 23.254 0.000 state.py:220(expire_attributes)
>> 100 0.021 0.000 5.035 0.050 query.py:1447(all)
>> 32048 0.042 0.000 3.180 0.000 query.py:1619(instances)
>> 504 0.001 0.000 3.139 0.006 sqlsoup.py:549(__getattr__)
>> 504 0.003 0.000 3.138 0.006 sqlsoup.py:535(entity)
>>
>> The problem is commit, almost all the time used by the script is on commit.
>>
>> Is there a faster way to modify every row? Or Should i use raw sql?
>>
>> Regards,
>> Diego
>>
>> --
>> Diego Woitasen
>> XTECH
>>
>> I forgot the important lines of the code.
>>
>> mail_item.volume_id = archive_vol.id
>> mboxgroup_db.commit()
>>
>> That's the only modification that I'm doing. A simple query with that change.
>>
>>
>> Regards,
>> Diego
>>
>> --
>> Diego Woitasen
>> XTECH
>>
>> --
>> 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.
>
>
>
> --
> Diego Woitasen
> XTECH
>
> --
> 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.
