The Data Tables service stores tables of information for your Anvil app. It’s a full database system built on top of PostgreSQL.
If you’re not familiar with databases, it’s also similar to a spreadsheet - data is stored in tables with rows and columns.
To add Data Tables to your app, first add the Data Tables service in the App Browser.
From the Data Tables page, you can create a new, empty table, or you can add tables from any of your other apps.
Each table has its own columns. For example, if we are making a table of people for our organisation, we might have columns for name, age and employment status. Each row in the table represents an item - in this case, a person.
Here’s an example table as it looks in the app editor:
Each column can contain data of a particular type. For example, in the table above, Name is always a string, Age is always
a number, and Employed is always a boolean value (either True or False). Any column can also be empty (
None in Python).
Click to edit any data in the table (just like a spreadsheet). To add a new row, click the ‘+’ button at the bottom of the table, or click in the blank row, and start typing! You can add more columns with the ‘+’ button at the top right of the table.
To change a table’s title, click on it (in the tabs at the top). Each table also has a Python name you can use to refer to it in your code. When you first create a table, the Python name for your table will match the Table’s title. If you change the title, the Python name will change to match the new title. You can also change the Python name in the IDE - by doing so, you can give your table a different Python name from its title.
If you delete your app, its tables will be deleted as well (if no other apps are using it).
All columns of a data table have fixed types. You choose the column type when it is created or, if it does not already exist, when a value is first set from code using
add_row. The available types are listed below, along with their corresponding python types.
- Text - A Python
- Number - Any Python number
- True/False - A Python boolean,
- Date - A Python
- Date and Time - A Python
- Simple Object - Can hold Python strings, numbers, dicts or lists
- Media - Binary data (a Media object)
- Link - A row (or list of rows) from another table
You can list all the columns in a Data Table by calling
list_columns() on the table object. This returns a list of strings.
There are Python methods to add rows to your table, search your table, update rows and delete them. These are available in client code as well as in Server Modules (you can configure access to your Data Tables by setting table permissions).
You can add a new row to a data table with the
Use keyword arguments to specify the value for each column. If you name a column that does not exist, a new column will be created.
from anvil.tables import app_tables # Add a new row and return a Row object that represents it row = app_tables.people.add_row(Name="Jane Smith", Age=7, Employed=False)
Remember that in Python,
"name" are two different strings, and they will create two different columns if you mix them up.
In Python, your table is represented by
Row Objects. These behave like Python dictionaries, allowing you
to get and set their values as you would for a Python dictionary. For example:
# Get the Row object whose 'name' value is Zaphod Beeblebrox zaphod_row = app_tables.people.get(name="Zaphod Beeblebrox") # Get Zaphod's 'age', and print it print("Zaphod is currently %s years old" % zaphod_row['age']) # It's Zaphod's birthday, update the database zaphod_row['age'] += 1
Multiple Rows are represented by
SearchIterators. You can get the entire contents of a table like this:
entire_table = app_tables.people.search()
This returns an object you can iterate over to get each Row in the table:
for row in app_tables.people.search(): print(row["age"])
The advantage of this over simply listing the Rows is that the
SearchIterator is lazy - it only loads data as the data is needed, making your app more efficient (you can cast
SearchIterators to lists and Rows to dicts if you want to).
search() method returns an iterable Python object, not a list. This is so you don’t have to fetch all the rows from your table before you can start processing them. If you want a list, pass the search result to Python’s
For a full explanation of how to use Data Tables in Python, see Using Data Tables from Python.
Data Tables can be shared between apps. When you click the ‘+ Add a table’ or ‘+’ button to add a new Data Table, you will be given a list of existing tables from your other apps which you can add to the current app.
Table permissions are on a per-app basis: if you make a table client-writable in one app, other apps that use it will not be affected. The ‘Python name’ of a table is also per-app.
A table that is included in several apps will only be deleted when it is removed from all apps that use it.