Chapter 3:
Add, Edit and Delete your items from your app

You’ve successfully connected to your external database from a web app! That’s the core mission of this tutorial accomplished.

We’ll go a little further by building a UI to edit the contents of the inventory table. We’ll also create some input components to add and delete rows.

Step 1: Create a 'refresh' method

We want to be able to refresh the table at any time, so we can show the user what happens when they create or update rows.

Here’s the line we’ve been using to fetch the inventory items from the database:

    self.repeating_panel_1.items = anvil.server.call('get_items')

Remove this line from __init__ and put it in a method of its own:

  def refresh(self, **event_args):
    self.repeating_panel_inventory.items = anvil.server.call('get_items')

Now call your new method from __init__:

  def __init__(self, **properties):
    # Set Form properties and Data Bindings.
    self.init_components(**properties)

    # Any code you write here will run when the form opens.
    self.refresh()

Let’s also create an event to trigger a refresh. That makes it easier to trigger a refresh from outside this Form.

At the end of __init__, add the following line to bind an event that triggers a refresh:

    # At the end of __init__:
    self.set_event_handler('x-refresh', self.refresh)

Re-run your app: you should still see your database rows displayed in the table.

Step 2: Write a function to add items

Let’s make our app add items to the database. We’ll start with the back-end.

In inventory.py, add this simple function to run an INSERT statement against the external database:

@anvil.server.callable
def insert_item(name, quantity):
  cur.execute('''
    INSERT INTO inventory (item_name, quantity)
      VALUES (%s, %s);
  ''',
  [name, quantity])

Why don’t we just use Python format strings to insert the values?

Psycopg2’s cursor.execute method allows you to construct queries from Python objects in a way that protects against SQL injection. That prevents users from taking over our database by using SQL statements as variables.

Read more in the Psycopg2 docs.

Step 3: Build the UI to add items

Now we need to run that insert_item function from the web app. Let’s build an ‘add’ widget into the Data Grid.

Add a column to your Data Grid. Clear its Title and Key. Set its width to 80. This will hold our ‘add’ button later.

Add a Data Row Panel Data Row Panel icon to the bottom of your Data Grid.

Drop a TextBox into each of the Name and Quantity columns. Rename them text_box_name and text_box_quantity. Set the Quantity TextBox’s type to number.

Drop a Button into the end column. Rename it button_add. Clear the Button’s text and set its icon to fa:plus.

Create a click handler by clicking the blue arrows to the right of ‘click’ in the Events section:

  def button_add_click(self, **event_args):
    anvil.server.call(
      'insert_item',
      self.text_box_name.text,
      self.text_box_quantity.text
    )

    # Refresh the open Form to load the new item into the UI
    get_open_form().raise_event('x-refresh')

    # Clear the input boxes
    self.text_box_name.text = ''
    self.text_box_quantity.text = ''

Now you can add items to your database from your web app!

Step 4: Write a function to update items

Next, we’ll update rows in the external database from the web app.

We need to write another function in inventory.py - this time we’ll do an UPDATE:

@anvil.server.callable
def update_item(item_id, name, quantity):
  cur.execute('''
     UPDATE inventory
       SET item_name = %s, quantity = %s
       WHERE id = %s;
  ''', 
  [name, quantity, item_id])

Step 5: Build the UI to update items

Now back to the Anvil Editor. We’ll modify each line of the table to contain input components so the user can change the items that already exist.

Double-click on the Repeating Panel in your Data Grid and drop TextBoxes into the Quantity and Name columns. Call them text_box_name and text_box_quantity respectively.

Set the Quantity TextBox to be type number.

These TextBoxes are going to display the values we read from the database when we refresh. Since we are inside a Repeating Panel, we can access the values for each row as self.item (see the docs on Repeating Panels for more information).

Set up a Data Binding for each TextBox to get the relevant value into its text property. Be sure to uncheck ‘Write back’.

Data Binding for ‘name’ TextBox

Data Binding for ‘name’ TextBox

Data Binding for ‘quantity’ TextBox

Data Binding for ‘quantity’ TextBox

Now go to the code for RowTemplate1.

Go to the Code View for RowTemplate1

Go to the Code View for RowTemplate1

Write a method to call when the values are changed:

  def update_item(self, **event_args):
    anvil.server.call(
      'update_item',
      self.item['id'],
      self.text_box_name.text,
      self.text_box_quantity.text,
    )

Bind this method to the pressed_enter and lost_focus events of each TextBox:

Bind the update_item method to the lost_focus and pressed_enter events by entering the method name in the boxes

Bind the update_item method to the lost_focus and pressed_enter events by entering the method name in the boxes

Now run your app. You’ll find your table is full of TextBoxes that update the database every time they are modified.

Updating the database from the web app.
The page refresh is triggered manually to demonstrate that the data has been persisted in the database.

Updating the database from the web app.
The page refresh is triggered manually to demonstrate that the data has been persisted in the database.

Step 6: Write a function to delete items

To delete items in the external database, we do exactly the same thing we’ve done for inserting, selecting and updating - write a one-line function in inventory.py with a simple SQL statement:

@anvil.server.callable
def delete_item(item_id):
  cur.execute('''
    DELETE FROM inventory WHERE id = %s;
  ''',
  [item_id])

Step 7: Build the UI to delete items

Double-click on the RepeatingPanel inside the Data Grid. Drag-and-drop a Button into the end column.

This will be the delete button for each item. Rename it button_delete. Clear the Button’s text, set its icon to fa:times and the foreground colour to theme:Secondary 500.

Adding a delete button to the end column of our Data Grid.

Adding a delete button to the end column of our Data Grid.

Now create a click event handler for your new delete button by clicking the blue arrows to the right of ‘click’ in the Events section:

  def button_delete_click(self, **event_args):
    """This method is called when the button is clicked"""
    anvil.server.call('delete_item', self.item['id'])
    get_open_form().raise_event('x-refresh')

Run your app - you can now delete items from your external database using your web app.

A cycle of deleting and re-adding an item in the database.

A cycle of deleting and re-adding an item in the database.

Nice work! You’ve not only connected your web app to an external database, you’ve also given the UI functionality to add, edit and delete items in that database.

Using an Uplink script to connect to systems outside of Anvil is very powerful. You can generalise this approach to solve a whole host of problems.

For example, you could use the Uplink to run machine learning models on your own cluster and return the results to Anvil (and use a Background Task to do this asynchronously). Or you could use an Uplink script as a gateway to communicate with IoT devices inside your network.

Anything you can do with Python, you can control with an Anvil app via the Uplink.

For a comprehensive view of what the Uplink can do, check out the Uplink section of the reference docs:

Congratulations, you've completed this chapter!