On Sep 22, 2008, at 11:49 AM, Tom Moertel wrote:
> 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 the Connection/Engine work by default in an autocommit fashion. To control transaction boundaries: http://www.sqlalchemy.org/docs/04/dbengine.html#dbengine_transactions > > 3. Is there any documentation on the interaction of transactions > between ORM sessions and SQL Expression Language connections? yes: http://www.sqlalchemy.org/docs/04/session.html#unitofwork_sql These patterns have been improved in the 0.5 series as well. > (I ask this because I first noticed the ROLLBACK-on-exception > issue when work on an ORM session failed to be committed as usual. the ORM Session will always issue a ROLLBACK when a flush() fails. This is due to two things - the database often requires a ROLLBACK in any case (i.e. a postgres transaction typically can't continue after an exception such as the above), and also that flush()'s bookkeeping only executes after the full set of SQL is issued, so therefore does not at the moment support "partial progress" of its SQL to be issued. In 0.5 this can be worked around using SAVEPOINT via session.begin_nested() (0.4 has more limited support for this usage). > 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.) 0.4 has a behavioral feature which has been fixed in 0.5, which is that the connection pool is set to still use threadlocal execution. Fix this by issuing create_engine(url, pool_threadlocal=False). For any new project, I would strongly urge the usage of 0.5 which is in final release candidate status. Particularly with regards to transactional control, 0.5 is vastly superior to 0.4. Docs are much better too particularly regarding this topic. --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
