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 <[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]<sqlalchemy%[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.
