Hey all, I have some code that attempts to add a bunch of new entries to a table in a single commit, with some try/excepts designed to catch any primary key errors. if any errors occur, i want to be able to manually go through and re-attempt each line one by one to understand which ones fail and which don't, and to make sure all the legal ones get done.
def add_rows_to_table(self,all_rows): Session = sessionmaker(bind=self.engine) session = Session() for row in all_rows: item = MyClass(row) session.add(item) try: session.commit() except Exception,e: #roll back, start new session, add rows one by one, raise more specific error print '='*10,'\nDEBUG-- encountered error, attempting one-at-a-time commits' print 'database contents as follows %s'%str(MyTable.get_table_as_columns_dict()) session.rollback() session.close() for rowno, row in enumerate(all_rows): #commit rows one at a time etc etc However, I'm getting some unexpected behaviour. When I do my initial transaction which attempts to do all rows at the same time, it falls over as expected if it runs into a PK clash and says it's doing a rollback.. BUT in fact any of the successful rows before the pk clash seem to still be in the db, ie they were'nt rolled back. is this expected behaviour? 2010-06-17 11:06:55,682 INFO sqlalchemy.engine.base.Engine.0x...0x35 BEGIN 2010-06-17 11:06:55,991 INFO sqlalchemy.engine.base.Engine.0x...0x35 INSERT INTO "Table1" (pk, col1, col2) VALUES (?, ?, ?) 2010-06-17 11:06:55,996 INFO sqlalchemy.engine.base.Engine.0x...0x35 (1, 'a good row', 'abc') 2010-06-17 11:06:56,049 INFO sqlalchemy.engine.base.Engine.0x...0x35 INSERT INTO "Table1" (pk, col1, col2) VALUES (?, ?, ?) 2010-06-17 11:06:56,053 INFO sqlalchemy.engine.base.Engine.0x...0x35 (1, 'a row with pk collision', 'jkl') 2010-06-17 11:06:56,090 INFO sqlalchemy.engine.base.Engine.0x...0x35 ROLLBACK ========== DEBUG-- encountered error, attempting one-at-a-time commits 2010-06-17 11:06:56,397 INFO sqlalchemy.engine.base.Engine.0x...0x35 BEGIN 2010-06-17 11:06:56,413 INFO sqlalchemy.engine.base.Engine.0x...0x35 SELECT "Table1".pk AS "Table1_pk", "Table1".col1 AS "Table1_col1", "Table1".col2 AS "Table1_col2" FROM "Table1" 2010-06-17 11:06:56,421 INFO sqlalchemy.engine.base.Engine.0x...0x35 () database contents as follows {'col2': ['abc'], 'col1': ['a good row'], 'pk': [1L]} <---- TABLE SHOULD BE EMPTY seems to me like the rollback isn't working. at the sqlite command-line, rollback works fine, so could it be a sqlalchemy problem? sqlite> create table tbl1('pk' numeric, 'col1' text, primary key (pk)); sqlite> begin; sqlite> insert into tbl1 values(1,'a'); sqlite> insert into tbl1 values(2,'b'); sqlite> insert into tbl1 values(3,'c'); sqlite> select * from tbl1; 1|a 2|b 3|c sqlite> insert into tbl1 values(3,'CLASH'); Error: column pk is not unique sqlite> select * from tbl1; 1|a 2|b 3|c sqlite> rollback; sqlite> select * from tbl1; sqlite> thanks in advance for any help! hp -- ------------------------------ Harry J.W. Percival ------------------------------ Italy Mobile: +39 389 095 8959 UK Mobile: +44 (0) 78877 02511 (may be turned off) Skype: harry.percival Email: harry.perci...@gmail.com -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.