Live Chat

We'll need to share your messages (and your email address if you're logged in) with our live chat provider, Drift. Here's their privacy policy.

If you don't want to do this, you can email us instead at contact@anvil.works.

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.

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("Zaphod is currently %s years old" % zaphod_row['age'])

# 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("%s is %d years old" % (row["Name"], row["Age"]))

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},
]

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 1. 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_search 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 on the right.

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

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]

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("There are %d people" % len(app_tables.people.search()))

If you want to access the returned rows by index, you can use the list() function to turn a search object into a Python list. Be careful when doing this with searches that return large numbers of rows - it will load them all and return the full list.

# Make a Python list from this search
people = list(app_tables.people.search(tables.order_by("Name")))

first_person = people[0]
print("First alphabetical name: %s" % first_person['Name'])

A safer and faster way to do the same thing would be to iterate through the results, but break after the first.

for person in app_tables.people.search(tables.order_by("Name")):
  break
else:
  print("No people found")

print("First alphabetical name: %s" % 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, 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.

(The Simple Object search semantics are derived directly from the Postgres JSON containment rules.)

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

If you just want to refer to one row in a data table from another row in a data table, don’t mess with IDs – use link columns instead!

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.

# Increase Jane Smith's age (from 7 to 8)
jane_row = app_tables.people.get(Name="Jane Smith"):
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
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

If you want to provide a download from a data table, you can call to_csv() to obtain a downloadable Media object from any search().

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

  1. Full list of stop words: i, me, my, myself, we, our, ours, ourselves, you, your, yours, yourself, yourselves, he, him, his, himself, she, her, hers, herself, it, its, itself, they, them, their, theirs, themselves, what, which, who, whom, this, that, these, those, am, is, are, was, were, be, been, being, have, has, had, having, do, does, did, doing, a, an, the, and, but, if, or, because, as, until, while, of, at, by, for, with, about, against, between, into, through, during, before, after, above, below, to, from, up, down, in, out, on, off, over, under, again, further, then, once, here, there, when, where, why, how, all, any, both, each, few, more, most, other, some, such, no, nor, not, only, own, same, so, than, too, very, s, t, can, will, just, don, should, now [return]