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 <[email protected]>
> 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 [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.
>
>
>
> --
> ------------------------------
> Harry J.W. Percival
> ------------------------------
> Italy Mobile: +39 389 095 8959
> UK Mobile: +44 (0) 78877 02511 (may be turned off)
> Skype: harry.percival
> Email: [email protected]
>
> --
> 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.
--
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.
