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: [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.
