Help with implicit transactions
George Sakkis
using SqlAlchemy with PostgreSQL and I am creating a few records that
I need to use for subsequent SELECTs within the same method. Although
I do flush() them and get their assigned IDs, subsequent explicit
SELECTs (using SA's select(), not the data mappers) fail to see the
changes.
Alternatively, is there an easy way to turn off the transaction
altogether for a specific method ? The workaround shown in [1] looks
messy and error prone.
George
Alberto Valverde
On Apr 16, 2007, at 5:54 PM, George Sakkis wrote:
>
> Is there any way I can force a COMMIT within an exposed method ? I am
> using SqlAlchemy with PostgreSQL and I am creating a few records that
> I need to use for subsequent SELECTs within the same method. Although
> I do flush() them and get their assigned IDs, subsequent explicit
> SELECTs (using SA's select(), not the data mappers) fail to see the
> changes.
How are you obtaining the engine when you use SA's select? You should
use the same connection the transaction is using so you see its
changes, check out [1]
>
> Alternatively, is there an easy way to turn off the transaction
> altogether for a specific method ? The workaround shown in [1] looks
> messy and error prone.
Not that I'm aware of.
Alberto
George Sakkis
> On Apr 16, 2007, at 5:54 PM, George Sakkis wrote:
>
>
>
> > Is there any way I can force a COMMIT within an exposed method ? I am
> > using SqlAlchemy with PostgreSQL and I am creating a few records that
> > I need to use for subsequent SELECTs within the same method. Although
> > I do flush() them and get their assigned IDs, subsequent explicit
> > SELECTs (using SA's select(), not the data mappers) fail to see the
> > changes.
>
> How are you obtaining the engine when you use SA's select? You should
> use the same connection the transaction is using so you see its
> changes, check out [1]
I use:
from turbogears import database as db
engine=db.get_engine()
How can I access the implicit transaction created by TG or its
connection object ?
George
Alberto Valverde
You'll need access to the transaction instance. Unfortunately it's a
local variable inside database.sa_rwt. Perhaps it should be bound to
cherrypy.request so you could do:
connection = engine.connect()
cherrypy.request.sa_transaction.add(connection)
then connection.execute(table.select()) # or whatever
as described in the mentioned link. Can you try this patch see if it
works?
Index: turbogears/database.py
===================================================================
--- turbogears/database.py (revision 2824)
+++ turbogears/database.py (working copy)
@@ -336,7 +336,8 @@
[run_with_transaction.when("_use_sa()")]
def sa_rwt(func, *args, **kw):
log.debug("New SA transaction")
- transaction = session.create_transaction()
+ req = cherrypy.request
+ transaction = req.sa_transaction = session.create_transaction()
try:
retval = func(*args, **kw)
transaction.commit()
Alberto
Janzert
It would be nice if instead of holding onto a local reference to the
transaction it used the one in the request object to commit and rollback
(i.e. get rid of the transaction variable above and change all
references to req.sa_transaction). I believe this would solve the
problem where people want to be able to rollback the transaction and
then start a new one within a controller.
Janzert
Alberto Valverde
On Apr 16, 2007, at 10:47 PM, Janzert wrote:
>>
>> Index: turbogears/database.py
>> ===================================================================
>> --- turbogears/database.py (revision 2824)
>> +++ turbogears/database.py (working copy)
>> @@ -336,7 +336,8 @@
>> [run_with_transaction.when("_use_sa()")]
>> def sa_rwt(func, *args, **kw):
>> log.debug("New SA transaction")
>> - transaction = session.create_transaction()
>> + req = cherrypy.request
>> + transaction = req.sa_transaction = session.create_transaction()
>> try:
>> retval = func(*args, **kw)
>> transaction.commit()
>>
>> Alberto
>>
>
> It would be nice if instead of holding onto a local reference to the
> transaction it used the one in the request object to commit and
> rollback
> (i.e. get rid of the transaction variable above and change all
> references to req.sa_transaction). I believe this would solve the
> problem where people want to be able to rollback the transaction and
> then start a new one within a controller.
Good point. Mind posting opening a tickjet at the Trac with a patch?
Thanks,
Alberto
Janzert
Done, ticket #1359.
Janzert
Alberto Valverde
Great, thanks!
Alberto
Marco Mariani
> You'll need access to the transaction instance. Unfortunately it's a
> local variable inside database.sa_rwt. Perhaps it should be bound to
> cherrypy.request so you could do:
>
> connection = engine.connect()
> cherrypy.request.sa_transaction.add(connection)
>
> then connection.execute(table.select()) # or whatever
>
> as described in the mentioned link. Can you try this patch see if it
> works?
>
The following works for me, without any patch. Is there a reason it
should not?
conn = session.context.get_current().connection(SomeMappedClass)
conn.execute(....).fetchall()