Hi! Let's say I have a simple table and want to create a function to ensure that a given row exists in that table. That is, the desired semantics is that after I call this function, the given row will exist: if it doesn't exist before the call, it will be created.
I'm using SA 0.4.7 and the SQL Expression Language. My initial attempt follows: def make_sure_row_exists(row_attr_dict): s = MyTable.insert(row_attr_dict) try: s.execute() except IntegrityError: pass # row already exists, so we need do nothing more In sum: try to create the row and if it's already there, great. The trouble is, if the row already exists, SA will issue a ROLLBACK on the connection, wiping out any as-of-yet-uncommitted work that may have built up previously: sqlalchemy.engine.base.Engine.0x..d0 INSERT INTO mytable (x, y) VALUES (?, ?) sqlalchemy.engine.base.Engine.0x..d0 ['1', '1'] # already exists sqlalchemy.engine.base.Engine.0x..d0 ROLLBACK This ROLLBACK makes it hard to use this function as a building block in larger transactions. (Unless I somehow isolate this function in its own transaction, e.g., by using nested transactions.) What that background, I have some questions: 1. Is there a better way to use SA's SQL Expression Language to implement a function with the desired semantics? 2. How can I tell SA that it's fine if the row already exists and not to issue the ROLLBACK? 3. Is there any documentation on the interaction of transactions between ORM sessions and SQL Expression Language connections? (I ask this because I first noticed the ROLLBACK-on-exception issue when work on an ORM session failed to be committed as usual. Since the ORM work was done via a scoped session object and the SQL-EL work was done as shown in the code snippet above on an engine that does not use threadlocal execution strategy, I would have expected the SQL-EL-issued ROLLBACK not to affect the ORM work, they presumably being on different connections, albeit to the same database.) If anybody can shed some light, I would be grateful. Thanks for your help! Cheers, Tom --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---