SQLAlchemy / Turbogears2.0 transaction rollbacks

[ozwyzard]

Hello,

I am trying to use Turbogears 2.x with SQLAlchemy 0.5.1.

TG2 uses scoped sessions and uses an additional layer of transaction
manager. If I have a scenario as follows:

import transaction

def main()
try:
add main_record to session
query main_record to get primary key
call foo()
except Exception, e:
transaction.abort()

def foo(pid):
add dependent record with main_record_primary_key as foreign_key
raise exception for testing


The above code is not rolling back the transaction. I realize the
'query main_record' will flush the main_record to the DB. But I am
wondering if it is also 'commiting' the record. How do I debug this?
Is there a debug flag in sqlalchemy which logs a commit operation?


The associated initialization code in TG2 is:

from zope.sqlalchemy import ZopeTransactionExtension
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy.ext.declarative import declarative_base

# Global session manager: DBSession() returns the Thread-local
# session object appropriate for the current web request.
maker = sessionmaker(autoflush=True, autocommit=False,
extension=ZopeTransactionExtension())
DBSession = scoped_session(maker)
metadata = DeclarativeBase.metadata


The link to the thread on turbogears group is:

http://groups.google.com/group/turbogears/browse_thread/thread/363c2e5ac7211a8


Thanks!

[Michael Bayer]

On Sep 9, 2010, at 3:51 PM, ozwyzard wrote:

> Hello,
>
> I am trying to use Turbogears 2.x with SQLAlchemy 0.5.1.
>
> TG2 uses scoped sessions and uses an additional layer of transaction
> manager. If I have a scenario as follows:
>
> import transaction
>
> def main()
> try:
> add main_record to session
> query main_record to get primary key
> call foo()
> except Exception, e:
> transaction.abort()
>
> def foo(pid):
> add dependent record with main_record_primary_key as foreign_key
> raise exception for testing
>
>
> The above code is not rolling back the transaction. I realize the
> 'query main_record' will flush the main_record to the DB. But I am
> wondering if it is also 'commiting' the record. How do I debug this?
> Is there a debug flag in sqlalchemy which logs a commit operation?

the regular engine logging will show the string "COMMIT" and "ROLLBACK" in the logs. See the "Engine" documentation for how to use the "echo" flag as well as more comprehensive logging.

>
>
> The associated initialization code in TG2 is:
>
> from zope.sqlalchemy import ZopeTransactionExtension
> from sqlalchemy.orm import scoped_session, sessionmaker
> from sqlalchemy.ext.declarative import declarative_base
>
> # Global session manager: DBSession() returns the Thread-local
> # session object appropriate for the current web request.
> maker = sessionmaker(autoflush=True, autocommit=False,
> extension=ZopeTransactionExtension())
> DBSession = scoped_session(maker)
> metadata = DeclarativeBase.metadata
>
>
> The link to the thread on turbogears group is:
>
> http://groups.google.com/group/turbogears/browse_thread/thread/363c2e5ac7211a8
>
>
> Thanks!
>

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

[ozwyzard]

Thanks for the info. I was able to get similar logging using a debug
flag in TG 2.x config file. Will update if I find any SQLAlchemy
issues.

> >http://groups.google.com/group/turbogears/browse_thread/thread/363c2e...

[ozwyzard]

Thanks for the info. I was able to get similar logging using a debug
flag in TG 2.x config file. Will update if I find any SQLAlchemy
issues.


On Sep 9, 7:25 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:

> >http://groups.google.com/group/turbogears/browse_thread/thread/363c2e...

[Chris Withers]

On 09/09/2010 20:51, ozwyzard wrote:
> TG2 uses scoped sessions and uses an additional layer of transaction
> manager. If I have a scenario as follows:
>
> import transaction
>
> def main()
> try:
> add main_record to session
> query main_record to get primary key
> call foo()
> except Exception, e:
> transaction.abort()

You might consider writing this as:

import transcation
def main():
with transaction:
...

...using transaction 1.1.0, it'll give you your commit as well as the
abort/rollback if an exception is raised.

> The above code is not rolling back the transaction.

My guess is because you never touched an orm-mapped object, which bit me
just yesterday...

> # Global session manager: DBSession() returns the Thread-local
> # session object appropriate for the current web request.
> maker = sessionmaker(autoflush=True, autocommit=False,
> extension=ZopeTransactionExtension())

Try changing the above to:

from zope.sqlalchemy.datamanager import STATUS_CHANGED

maker = sessionmaker(autoflush=True, autocommit=False,
extension=ZopeTransactionExtension(
initial_state=STATUS_CHANGED,
))

With the above you will get a COMMIT or ROLLBACK with every
transaction.commit() or transaction.abort(), regardless of whether or
not you touch any ORM-mapped objects...

cheers,

Chris

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