How to update Row

Anvil Q&A
1

A while back somebody did:
self.myname = app_tables.people.add_row(Name=“Jane Smith”,
Age=7,
Employed=False)

somewhere in init:
self.friends = anvil.server.call(‘get_friend’)
self.mybestfriend = self.friend.search()

…i have a dropdown with some values…
self.best_friend_drop_down.items=app_table.find_people…(list of friends…)
self.drop_down.selected_item=self.mybestfriend…

…User selects a different one

self.drop_down_change(self, **event_args):
#How do I update my row…
#if I never selected anything I do:
if not self.mybestfriend:
self.friend.add_row(self.item(…))
else:
self.mybestfriend = newly selected item?

How exactly do I update the table…I see in docs, how to add_row, but that causes bestfriend to be added each time instead of replaced.
If I initated self.mybestfriend in init, when I try to update it in drop_down_change function I get
TypeError: ‘’ object is not callable
at Form1, line 83

I can’t seem to locate instructions on how to update linked row with new value.
update_row(Mybestfriend=self…?)?

Thanks
Lucas

3

I’ve not tried this, and I may have misunderstood, but can’t you just set the link field to the new linked-to value? I don’t think there’s an update per se, the data is live when you work on the row object (or client view).

For example (almost using your example but not quite) :

Table A :
name, tableB.bestfriend (link)
name=“dave”
bestfriend=None

Table B :
bestfriend (values “fred”,“jim”,“ernie”)

So …

# Fetch main record.
row=app_tables.tableA.get(name="dave")

# Fetch besty you want (probably loaded into a drop down but this is for simplicity)
besty=app_tables.tableB.get(bestfriend="ernie")

# Just set the link to the new besty object.
row['bestfriend']=besty

And that should do it. Did I understand correctly?

4

Thanks. update is what I needed. I guess I was close with update_row… :wink:

update(MyLinkedColumn='some new value from dropdown')

example of drop down change:

#somehwere in init, I get current friend that I will display in dropdown as "selected_value", and items will contain all people
#selected value for my dropdown
#note that self.friend = anvil.server.call('get_friend') which will pass the logged in user/owner of record
self.currentfriend=self.friend.search()[0]

def drop_down_bestfriend_change (self, **event_args):
    # This method is called when an item is selected
    #based on the dropdown(s) selected get or search the record you want in database. 
    #If search gets you more records then 1 you can temporarily pick [0] (first row) 
    #until you figure out how to get exact row or loop through.
    
    #getting my records that I will insert or update 
    self.friend_to_add=app_data.people.search(...something from a dropdown) #search or get the record of the best friend

    if not self.currentfriend:
      self.currentfriend=self.friend.add_row(Mybestfriend=self.friend_to_add)
    else:
      self.currentfriend.update(Mybestfriend=self.friend_to_add)
    pass
5

Glad you got it working.

Just for anyone else reading this in the future, these two update methods are generally interchangeable, whether updating a table value directly or updating a link to another table :

# Fetch a row.
row = app_tables.tableA.get(name="dave")

# Update method 1
row.update(name="fred")

# Update method 2
row['name']="fred"
1 Like
6

Quick note on this code:

    #getting my records that I will insert or update 
    self.friend_to_add=app_data.people.search(...something from a dropdown) #search or get the record of the best friend

    if not self.currentfriend:
      # ...

If you want a single row, you probably want to use app_data.people.get(...) rather than search(...). The get() function returns a single matching row, or None if there are no matching rows match (or throws an exception if more than one row matches).

search() returns an iterator, so checking how many rows were returned requires calling len(). Then you’d need to pull out the first row explicitly (eg best_friend = rows[0]). It’s entirely possible; it’s just faster with get().

1 Like
7

what if you want the row update to be a variable?
row[bestfriend]=besty
Where bestfriend value can change.

8

Hi John, and welcome to the forum!

Yes, you can use a variable to select and update a specific column from a Data Table row.

Here’s a simple app that demonstrates how this might work:

https://anvil.works/build#clone:L4TGD4LUWGDTG76W=N2ARZYQUNV4GTRMHCAZ4PTPI

