[Pyramid] Disable SQLAlchemy Transaction

[jerry]

It seems transaction based SQLAlchemy session is defined by the
ZopeTransactionExtension --

DBSession =
scoped_session(sessionmaker(extension=ZopeTransactionExtension()))

This is causing problem with my long-running/large-volume paster
command, which are not suitable for transactions. But I couldn't get
the DBSession redefined no matter how I fiddle with the paster command
source. Is there a way to temporarily turn off transaction in a
Pyramid project?

Thanks in advance!

Jerry

[Michael Merickel]

Nothing is stopping you from defining another session object bound to the same engine that does not use the ZTE. As long as your engine is accessible through the settings/registry then it shouldn't be an issue to create a session in your script and use it only there.

Michael

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

[jerry]

Hi Michael,

Thanks for the reply, but even if I remove ZTE in my models.py --

DBSession = scoped_session(sessionmaker())

Everything is still wrapped in one big transaction --

-> for item in items_query.all():
(Pdb)
2011-05-13 08:23:54,109 INFO sqlalchemy.engine.base.Engine.0x...68cc
BEGIN (implicit)
2011-05-13 08:23:54,122 INFO sqlalchemy.engine.base.Engine.0x...68cc
SELECT ...

If I have 2 million items it's not going to be a robust design to
commit only once in the end after updating 1 million of them.

Is there a way to explicitly turn off the transaction (at least for
SELECT)?

Jerry

On May 12, 11:27 pm, Michael Merickel <mich...@merickel.org> wrote:
> Nothing is stopping you from defining another session object bound to the
> same engine that does not use the ZTE. As long as your engine is accessible
> through the settings/registry then it shouldn't be an issue to create a
> session in your script and use it only there.
>
> Michael
>

[Chris Withers]

Hi Jerry,

On 13/05/2011 01:31, jerry wrote:
> Everything is still wrapped in one big transaction --
>
> -> for item in items_query.all():
> (Pdb)
> 2011-05-13 08:23:54,109 INFO sqlalchemy.engine.base.Engine.0x...68cc
> BEGIN (implicit)
> 2011-05-13 08:23:54,122 INFO sqlalchemy.engine.base.Engine.0x...68cc
> SELECT ...

I don't think this is anything to do with Pyramid.

Read the docs for sessionmaker, particularly those about autocommit:
http://www.sqlalchemy.org/docs/orm/session.html#sqlalchemy.orm.session.sessionmaker

So, you have a transaction created for you, but you can manage it yourself:

session = DBSession()
session.bind = ...your engine...

for item in items_query.all():
try:
...do stuff...
except:
session.abort()
else:
session.commit()

Now, personally, I like the ZopeTransactionExtension 'cos it lets me
spell the above in a nicer way and synchronise transactions across
things like sending mail, writing files to disk, etc.

I also like http://packages.python.org/mortar_rdb/use.html, where the
pattern would become:

from mortar_rdb import registerSession,getSession
import transaction

registerSession('sqlite://')

session = getSession()
for item in session.query(...).all():
with transaction:
...do stuff...

cheers,

Chris

--
Simplistix - Content Management, Batch Processing & Python Consulting
- http://www.simplistix.co.uk

[jerry]

Thanks Chris.

I finally got (hopefully is) what I want with --

DBSession = sessionmaker()
dbsession = DBSession()
for item in items_query.all():
dbsession.begin_nested()
item.name = 'foobar'
try:
dbsession.commit()
except:
dbsession.rollback()
dbsession.commit()

What I'm not so sure is whether (PostgreSQL) savepoint is designed to
support millions of iteration before the grand final commit.

Jerry

On May 13, 2:06 pm, Chris Withers <ch...@simplistix.co.uk> wrote:
> Hi Jerry,
>
> On 13/05/2011 01:31, jerry wrote:
>
> > Everything is still wrapped in one big transaction --
>
> > ->  for item in items_query.all():
> > (Pdb)
> > 2011-05-13 08:23:54,109 INFO sqlalchemy.engine.base.Engine.0x...68cc
> > BEGIN (implicit)
> > 2011-05-13 08:23:54,122 INFO sqlalchemy.engine.base.Engine.0x...68cc
> > SELECT ...
>
> I don't think this is anything to do with Pyramid.
>

> Read the docs for sessionmaker, particularly those about autocommit:http://www.sqlalchemy.org/docs/orm/session.html#sqlalchemy.orm.sessio...

>
> So, you have a transaction created for you, but you can manage it yourself:
>
> session = DBSession()
> session.bind = ...your engine...
>
> for item in items_query.all():
>     try:
>         ...do stuff...
>     except:
>         session.abort()
>     else:
>         session.commit()
>
> Now, personally, I like the ZopeTransactionExtension 'cos it lets me
> spell the above in a nicer way and synchronise transactions across
> things like sending mail, writing files to disk, etc.
>

> I also likehttp://packages.python.org/mortar_rdb/use.html, where the