You can store a reference to one table in another table, using a link column. In this example, we have a
people table, and a
notes table containing notes about a person. The
notes table has a “Person” column, which links to a row from the
# Get Jane Smith's row from the table jane_smith = app_tables.people.get(Name="Jane Smith") # Add a row to the 'notes' table, referencing her n = app_tables.notes.add_row(Person=jane_smith, Text="Jane is a good kid") # Print the name of the person this note is linked to # (prints "Jane Smith") print(n["Person"]["Name"]) # Search by reference to a row. Eg, # How many notes do we have about Jane? janes_notes = app_tables.notes.search(Person=jane_smith) print("There are %d notes about Jane." % len(janes_notes))
Once we have a row object representing Jane Smith’s row in the
people table, we can add a new row to the
notes table that refers to her, by passing that row object into
add_row(). We can also search for all notes that refer to this person, by passing her row object into
Note that we use the
len() function here -
len() runs very efficiently over search iterators.
You can also create columns that link to multiple rows from another table. In this example, the “Friends” column links to multiple rows from the “People” table. We set it to a list of row objects.
zaphod = app_tables.people.get(Name="Zaphod Beeblebrox") zaphod["Friends"] = [jane_smith, john_smith]
You can search using a “link to multiple rows” column. If you pass a list of row objects to
search(), it will return only rows that link to all of those rows. (If you specify multiple rows, the order doesn’t matter - it matches anything that links to both of them.)
friends_of_jane = app_tables.people.search(Friends=[jane_smith]) mutual_friends = app_tables.people.search(Friends=[jane_smith, john_smith])
Values in Multiple Link columns are represented as lists in Python. Lists from Simple Objects are also Python lists.
To add a new item to a Multiple Link or a Simple Object, use the
zaphod = app_tables.people.get(Name="Zaphod Beeblebrox") ford_prefect = app_tables.people.get(Name="Ford Prefect") zaphod["Friends"] += [ford_prefect]
Ensure the right-hand-side of the operation is a list, since the Multiple Link column behaves like a list.
This will work:
zaphod["Friends"] += [ford_prefect]
This will raise an exception:
zaphod["Friends"] += ford_prefect
To remove rows, remove the item from the list and re-assign it using the
zaphod = app_tables.people.get(Name="Zaphod Beeblebrox") ford_prefect = app_tables.people.get(Name="Ford Prefect") zaphod["Friends"] = [r for r in zaphod['Friends'] if r != ford_prefect]