Using Data Tables from Python

Anvil represents Data Tables in code as Python objects. You can imagine a Data Table in Python as a list of dictionaries, although they have many extra methods as described on this page, and they have caching and lazy loading for efficiency.

Client-side code can access Data Tables in this way, if you give it permission to do so. This allows you to use one representation for your data from the database all the way to the UI.

Adding Data

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

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.

Data Table rows cache their data. This cache doesn’t automatically update if other code modifies that table row. If you want to refresh the data in a row object, call row.update(). However, if you pass a data table row into a server function, and update it there, then its cache will be automatically updated (this makes things like “call a server function to apply changes to this table row” work).

Row Objects

In Python, your table is represented by Row Objects. Row Objects behave like Python dictionaries. You can get, set and update values in Rows using square brackets.

The add_row() method returns a row object, which you can use to get or set column values:

james_row = app_tables.people.add_row(name="James Smith")
james_row["age"] = 12

Using square brackets on Row objects reads and writes to the database, with built-in caching:

zaphod_row = app_tables.people.get(name="Zaphod Beeblebrox")
print(f"Zaphod is currently {zaphod_row['age']} years old")

# It's Zaphod's birthday, update the database
zaphod_row['age'] += 1

Searching (querying) a Table

You can list the contents of a table with the search() method.

for row in app_tables.people.search():
  print(f"{row['name']} is {row['age']} years old")

Use keyword arguments to search for a particular value in a particular column.

people_called_dave = app_tables.people.search(name="Dave")

The return value is a SearchIterator, an iterator of Row objects. You can think of a SearchIterator like a list, and since the Row objects are like dictionaries, the whole structure behaves like a list of dictionaries.

# It can be useful to imagine SearchIterators like this:
[
  {"name": "Dave", "age": 34},
  {"name": "James Smith", "age": 12},
  {"name": "Zaphod Beeblebrox", "age": 42},
]

Getting values from a single column

You can use Python list comprehension to get the values from a particular column in your Data Table:

# Get a list of values from the 'name' column of your 'people' Data Table:
names = [r['name'] for r in app_tables.people.search()]

Getting values from several columns

To get the values from several columns in your Data Table, you can use a list comprehension to return a list of dictionaries with the desired values:

info = [
  {
    'name': r['name'],
    'employed': r['employed'],
  }
  for r in app_tables.people.search()
]

Query operators

Use the Query Operators from the anvil.tables.query module to perform more complex searches.

import anvil.tables.query as q

people_over_50 = app_tables.people.search(age=q.greater_than(50))

from datetime import date
late_projects = app_tables.projects.search(
  Complete=False,
  Due_Date=q.less_than(date.today())
)

The following query functions are available (here’s the relevant API Docs entry). These can only be used as values of keyword arguments to match column values:

• between(min, max, [min_inclusive=True], [max_inclusive=False]) - Match values between min and max. By default, min is inclusive and max is exclusive. This can be changed by supplying alternative values as keyword arguments.

• full_text_match(pattern, [raw=False]) - By default, matches values which contain all the words in pattern, ignoring the particular form of the word (e.g. ‘Walk’, ‘walk’, ‘walked’ and ‘walking’ will all match one another). For more control, set raw to True, then provide a full PostgreSQL tsquery pattern.
  
  The match function ignores ‘stop words’: certain words that appear commonly in text, and therefore usually cloud the results of a natural language search ¹. If you need to search for a string containing stop words, consider using like or ilike instead. These are intended for a literal text match, whereas full_text_match is intended for a natural language query.

• greater_than(value) - Matches any values greater than value.

• greater_than_or_equal_to(value) - Matches any values greater than or equal to value.

• ilike(pattern) - Provides case-insensitive string matching using the PostgreSQL pattern-matching syntax.

• less_than(value) - Matches any values less than value.

• less_than_or_equal_to(value) - Matches any values less than or equal to value.

• like(pattern) - Provides case-sensitive string matching using the PostgreSQL pattern-matching syntax.

See Querying Data Tables for more examples.

Combining query operators

The any_of, all_of and none_of functions let you build up arbitrary expressions. When used as values for keyword arguments, the arguments to these functions should also be values, as in the example below.

# Find anyone called Dave or Brian
x = app_tables.people.search(name=q.any_of("Dave", "Brian"))

# Find anyone not called Steve or John
x = app_tables.people.search(name=q.none_of("Steve", "John"))

Alternatively, when used as positional arguments, the any_of, all_of and none_of functions allow you to build up arbitrarily complex nested search expressions.

# Find anyone called Dave *or* 35 years old.
y = app_tables.people.search(q.any_of(name="Dave", age=35))

# Find anyone less than 20 or more than 30 years old
x = app_tables.people.search(
  age=q.any_of(q.less_than(20), q.greater_than(30))
)

# Find anyone who is 35 years old, or 34 and called Dave or Brian
z = app_tables.people.search(
  q.any_of(
    age=35,
    q.all_of(age=34, name=q.any_of("Dave", "Brian")),
  )
)

Sorting and slicing

Use tables.order_by() to sort the rows returned. This takes the name of the column to sort by, and a keyword argument ascending, which defaults to True.

