[sqlalchemy] Re: Creating something with a unique, random key
Michael, On May 22, 6:03 pm, Michael Bayer mike...@zzzcomputing.com wrote: 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. That's probably the direction I'd go - just wanted to find out whether I do understand the limitations of the database itself correctly. 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. In my original line of thinking this would probably be the best solution. But they way ids are generated (using sequences on postgresql, for example) just seems far superior: you don't have to worry about stuff like transactions, locking, etc - you just get the next, unique number. It would have been nice if there was some DB support for a random sequence-like thing. But I suppose my key can always consist of the id generated by the db + some random component. 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. Yup, thanks I saw that... We need to be on a LTS version of Ubuntu, the latest of which is hardy - and it is still on 0.4... --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Creating something with a unique, random key
Adrian, On May 22, 7:45 pm, Adrian von Bidder avbid...@fortytwo.ch wrote: I guess you just have to query for your string to see if it's unique. Performance-wise it shouldn't make a difference, and in Python, I usually find a simple if even nicer than a try-except block. Yup, thanks, that is what we are doing now. -i --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Creating something with a unique, random key
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 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Creating something with a unique, random key
On Friday 22 May 2009 12.01:05 Iwan wrote: Naïvely, I thought you'd create an X, flush it, and then catch any IntegrityError's thrown. [...] I know that PostgreSQL can't continue in a transaction after an error, you have to roll back the transaction. I don't know what the SQL standard says on this, but that's how postgres has always behaved. I guess you just have to query for your string to see if it's unique. Performance-wise it shouldn't make a difference, and in Python, I usually find a simple if even nicer than a try-except block. cheers -- vbi -- Jeder Mensch ist einzigartig. -- Benedetto Croce signature.asc Description: This is a digitally signed message part.
