I'm using SQLAlchemy 0.4 inside a Pylons application with a SQLite backend. I'm having trouble getting SQLAlchemy transactions to behave how I want.
Here's what I want to do, in pseudo-code: begin transaction create new row1 in table1 save row1 do some other (non-database) stuff if condition: modify some columns in row1 create new row2 in table2, with a foreign key column pointing to the row1.id save row1 save row2 if errors: rollback transaction else: commit transaction For complicated reasons, it's not feasible to do all the database work in a single place. :( I'm having a bunch of problems: 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? (I know such a thing is possible, in theory, as I have worked with a homegrown ORM using PostgreSQL that did this. Maybe this is a shortcoming of SQLite?) 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. 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? Thanks in advance. I am new to SQLAlchemy (and SQLite and Pylons) so please forgive me if I'm missing something obvious. -matt --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