My People Data Table has two columns (‘bestfriend’, and ‘colleague’) that link to my second Data Table, BFFs.

I use dropdowns to decide which column in my People Data Table to update, ‘bestfriend’ or ‘colleague’:

The first dropdown self.dropdown_1 is a row from the People Data Table.

The second dropdown, self.dropdown_2.items, has two options: ‘bestfriend’ or ‘colleague’. The selected string is used as a variable which will update either the ‘bestfriend’ or the ‘colleague’ column in the People Data Table for the person selected in the first dropdown.

Let me know if you have any questions

9

Bridget,

Thank you for your response.

I am trying to update a variable column in lieu of a specific column.

For example, I have a data table with columns 1, 2, 3, 4, ….50.

Each week I have lottery numbers 2, 10, 19, 28, 32, 50 that need to be logged into by data table.

So I am trying to run something like:


def load_num ():
  gap_data = app_tables.gap.search()
  hl_data = app_tables.hl.search()
  for row in gap_data:
    cur_date = row['Draw_Date']
    cur_hl_row = app_tables.hl.get(Draw_Date=cur_date)
    cur_gap_row=row
    ball1 = cur_hl_row['Ball1']
    ball2 = cur_hl_row['Ball2']
    ball3 = cur_hl_row['Ball3']
    ball4 = cur_hl_row['Ball4']
    ball5 = cur_hl_row['Ball5']
    ball6 = cur_hl_row['Ball6']
    row.update(ball1=1, ball2=1,ball3=1,ball4=1,ball5=1,ball6=1)

My goal is to assign a value of 1 to the column number, in this case 2, for the week (Row[]) that is currently being iterated.

When I do this I get an error message stating that there is no column ‘ball1’.

I don’t want to add a new column. Rather, I want to add this to column ball1=2, ball2=10, etc.

Any suggestions would be appreciated.

Thanks,

John

10

@john.ovall just a quick tip to make it easier to read code on the forum (and thus more likely to receive assistance).

You can format your code nicely by wrapping your code block with triple backticks. For example:

```python
print(‘nicely formatted code’)

```

It will look like this:

print('nicely formatted code')
1 Like
11

Hi John,

Let me state your question in my own words to check I’ve understood:

You have a table of ball numbers with columns Draw_Date, Ball1, Ball2, Ball3, Ball4, Ball5, Ball6. It’s called hl.

You have a table with a Draw_Date column and one column per possible number (1 to 50 inclusive). It’s called gap.

For each row of hl, you want to create a row of gap that has a 1 in the columns that were drawn.

If you change this line:

    row.update(ball1=1, ball2=1,ball3=1,ball4=1,ball5=1,ball6=1)

to be

    kwargs = {ball1: 1, ball2: 1, ball3: 1, ball4: 1, ball5: 1, ball6: 1}
    row.update(**kwargs)

then it should work. The ** operator unpacks the dictionary into keyword arguments, which allows you to use variables as argument names (here’s more on that from pythontips.com).

I made a quick example of my own to illustrate this. It generates a lottery draw at random - 3 balls that can be 1 to 8 inclusive.

I found I needed to do this to update the gap table, because you can get two balls of the same number, so you might need to store 2 instead of 1:

      # Add 1 to each `draw` column.
      row[str(draw1)] += 1
      row[str(draw2)] += 1
      row[str(draw3)] += 1

Click this link to get a clone of the app:

https://anvil.works/build#clone:VXORGPZEMRMSLYQ5=7WRYKYSITPIB2YLUORD7B4X6

Here’s a GIF of it:

lottery-draw
lottery-draw.gif1170×649 41.8 KB

One more tip: to optimise performance, I would do as many of your Data Tables operations as possible in a single Server Module function, and try to make as few Data Tables calls as you can. Each time you make a Data Tables call, you have to wait for the latency to the database, so reducing the number of round-trips is a good idea (this is true for any database system, not just Anvil’s Data Tables.) In my example I didn’t optimise this, I optimised for clarity of code instead!

Hope that helps!

3 Likes
12

This was perfect. Ran exactly the way intended.

Thanks so much for taking time to help out.

3 Likes