Hi Michael, the get_table_as_columns_dict starts a new session and does a select all, returning results as a dictionary of columns-lists. my reading of the debug output though, is that this happens *after* a rollback which seems to be called automatically:
debug output starts from the first attempt to commit all rows to the database - ie in the "try" clause, before the "except" clause 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 * <---- this rollback here I didn't explicitly call for, it seems to happen automatically when the pk clash happens* ========== DEBUG-- encountered error, attempting one-at-a-time commits *<----- we're now in the except clause *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]} de debug output is generated cos my engine is set to echo=True. What do you mean 'the session is unusable here'? do you mean i can't use the same session inside the try and except clauses? and is it possible that the new session that get_table_as_columns_dict creates somehow sneaks in before the try: clause's session has completed the rollback (because it doesn't look like that from the debug output...). How else would i roll back the transaction anyway? here's it's code, for info: class MyTable(object): """ special class to represent a database table. used in mapper, but has some useful class-methods for querying the whole table... """ @classmethod def get_table_as_columns_dict(cls): """ return the table as a dictionary, with keys as column names and values as list of column values NB - dictionaries are unsorted, so cols may show up in any order! """ if cls.am_mapped(): table_dict = {} for col in cls.column_names(): table_dict[col] = [] for row in cls.select_all(): for col in cls.column_names(): if row is None: #sqlalchemy bug?? retrieving a row with from a single column table with val=null fails table_dict[col].append(None) else: table_dict[col].append(getattr(row,col)) return table_dict else: return None thanks for your help! bonus question: is it really worth it? the alternative is to stop trying to add all the rows in a single commit, and just commit them one-by-one from the beginning (in fact that's what i've done to get round this bug - essentially delete the try clause and only use the code from the except clause). But I was worried there might be a big perf. difference? On Thu, Jun 17, 2010 at 3:26 PM, Michael Bayer <mike...@zzzcomputing.com>wrote: > > On Jun 17, 2010, at 6:21 AM, Harry Percival wrote: > > `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? > > > If the debug output below is from the "print" line above that says > "database contents as follows", you haven't rolled back the transaction yet, > which is suspcious here, but the underlying SQLite transaction is rolled > back, so at that point the table is empty. The session is also unusable on > that line so this makes it that much more mysterious what > "get_table_as_columns_dict()" could possibly be doing. > > Since I don't know what "MyTable.get_table_as_columns_dict()" is or > anything else like that, you'd have to illustrate a full test. I imitated > your code above as closely as I could tell and it works fine. > > > -- > 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<sqlalchemy%2bunsubscr...@googlegroups.com> > . > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > -- ------------------------------ 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.