| i thought you wanted to use raw SQL to update the data ? thats what you had in your example.
do you need to be using database transactions or not ? this is getting confusing. On Mar 14, 2006, at 3:26 PM, Qvx 3000 wrote: Thanks, I'll try. But I think it is the other way around:
job/log should be using unique_connection() because log activity should be commited no matter what. "insert into business" is part of work that should be rolled-back/commited along with other business statements and mapper object activity (on main connection).
How do I save job/log object using unique_connection()?
Wild guess:
class Job(object):
def update_job(self, step):
self.step = step
# was: objectstore.commit(self)
unique_connection().commit(self) # ???
On 3/14/06, Michael Bayer <[EMAIL PROTECTED]> wrote: OK, once you say engine.begin(), engine.rollback(), that is a database level transaction, so writing any data to the database within that block will not remain after you do the rollback....all calls to the engine are going to return the same connection that is part of the transaction, which has to do with the nature of the connection pool with the "use_threadlocal" flag. also doing a rollback() without the begin() at the engine level will do nothing since it doesnt have a connection object present with which to roll back on.
since you need a connection from the engine that is not part of the current thread-local context, i just checked in a method "unique_connection()" that interacts with the pool to give you a connection that is guaranteed external to the current thread-local connection. by default, SQLAlchemy's pool returns the same thread-local connection if one is already checked out, so this method enables you to get around that.
then execute as follows:
engine.execute("insert into business(id) values(77)", connection=engine.unique_connection())
if the method isnt working, let me know. On Mar 14, 2006, at 1:47 PM, Qvx 3000 wrote: I guess I understand what you are saying but something is not right or I'm still missing something.
I tried an example to test what you were saying. Here it is (Oracle):
========
from sqlalchemy import *
engine = create_engine('oracle://dsn= 127.0.0.1&username=scott&password=tiger', echo=True)
job_table = Table('job', engine,
Column('id', Integer, primary_key=True),
Column('step', Integer))
business_table = Table('business', engine,
Column('id', Integer, primary_key=True))
job_table.create()
business_table.create()
class Job(object):
def update_job(self, step):
self.step = step
objectstore.commit(self)
class BO(object): pass
assign_mapper(Job, job_table)
assign_mapper(BO, business_table)
# TEST 1
# If I do it like this, update_job's work is lost.
# It wouldn't be in normal autonomous_transaction I'm talking about.
engine.begin()
job = Job(id=1, step=0)
job.update_job(1)
engine.execute("insert into business(id) values(77)")
engine.execute("insert into business(id) values(88)")
job.update_job (2)
engine.execute("insert into business(id) values(99)")
bo = BO(id=1)
engine.rollback()
print [x.step for x in Job.select()] # Empty! Not what I want
print [ x.id for x in BO.select()] # Empty - good
# TEST 2
# If I do it like this, update_job's commit also
# commits manually inserted objects
job_table.drop()
job_table.create()
business_table.drop()
business_table.create()
#engine.begin()
job = Job(id=1, step=0)
job.update_job(1)
engine.execute("insert into business(id) values(77)")
engine.execute("insert into business(id) values(88)")
job.update_job (2)
engine.execute("insert into business(id) values(99)")
engine.execute("rollback") # No effect, I guess there is implicit commit going on
engine.rollback() # No effect
print [x.step for x in Job.select()] # Something - good
print [x.id for x in BO.select()] # Something! Not what I want (There is even 99 present)
job_table.drop()
business_table.drop()
=========
It seems that engine.begin() only postpones python commits until engine.commit(). That is why my log never gets written.
On the other hand if I don't use engine.begin() then objectstore.commit(obj1) eventually tells database to COMMIT and database of course writes EVERYTHING. Later "ROLLBACK" and engine.rollback() have no effects (data is already commited - I guess implicitly).
Regards,
Tvrtko Sokolovski
|
