Im assuming you're writing an ORM centric application for my answers below. If not, the answers would be slightly different.
On Dec 28, 2007, at 8:02 PM, Glypho Phobet wrote: > > Problem #1: I can't get the id of row1 until I commit the > transaction. I'd like to be able to get, before I commit, the id that > row1 will have after the commit, so that I can enter it in row2. Or > is there some way of telling SQLAlchemy that a particular column in > row2 should point to whatever the id of of row1 will be when the > commit happens? why cant you get the id until commit happens ? with the ORM you just issue a flush() anytime you want and it will insert records/get new ids. im assuming you are also using session.begin() and commit() to frame the larger transaction; within those, you can issue as many flush() calls as you like and they participate in the same transaction....so you can do any number of persists, loads, deletes, whatever, without ever having to commit anything. you can also issue SQL if you wanted to execute postgres sequences or something like that. > Problem #2: If I commit before creating row2, in order to get the id > of row1, any changes I make to row1 after I commit are immediately > reflected in the database -- regardless of whether I commit or > rollback at the end. If I try to re-save row1 after making changes, I > get a traceback saying that row1 is "already persistent," like this: > > <class 'sqlalchemy.exceptions.InvalidRequestError'>: Instance > '[EMAIL PROTECTED]' is already persistent > > Is there a way to make this object non-persistent again? I want to be > able to rollback all changes made to row1 and row2 together, if > there's an error. if a transaction fails, you need to remove (or repair, if thats feasable) whatever offending objects are present in the session. if your process is going to re-do everything and recreate objects, you need to clear the whole session using session.clear()..this is typically the best approach after a transaction fails. usually within a web application a failed transaction means youre going to report an error and end the request..if you are "retrying" wihtin one request and doing everything again (which is unusual), just do a clear(). > Problem #3: Even if I do this in two separate transactions, and > re-query for row1 by id the second time I need it, SQLAlchemy returns > an instance that is "already persistent." Probably this is some sort > of > caching that SQLAlchemy is doing, which I will be very grateful in > general, > but is there a way to turn it off for one query? > the session always returns the same instance for a particular primary key once its loaded or persisted. if you want to remove that instance, use session.expunge(theinstance). or as above session.clear() to clear the whole thing. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com 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 -~----------~----~----~----~------~----~------~--~---