Example: Multiple Tables

In this example, I show how doctable can be used with multiple inter-related 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 original doctable or ConnectEngine constructor. Otherwise sqlalchemy will not enable.

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

In this example I'll create interrelated tables for authors and their books.

Joint Select Statements

You can perform joins by using select queries with column objects from different tables.

Cascade deletion

See now that by deleting the author "Devin Cornell", we also removed the corresponding rows in the book table.