Storing Data for your app

Every Anvil app comes with a built-in database. Each database 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 a data table to your app’s Default database, open the database window from the Sidebar Menu. Then click + Add Table button. Check the Quickstart to see how to get up and running.

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. Check the Quickstart to see how to get up and running.

Editing Data Tables in the IDE

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 its name in the data tables list on the left.

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).

Column types

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 str
• Number - Any Python number
• True/False - A Python boolean, True or False
• Date - A Python datetime.date
• Date and Time - A Python datetime.datetime
• Simple Object - Can hold Python strings, numbers (not NaN), 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 dicts representing each column in your Data Table. Each dict contains a name and type key. For example:

# list_columns() returns a list of dicts, like this:
[
  {'type': 'liveObject', 'name': 'category'}, 
  {'type': 'string', 'name': 'content'}, 
  {'type': 'datetime', 'name': 'created'}, 
  {'type': 'media', 'name': 'image'}
]

Using Data Tables from Python

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 add_row() method.

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" and "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(f"Zaphod is currently {zaphod_row['age']} years old")

# 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).

The 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 list() function.

For a full explanation of how to use Data Tables in Python, see Using Data Tables from Python.

Sharing Data Tables Between Apps

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.

Any changes you make to a shared Data Table will also be reflected in the other apps that use it.

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.

Downloading a Data Table as CSV

Do you still have questions?

Our Community Forum is full of helpful information and Anvil experts.