Could we have the facility to define columns within the data tables service as requiring unique values (similar to an SQL unique constraint)?
5 Likes
It would be nice to be able to do this without a pre-lookup.
At the moment, I implement this using a dict within anvil.server.session.
The dict maps a table name to a python set - where the set contains tuples of the existing unique values for that table.
I initialise that dict when the app starts and prior to adding a row, make an attempt to add values to the set. If that addition succeeds, the row is added. If not, it fails.
That works, although I should mention the standard ways to do this. Imagine you have a table called my_table, with a column key whose values should all be unique:
If you want to create a row in this table with a given key or update the row with that key if it already exists:
@tables.in_transaction
def upsert(some_key,some_value):
row = app_tables.my_table.get(key=some_key) or app_tables.my_table.add_row(key=some_key)
row['value'] = some_value
If you want to add a new row, and it’s an error if that key already exists in the table, you can do this instead:
@tables.in_transaction
def insert(some_key,some_value):
if app_tables.my_table.get(key=some_key) is not None:
raise Exception("This key already exists")
app_tables.my_table.add_row(key=some_key, value=some_value)
4 Likes
Yep, I’ve used those. I’ve found that the session variable speeds up the insertions since the “index” is already loaded into memory. But, that’s obviously at the cost of startup time. I’m still playing…
Yeah; I figured you’d be familiar, but I wanted those examples available to anyone else who found this thread 
2 Likes
thank you for this! I just started using anvil - am not a programmer but have taught myself various things for my own use or my employer or non-profits I work with. Yours is the fourth platform I have tried for this non-profit project I am doing. The resources are fantastic for learning independently even without knowing python etc. This is exactly what I was looking for just now
2 Likes
Did this one ever go anywhere?
I have have coded around it, but would like to leverage the power of postgres.