Re: [Sqlalchemy-users] Autonomous transactions

[Michael Bayer]

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