Example: Multiple Tables

In this example, I show how doctable can be used with multiple relational tables to perform queries which automatically merge different aspects of your dataset when you use .select(). By integrating these relations into the schema, your database can automatically maintain consistency between tables by deleting irrelevant elements when their relations disappear. There are two important features of any multi-table schema using doctable:

(1) Set the foreign_keys=True in the DocTable or ConnectEngine constructor. It is enabled by default. Otherwise sqlalchemy will not enable.

(2) Use the "foreignkey" column type to set the constraint, probably with the onupdate and ondelete keywords specifiied.

I will show two examples here: many-to-many relations, and many-to-one relations.

Many-to-Many Relationships

The premise is that we have an imaginary API where we can get newly released books along with the libraries they are associted with (although they man, in some cases, not have library information). We want to keep track of the set of books with unique titles, and have book information exist on its own (i.e. we can insert book information if it does not have library information). We would also like to keep track of the libraries they belong to. We need this schema to be fast for selection, but it can be slow for insertion.

Primary accesses methods:

In this example, we are going to use two tables with a many-to-many relationships and a table to handle relationships between them (required for a many-to-many relationship):

First we define the BookTable table. Because we are primarily interested in books, we will create a separate Book object for working with them.

We are not planning to work with author data outside of the schema definition, so we include it as part of the table definition.

Now we create some random books that are not at libraries and add them into our database.

Now we insert the list of books that were published. It works as expected.

And now lets add a bunch of books that are associated with library objects.

Select Queries That Join Tables

Similar to sqlalchemy, DocTable joins are doen simply by replacing the where conditional. While not technically nessecary, typically you will be joining tables on foreign key columns because it is much faster.

For the first example, say we want to get the isbn numbers of books associated with each library in zip code 12345. We implement the join using a simple conditional equating the associated keys in each table. Our database schema already knows that the foreign keys are in place, so this expression will give us the join we want.

Now say we want to characterize each library according to the age distribution of it's books. We use two conditionals for the join: one connecting library table to relations table, and another connecting relations table to books table. We also include the condition to get only libraries associated with the given zip.

Alternatively we can use the .join method of doctable (although I recommend just using select statements).

Many-to-One Relationships

Now we create an author class and table to demonstrate a many-to-one relationship.