On Mar 31, 2008, at 12:40 AM, Nicholas Riley wrote:
> > Hi, > > I just tried out SQLAlchemy 0.4.4 on a simple project - the front end > for a vending machine. It uses two MySQL database connections. I've > used the session/transaction facilities so I can use SQLAlchemy's two- > phase commit to create a transaction spanning both databases. It's > working, but I'm having to do more things than I think I should be > doing, so I'm not sure if I'm misunderstanding SQLAlchemy or have > something set up incorrectly. > > First, according to the SQLAlchemy 0.4 release notes, it should be > possible to commit the session and have the changes take effect, but > I'm still needing to manually commit the transaction. I'm doing it > like this: > > transaction = session.begin() > ... do stuff ... > transaction.commit() > session.commit() You shouldn't be doing anything with the SessionTransaction explicitly, this is a backwards compatibility thing from 0.3. Use only begin()/commit() on the Session itself. When you commit on the SessionTransaction explicitly, the second commit() on Session will raise an error since ST asserts that the transaction is open when this call comes in (though theres a reason why its not happening for your case). > It also works when the two commit calls are reversed. Without the > transaction.commit(), the data gets saved to the database but is not > visible outside the session; is the transaction not committing or is > it a nested transaction? a "nested" transaction is what we term for a "SAVEPOINT", which isn't occuring unless you use "begin_nested()". The transaction has the quality though that multiple calls to begin() will require that many calls to commit() for the actual COMMIT to occur, this is so that code which uses its own begin()/commit() pair may be enclosed within other code which does the same. I think this is the problem you're having. > Second, I'm having to call session.close() to get updated data outside > an explicit transaction, when I thought creating the sessionmaker with > autoflush=True should cause an update to occur every time I make a > query. Calling session.flush() does not help, regardless of whether I > specify autoflush. if your session is still within a transaction, then you wont see data from outside that transaction (depending on isolation levels) until that transaction is committed/rolled back (or closed). To see the behavior of flush(), turn on SQL echoing (always the first step when anything is not working as expected). > > Definitions (yes, I realize there's a password in there): > > http://www.acm.uiuc.edu/projects/SIGArch/browser/sucrose/trunk/python/mysql/db.py > > Usage (see Sucrose.vend): > > http://www.acm.uiuc.edu/projects/SIGArch/browser/sucrose/trunk/python/api/sucrose.py > yes - the main thing is, the Session you've set up is "transactional", which means the session is always going to be in a "begun" transaction, when first constructed and after each commit - theres no need to call begin() explicitly in that case. By calling begin() a second time (since that's what it really is here), you are indicating you'd like to call commit() twice, so that would explain why your commit needs to be called twice, and also why no error is raised for the double-commit. An example of "transactional" session usage is here: http://www.sqlalchemy.org/docs/04/session.html#unitofwork_managing . I can see that the docs later down which describe what you need to know here might need some editing for brevity. If you'd like to call begin() explicitly, you might be better off leaving the "transactional" flag off - what this means is that when you're outside of a transaction, connections are returned to the connection pool after usage where their transactional state is rolled back, so that subsequent query operations will get the most recently available data. the only thing to be careful of is that when autoflush=True, you will be getting those flushes after each query which can't be rolled back if no transaction was begun. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
