request_table = Table('request', metadata,
Column('id', Integer, primary_key=True),
Column('number', Integer, unique=True, nullable=True,
default=text('(SELECT coalesce(max(number), 0) + 1 FROM
request)')))
This seems to work well. But is this a good way to do this or can it
cause complications?
On Apr 13, 11:23 am, "Koen Bok" <[EMAIL PROTECTED]> wrote:
> Ok, I'd rather handle it on the database level. Is that just a matter
> of creating a function and calling it on insert?
>
> Koen
>
> On Apr 13, 4:47 am, "Ants Aasma" <[EMAIL PROTECTED]> wrote:
>
> > On Apr 13, 2:47 am, Jorge Godoy <[EMAIL PROTECTED]> wrote:
>
> > > IF you insist on doing that at your code, make the column UNIQUE (or a
> > > PK...) and write something like this pseudocode:
>
> > > def save_data():
> > > def insert_data():
> > > try:
> > > unique_column_value = get_max_from_unique_column
> > > Class(unique_column_value + 1, 'other data')
> > > except YourDBExceptionForConstraintViolation:
> > > sleep(random.random())
> > > insert_data()
>
> > > The 'sleep(random.random())' is there to avoid constant clashes and to
> > > be "fair" to all connections that are inserting data on your table.
>
> > To get an uninterrupted number sequence you need to serialize your
> > inserts to that specific entity, for which you basically need locking.
> > The quoted approach is optimistic locking, where you hope that no one
> > tries to insert another row between when you use the
> > get_max_from_unique_column and do the database commit, but are ready
> > to retry if that expectation fails. Another way would be to use
> > pessimistic locking, by doing the get_max_from_unique_column query
> > with lockmode='update'. Then any other thread trying to insert another
> > row while you're busy inserting yours will have to wait.
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
