On Jun 18, 2010, at 7:46 AM, Harry Percival wrote: > 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 is the test I'm using, if you can tell me how to make it get your results, then we'd have a better idea what is going on. Below, if you let the session.query(Foo) run, the error is: The transaction is inactive due to a rollback in a subtransaction. Issue rollback() to cancel the transaction. that's what I mean by unusable. If OTOH you uncomment the line that uses the engine to do the query, you get: database contents as follows [(2,)] which is the row the test inserts and commits before running in the loop. row (1,) from the loop is not present. from sqlalchemy import * from sqlalchemy.orm import * from sqlalchemy.ext.declarative import declarative_base engine = create_engine('sqlite://', echo=True) all_rows = [ (1,), (2,), (3,), ] Base = declarative_base() class Foo(Base): __tablename__ = 'foo' id = Column(Integer, primary_key=True) Base.metadata.create_all(engine) Session = sessionmaker(bind=engine) session = Session() session.add(Foo(id=2)) session.commit() for row in all_rows: foo = Foo(id=row[0]) session.add(foo) 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(engine.execute(Foo.__table__.select()).fetchall()) print 'database contents as follows %s'%str(session.query(Foo).all()) session.rollback() session.close() > > > 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. > 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. -- 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.