On Aug 1, 2011, at 11:07 AM, Eduardo wrote: > Daer all > > I have a following problem : > > Session = sessionmaker(bind=engine) > > session = Session() > for item in items: > item1 = session.query(item) > if len(item1)==0: > session.add(item1) > session.commit() > > The session is valid for a loop and I write items in a database during > each iteration. Can it happen that if an item is identical to the > previous one, the session.commit() will not make it to write the > previous item in the database before the query of the next iteration > is executed and the next session.commit() will fail because of > sqlalchemy.exc.IntegrityError: (IntegrityError) duplicate key value > violates unique constraint. > How can I make sure that the previous item is already in the database > before the query in the next iteration is carried out?
I'd get the data in order before going in, and most likely do not commit() on every insert, this is wasteful. Assuming the primary key of "item" is "item.id": # uniquify on id items_by_id = dict((item.id, item) for item in items) for item in items_by_id.values(): # merge each. item = session.merge(item) session.commit() If the above approach is emitting too many SELECTs for your taste, an additional optimization would be to pre-load the existing items, such as: # get the ids into a dict items_by_id = dict((item.id, item) for item in items) # load whatever is present first in one SELECT, keep them in memory # inside of existing_items existing_items = session.query(Item).filter(Item.id.in_(items_by_id)).all() # then do the loop + merge() -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@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.
