[ANSWERED] Auto Incrementing Field

Anvil Q&A
1

I have a need for an auto-incrementing integer field on a data table. Anyone got a nice way of doing that?

1 Like
2

I would also very much like one of those. I haven’t yet found a reliable way to do it.

3

EDIT - don’t do this - see @meredydd 's answer elsewhere using transactions.

Got a feeling this is not what you’re looking for, but for the sake of typing something …
I have some code somewhere for this, but basically what I did was :

  • search, ordered by your id field descending and returning just the [0] record to get the biggest number so far.
  • add 1 to that value
  • use it in your add_row() call

If your app is seriously multi-user then you may need to lock the table first (store a random number in a lock table and make sure you can read back your number confirming it’s you that has the lock) else you might have a race.

(edit) - my answer doesn’t question the reasoning for doing this - see @p.colbert 's answer below for caveats to this method.

1 Like
4

That’s pretty much what I had in mind, but it felt dirty!!

5

I’m sure Anvil Central will have a better way, but it kinda works.

6

I was also wondering about using a sequence generator, but it would still have to do something similar when the app starts.

7

If your intent is simply to give each row a unique id, it already has one. See “Row IDs” in https://anvil.works/doc/#-div-id-data_tables_api-using-data-tables-in-python-div-

Otherwise, a contingency you may want to consider: will you ever have to delete the record that has the largest number? (This need not be part of the design. It could just as easily be due to a bug, or a GDPR request.)

If so, then you will end up reusing a sequence number. This can sometimes be a bad thing (e.g., if that number is referenced from somewhere else).

If it’s a bad thing, then you might use @david.wylie’s lock table approach. E.g., also store your largest sequence number in that table. While you have it locked, you can increment its sequence number, and stamp other record(s) with that new number.

8

It’s not just uniqueness that I need, it’s a contiguous sequence of integers.

I’m looking at holding a ‘next sequence number’ in a separate table, with a lock, so that I don’t have to query the entire history each time looking for the max value.

9

@p.colbert Just read your post more carefully and realised that’s exactly what you suggested!

10

Eek. No need for any of these shenanigans! Just use a transaction:

# Assume there's a table called 'Counter' with a single column ('value')
# and a single row:

@tables.in_transaction
def get_next_value_in_sequence():
  row = app_tables.counter.get()
  row['value'] += 1
  return row['value']

Anvil’s transactions are fully serialisable, and @tables.in_transaction automatically retries on conflict, so this function will produce a steady continuous sequence.

10 Likes
11

I knew they’d have a better way :slight_smile:

12

@meredydd Ha! I’ve just been playing with exactly that to see if it behaves as I was hoping. You’ve saved me a job!

13

@meredydd - can you move your answer up to underneath the question? Don’t want anyone looking at my nonsense before seeing your answer :slight_smile: