Iwan wrote: > > Hi there, > > I am working with SqlAlchemy for the first time (coming from > SqlObject), and I fear I may not understand it as well as I thought I > did... > > I have a class (X), persisted with SA which contains a key (X.key) > which is a randomly generated string of fixed length. This field is > supposed to be unique as well. > > When you create a X, though, there is the chance that the newly > generated key may clash with an existing one in the DB. In which case > I'd like to regenerate the random key and try again. But, you cannot > use a sequence in the DB for generating the key, since it should be > random, but unique.
have you considered using some more industrial strength "randomness", like GUIDs generated from the current timestamp or similar ? the python uuid module works very well for this. > Naïvely, I thought you'd create an X, flush it, and then catch any > IntegrityError's thrown. Something like: > > session.begin() > #create some other objects > > unique = False > #session.begin() > while not unique: > try: > newx = X() # It generates its own random key in the __init__ > session.save(newx) > session.flush() > unique = True > except IntegrityError: > pass > #session.commit() > > session.commit() > > But, this does not work: It seems that the whole transaction is rolled > back when the IntegrityError happens, so objects created before this > code is then lost here. Ive tried putting a nested transaction around > this bit of code (using session.begin/commit as indicated in > comments), but that did not work. if your database supports SAVEPOINT, this usage pattern can be achieved using begin_nested() to start a SAVEPOINT (but not with 0.4...). Otherwise, you have to query the database first for your value - and you might need to use table locks to prevent concurrent inserts. > This is done with SqlAlchemy 0.4 (and Elixir 0.5), on postgresql 8.3. if you just started with SQLAlchemy, why 0.4 ? 0.5 has been out in final release for six months. --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