oldest_first = app_tables.people.search(
  tables.order_by("age", ascending=False)
)

You can also use tables.order_by() to sort by multiple columns. This sorts by “name” in ascending order, and rows that have the same value for “name” will be sorted by “age” in descending order:

oldest_first = app_tables.people.search(
  tables.order_by("name"),
  tables.order_by("age", ascending=False)
)

Slice a table search to return just a portion of the results.

oldest_first = app_tables.people.search(
  tables.order_by("age", ascending=False)
)
ten_oldest = oldest_first[:10]

Unpacking a dictionary

You can also search a table by unpacking a dictionary of arguments.

kwargs = {'age': 42, 'employed': True}
filtered_table = app_tables.people.search(**kwargs)

This can be used in conjunction with a query operator.

kwargs = {'age': 42, 'employed': True}
filtered_table = app_tables.people.search(q.any_of(**kwargs))

Search is lazy

search() returns a search object, which is iterable - you can use it in a for loop or a list comprehension. If your search returns lots of results, Anvil only loads a few at a time.

You can call len() on a search object to efficiently find out how many rows it would return (without loading them all!).

print(f"There are {len(app_tables.people.search())} people")

You can also access the returned rows by index. Because search() is sensibly lazy, it will only load as many results as is necessary to retrieve the value at a given index. If your search returns lots of results, Anvil only loads a few at a time.

first_person = app_tables.people.search(tables.order_by("Name"))[0]

print(f"First alphabetical name: {first_person['name']}")

You can use Python’s normal sequence operations to work with search results.

# Print the names of all the adults:

adult_names = " and ".join([p['name'] for p in app_tables.people.search(age=q.greater_than_or_equal_to(18))])

print(adult_names)
# ^^ Prints "Tom and Dick and Harry"

Searching Simple Objects

You can think of a Simple Object column as storing a JSON object. A Simple Object column can store strings, numbers (not NaN), None, lists of any simple object, and dictionaries whose keys are strings and whose values are any simple object. (In other words, it’s the values that can be represented as JSON.)

You can search within Simple Objects using search:

# This finds all rows where object_col is a
# dict and contains a key 'the_answer' with
# the value '42' - no matter what other keys
# are in the dict.
r = app_tables.my_table.search(object_col={'the_answer': 42})

When you search on a Simple Object column, you search by object specifying an object pattern. The rules are:

• If you specify a dictionary, all specified keys must be present in the column, and all values must match the pattern specified in the dictionary.
• If you specify a list, the object you’re matching must be a list, and it must contain elements matching all elements you specify. Order doesn’t matter, though, so if you specify [1,3] as your search you will match a column containing [3,2,1].
• If you specify a string, number or None (a “primitive value”), the object you’re matching must be exactly the same, or be a list containing that primitive value.

Getting One Row from a Table

Sometimes, you only want a single row. The get() method returns a single row that matches the arguments (which are the same as those for the search method), or None if no such row exists. If more than one row matches, it raises an exception.

zaphod_row = app_tables.people.get(name="Zaphod Beeblebrox")

You can use this neat trick to fetch a row if it exists, or create a new one if it does not. Because get() returns None if a row does not exist, we use “short circuiting” of the or operator to make sure we only run the add_row() if no such row already exists.

zaphod_row = (app_tables.people.get(name="Zaphod Beeblebrox")
               or app_tables.people.add_row(name="Zaphod Beeblebrox", age=42))

Row IDs

Each row of each table has unique ID, which is a string. You can get the ID of any row, and use it to retrieve that row later.

# 'jane_smith' is a Row object from the 'people' table
janes_id = jane_smith.get_id()

r = app_tables.people.get_by_id(janes_id)

# r and jane_smith are equal, as they both
# point to the same row.
# (jane_smith == r)

This is useful if you want to store a reference to a table row somewhere else (for example, in a file).

Updating and Deleting Rows

You can set new values for a row in the same way as you would update a Python dictionary. This updates the database, not just the data on the client side.

jane_row = app_tables.people.get(name="Jane Smith"):
# Increase Jane Smith's age (from 7 to 8)
jane_row["age"] += 1

You can set the value of multiple columns at once by calling the update() method on a row. Again, this updates the database.

# Set Jane Smith's age to 9 and her middle initial
jane_row.update(age=9, name="Jane E. Smith")

You can also delete a row by calling its delete() method. The row is removed from the database.

# Remove Jane Smith from the table
jane_row = app_tables.people.get(name="Jane E. Smith"):
if jane_row is not None:
  jane_row.delete()

You can delete all rows in a table by calling the table’s delete_all_rows() method. All data is cleared from the database for this table!

# Delete all rows in the table
app_tables.people.delete_all_rows()

CSV Export

You can call to_csv() to obtain a downloadable Media object from any search(). You can access the url property of this Media Object to provide a link to download that data.

self.link_1.url = app_tables.people.search().to_csv().url

You can also call to_csv() on table views. This can be used to provide downloads of only certain columns of a data table. In this example, the person column will not be included in the download.

janes_notes = app_tables.notes.client_readable(person=jane_smith)
self.link_1.url = janes_notes.search().to_csv().url

Do you still have questions?

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