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
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.
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?
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.
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 to the bottom of your Data Grid.
Drop a TextBox into each of the Name and Quantity columns. Rename them
text_box_quantity. Set the Quantity TextBox’s
Drop a Button into the end column. Rename it
button_add. Clear the Button’s
text and set its
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
@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.
Set the Quantity TextBox to be type
These TextBoxes are going to display the values we read from the database when we
Since we are inside a Repeating Panel, we can access the values for each row as
(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’.
Now go to the code 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
lost_focus events of each TextBox:
Now run your app. You’ll find your table is full of TextBoxes that update the database every time they are modified.
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
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.
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.
You can do a lot more using the Uplink
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: