I am amezed by the power of SQLAlchemy. The minute I saw it I decided to use it. Maybe little irresponsible of me because it is a project already in producion, but I was tired of connecting to four different databases and doing everything manually. I'm doing it step by step mostly for new functionality but my goal is to replace all with SQLAlchemy. My only concern is that somebody else who later comes to maintain this project will have to learn another tool instead of using already familiar SQL (on top of using Python in the first place).
Regards,
Tvrtko Sokolovski
On 3/14/06, Michael Bayer <[EMAIL PROTECTED]> wrote:
the better solution is going to have to involve changes to the engine and the way it gets connections, the mapper and the way it does selects (since an out-of-transaction select is different from an in-transaction select), and the unit of work and the way it does commits. at the end of the day youd get a Session that uses a second connection in the same thread. the existing methods for using multiple Sessions can then be applied to work with units of work on two separate connections in the same thread.log_session = objectstore.Session(distinct_connection=True)engine.begin()j = Job(_sa_session=log_session)try:do_stuff()j.step = Step('step1', _sa_session=log_session)log_session.commit()do_more_stuff()j.step = Step('step2', _sa_session=log_session)log_session.commit()objectstore.commit()engine.commit()except:engine.rollback()does that work for you? it will take me several days to make that work.On Mar 14, 2006, at 4:15 PM, Michael Bayer wrote:if youre trying to have an explicit database transaction, and two separate unit-of-work sessions in the same thread on two different connections, thats not really supported so much. you might try using a ProxyEngine and connecting to a second engine when needed -engine = ProxyEngine()engine.connect('foo')engine.begin()# do stuff# now switch out the engineengine1 = engine.get_engine ()try:engine.set_engine(create_engine('foo'))objectstore.commit(log, job)finally:# switch it backengine.set_engine(engine1)# do more stuffengine.rollback()id have to think more about this pattern to come up with something cleaner.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
