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.

In [1]:
import sys
sys.path.append('..')
import doctable
In [2]:
class Authors(doctable.DocTable):
    __tabname__ = 'authors'
    __schema__ = (
        ('idcol', 'id'),
        ('string', 'name', dict(unique=True)),
        ('string', 'fav_color'),
        ('date_updated', 'updated'),
    )
adb = Authors(target=':memory:', foreign_keys=True)
#adb.execute('pragma foreign_keys=ON')
adb
Out[2]:
<DocTable::sqlite:///:memory::authors ct: 0>
In [3]:
class Books(doctable.DocTable):
    __tabname__ = 'books'
    __schema__ = (
        ('idcol', 'id'), # each book has its own id
        ('string', 'title'),
        
        # reference to authors table
        ('integer', 'authname'), 
        ('foreignkey', 'authname', 'authors.name', dict(onupdate="CASCADE", ondelete="CASCADE")),
        
        # make unique combination
        ('index', 'ind_authtitle', ['title', 'authname'], dict(unique=True)),
    )
bdb = Books(engine=adb.engine)
bdb
Out[3]:
<DocTable::sqlite:///:memory::books ct: 0>
In [4]:
# see that both are registered with the engine metadata
adb.engine.tables.keys()
Out[4]:
dict_keys(['authors', 'books'])
In [5]:
# define a test dataset
collection = (
    ('Devin Cornell', 'green', 'The Case of Austerity'),
    ('Devin Cornell', 'green', 'Gender Stereotypes'),
    ('Devin Cornell', 'green', 'Colombian Politics'),
    ('Pierre Bourdieu', 'orange', 'Distinction'),
    ('Pierre Bourdieu', 'orange', 'Symbolic Power'),
    ('Jean-Luc Picard', 'red', 'Enterprise Stories'),
)
In [6]:
for auth, color, title in collection:
    adb.insert({'name':auth, 'fav_color': color}, ifnotunique='ignore')
    bdb.insert({'authname':auth, 'title': title}, ifnotunique='ignore')
adb.count(), bdb.count()
Out[6]:
(3, 6)
In [7]:
adb.head()
Out[7]:
id name fav_color updated
0 1 Devin Cornell green 2020-06-20 23:14:43.460658
1 2 Pierre Bourdieu orange 2020-06-20 23:14:43.462758
2 3 Jean-Luc Picard red 2020-06-20 23:14:43.463840
In [8]:
bdb.head(10)
Out[8]:
id title authname
0 1 The Case of Austerity Devin Cornell
1 2 Gender Stereotypes Devin Cornell
2 3 Colombian Politics Devin Cornell
3 4 Distinction Pierre Bourdieu
4 5 Symbolic Power Pierre Bourdieu
5 6 Enterprise Stories Jean-Luc Picard

Joint Select Statements

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

In [9]:
# this is a left join
bdb.select(['title', adb['name'], adb['fav_color']], where=bdb['authname']==adb['name'])
Out[9]:
[('Colombian Politics', 'Devin Cornell', 'green'),
 ('Gender Stereotypes', 'Devin Cornell', 'green'),
 ('The Case of Austerity', 'Devin Cornell', 'green'),
 ('Distinction', 'Pierre Bourdieu', 'orange'),
 ('Symbolic Power', 'Pierre Bourdieu', 'orange'),
 ('Enterprise Stories', 'Jean-Luc Picard', 'red')]
In [10]:
# with tables reversed, still returns same output
adb.select(['name', bdb['title']], where=adb['name']==bdb['authname'])
Out[10]:
[('Devin Cornell', 'Colombian Politics'),
 ('Devin Cornell', 'Gender Stereotypes'),
 ('Devin Cornell', 'The Case of Austerity'),
 ('Jean-Luc Picard', 'Enterprise Stories'),
 ('Pierre Bourdieu', 'Distinction'),
 ('Pierre Bourdieu', 'Symbolic Power')]

Cascade deletion

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

In [12]:
adb.delete(where=adb['name']=='Devin Cornell')
Out[12]:
<sqlalchemy.engine.result.ResultProxy at 0x7f68b8d59f60>
In [13]:
adb.head()
Out[13]:
id name fav_color updated
0 2 Pierre Bourdieu orange 2020-06-20 23:14:43.462758
1 3 Jean-Luc Picard red 2020-06-20 23:14:43.463840
In [14]:
bdb.head(10)
Out[14]:
id title authname
0 4 Distinction Pierre Bourdieu
1 5 Symbolic Power Pierre Bourdieu
2 6 Enterprise Stories Jean-Luc Picard