Best Practices Adding/Removing From DB

[thenerdypython]

My app is almost ready now and I have it working updating the database records to add a snake record and from looking around on the forums and tutorials the way I’m doing that is:

@anvil.server.callable
def get_my_snakes():
  me = ensure_user()
  return app_tables.snakes.search(user = me)

@anvil.server.callable
def get_snakes_table():
  me = ensure_user()
  return app_tables.snakes.client_writable(user = me)


self.my_snake_items = anvil.server.call('get_snakes_table')
self.my_snakes_table.items = anvil.server.call('get_my_snakes')

which I guess basically lets the client have writable access without me having to change the table permissions, etc for the form. I’m also wanting to add remove snake entries where I will just pull their snakes from the table and populate a list they can remove them from but what is the best approach/best practices for this sort of stuff as far as checking the DB if a record already exists, etc which I’m not doing on the add atm. I’m curious what the rest of people here use for generating their ID’s, etc right now I’ve been testing using the userid + the snake name but wasn’t sure the best approach here in a multi user setup like this.

Would my approach above still be the best for the removal I’m assuming as well or is there a better way to do this than what I’m doing?

Thanks again.

[shaun]

Row objects have ids that you can use if you want to check the specific record from the database.

# 'jane_smith' is a Row object from the 'people' table
janes_id = jane_smith.get_id()

r = app_tables.people.get_by_id(janes_id)

[thenerdypython]

Ok I got the get id working but I see its a list any idea what the first and second items are in this list it gives back?

Thanks

[shaun]

Regarding restricting user access to rows - Here’s a video tutorial explaining how to restrict acess to users in the context of a Todo List app. Sounds like your use-case is very similar:

Multi-User Applications with Anvil

Regarding accessing rows by ID: The list you see just holds internal information that Anvil uses to know where the data is in Postgres. You can just pass that in to get_by_id without needing to know what the numbers mean.

I copied the jane_smith.get_id() example from the Reference Docs, so taking a look there might help you:

https://anvil.works/doc/#-div-id-data_tables_api-using-data-tables-in-python-div-

# Get Jane Smith's row from the table
jane_smith = app_tables.people.get(Name="Jane Smith")

You can also get the same type of object using app_tables.people.search():

jane_smith = app_tables.people.search()[0]

The return value of search() is a SearchIterator, which is a lazily-evaluated view of the Data Table.

The return value of search()[0] or get() is a Row, which is a lazily-evaluated view of an individual Data Table row.

[thenerdypython]

Great thanks I’m looking at that now, how might this work for say my snakes that a user is linked to in the snakes table? So for example user adds Snake A to the table via my form how do you see most people dealing with the records do they create a unique ID based off the user id value + the name or how might that work?

I wasn’t sure if these table rows are unique to the users or not cause I’m guessing if I did this soley by the name column it wouldn’t work cause they couldn’t all be the same name right.

[shaun]

I think the question changed under your feet!

[david.wylie]

Right - assuming the question stays put

You create a link in your snakes table to the users table, and you put the user object into that column.

So create a new column in “snakes” called “owner” and select link to table and choose Users.
Then when you create a new snake, do something like this :

me = anvil.users.get_user()
app_tables.snakes.add_row(name="python",owner=me)

Anvil maintains the link for you.

[thenerdypython]

Ah I see now behind the scenes even this snake table has a ID I just have to do a get by the name then do the same get_id for it, so this does mean that I can have multiple rows with the same name IE multiple users with the same snake name and it will work fine cause I’m only going to be dealing with their snakes they can access/see so I still should be fine to delete just by showing them a list of their snake records and setting up a delete row for when they click the delete button, etc.

Sound logical? Right now the snake table rows are tied to the user so that’s how I’ve been doing it.

[david.wylie]

Blimey - you’re even faster than me

You don’t really need to get the ID of the record.
You can fetch all “your” snakes like this :

me = anvil.users.get_user()
result = app_tables.snakes.search(owner = me)

[thenerdypython]

yea sorry about that this is what I wanted and what I was curious about its basically how I already have the db setup I was just getting to the part of updating and removing the rows via forms, thanks

[david.wylie]

its basically how I already have the db

Ah ok. I wasn’t sure.

[thenerdypython]

Yep I looked at some tutorials and saw that is how they did it so I already have it setup that way and have the records showing up, now I just have to create delete record which is what I’m doing now, just wasn’t sure if people do validation on this stuff before deleting, etc that is what I was curious about before I built all this out.

Just wanted to make sure I was only deleting snakes from the right users

Thanks again.

[thenerdypython]

So I’m getting an error when trying to do this:

tables.TableError: Cannot automatically add column 'owner' to this view

Here is my code

@anvil.server.callable
def get_snakes_table():
  me = ensure_user()
  return app_tables.snakes.client_writable(owner = me)

self.my_snake_items = anvil.server.call('get_snakes_table')

def add_snake_click(self, **event_args):
    """This method is called when the button is clicked"""
    me = anvil.users.get_user()
    self.my_snake_items.add_row(sex=self.gender_dropdown.selected_value,
                              genes={"genes": [self.genes_dropdown.selected_value]},
                              hatchdate=self.hatch_date_picker.date,
                              name=self.name_text_box.text,
                              type=self.type_dropdown.selected_value,
                              owner=me,
                              alive=self.alive_dropdown.selected_value,
                              producer=self.producer_dropdown.text)

So what am I doing wrong

[shaun]

You need to add an owner column to the table. It is not automatically generated for you.

[thenerdypython]

I have that already it was there before, I have it setup as a link to the Users row/table

[thenerdypython]

Looks like I had to create this as a server call and put the add row call there so the user permissions worked right, now its all working

[thenerdypython]

So bringing up a semi old topic here. I’m now going back and adding in some logic so people can’t accidently write the same snake name to different table records since I’m using snake name throughout my app.

Curious others thoughts here as I’m testing a few options for the best option but I’m thinking when someone adds a snake I would just pull their records and see if a snake with the same name in the row exists otherwise add it.

Or I guess I could use the row ID, I’m just curious how others have approached this as well as formatting since obviously its case sensitive so I’ve debating just making everything lowercase as I put it in. Wasn’t sure if someone with more experience with anvil that might have hit some of this and your opinions.

I’m testing a few things myself here but figured I’d put the feeler out there while I test this myself. I created a big bug here for myself by not doing form validation and now people are adding duplicate snakes doh

[thenerdypython]

For now I’m just checking the record of snake name, but I’m wondering if I should be doing more using the row ID or is having 1 column like this that I do verification on you think ok? I mean the snake name I would think should be unique enough

[shaun]

A few bits of advice that spring to mind:

1. It seems to me that your primary key is (user, snake_name).
2. Don’t constrain snake names to be globally unique, only unique within a particular user. You don’t want users to be able to discover each others’ data by trying names and seeing what’s already taken.
3. There are lots of ways of introducing new constraints while keeping the old data. For example, you could store a ‘schema version’ number in the table, and not count records with old schema versions when checking for duplicates. Or start a new table for the new, unduplicated data. Or have a schema version column in the Users table so legacy users have the old constraints applied.
4. If I were you, I would compare snake names case-insensitively (snake1.lower() == snake2.lower()) but store them with the case information. Users would probably like to choose where to put the capital letters!

[thenerdypython]

Cool yea this is what ive been thinking too. For now each user has their own space if you will in the db so only between each user they can’t really have a snake with the same name.

I can’t really think of a case where they should have the same name anyways so I think it should be good

I’ll play around with it some more

Thanks