Table Relationship

Creating a relation between Tables

The Database also manages the relations between Tables. This way, a Field can refer to another Table; this Field actually contains the index of a row instance in that Table.

To create a relationship between Tables, the first Table must be created, then specified as Field type when creating the second Table:

# Create the first Table
db.create_table(table_name='Stats',
                fields=[('mean', float, 0.), ('max', float, 0.)])
db.create_table(table_name='Arrays',
                fields=[('array', ndarray), ('stats', 'Stats')])
db.print_architecture()
"""
>> DATABASE database.db
     * StoringTable 'Stats'
        - id (AUTO) (default)
        - mean (FLOAT)
        - max (FLOAT)
     * StoringTable 'Arrays'
        - id (AUTO) (default)
        - array (NUMPY)
        - stats (FK -> Stats)
"""

Adding data to joined Tables

Adding data to joined Tables can be done independently, but you may find it more convenient to do that only using the main Table. Data can still be added either line by line either per batch of lines:

new_array = np.random.uniform(size=(10,))

# Add a single line to Tables independently
id_stats_line = db.add_data(table_name='Stats',
                            data={'mean': new_array.mean(),
                                  'max':  new_array.max()})
db.add_data(table_name='Arrays',
            data={'array': new_array,
                  'stats': id_stats_line})

# Add a single line to Tables using the main Table
db.add_data(table_name='Arrays',
            data={'array': new_array,
                  'stats': {'mean': new_array.mean(),
                            'max':  new_array.max()}})

new_arrays = [np.random.uniform(size=(10,)) for _ in range(5)]

# Add a batch to Tables independently
id_stats_lines = db.add_batch(table_name='Stats',
                              data={'mean': [new_array.mean() for new_array in new_arrays],
                                    'max':  [new_array.max() for new_array in new_arrays]})
db.add_batch(table_name='Arrays',
             batch={'array': new_arrays,
                    'stats': id_stats_lines})

# Add a batch to Tables using th main Table
db.add_batch(table_name='Arrays',
             batch={'array': new_arrays,
                    'stats': {'mean': [new_array.mean() for new_array in new_arrays],
                              'max':  [new_array.max()  for new_array in new_arrays]}})

Updating data in joined Tables

Updating data in joined Tables can be done independently, but you may find it more convenient to do that only using the main Table. Data updates can only be performed line by line:

new_array = np.random.uniform(size=(10,))

# Update a line in Tables independently
array_data = db.get_line(table_name='Arrays',
                         line_id=1,
                         fields='stats')
db.update(table_name='Arrays',
          line_id=1,
          data={'array': new_array})
db.update(table_name='Stats',
          line_id=array_data['stats'],
          data={'mean': new_array.mean(),
                'max':  new_array.max()})

# Update a line in Tables using the main Table
db.update(table_name='Arrays',
          line_id=1,
          data={'array': new_array,
                'stats': {'mean': new_array.mean(),
                          'max':  new_array.max()}})

Getting data from joined Tables

Getting data from joined Tables can be done independently, but you may find it more convenient to do that only using the main Table. Data can still be accessed either line by line either per batch of lines:

# Get a single line from Tables independently
array_data = db.get_line(table_name='Arrays',
                         line_id=1)
stats_data = db.get_line(table_name='Stats',
                         line_id=array_data['stats'])
"""
>> array_data = {'id': 1,
                 'array': array(...),
                 'stats': 1}
   stats_data = {'id': 1,
                 'mean': 0.23,
                 'max': 0.41}
"""

# Get a single line from Tables using the main Table
array_data = db.get_line(table_name='Arrays',
                         line_id=1,
                         joins='Stats')
"""
>> array_data = {'id': 1,
                 'array': array(...),
                 'stats': {'id': 1,
                           'mean': 0.23,
                           'max': 0.41}}
"""

# Get a batch from Tables independently
array_data = db.get_lines(table_name='Arrays',
                          lines_range=[1, 3],
                          batched=True)
stats_data = db.get_lines(table_name='Stats',
                          lines_id=array_data['stats'],
                          batched=True)
"""
>> array_data = {'id': [1, 2, 3],
                 'array': [array(...), array(...), array(...)]
                 'stats': [1, 2, 3]}
   stats_data = {'id': [1, 2, 3],
                 'mean': [0.23, 0.56, 0.47],
                 'max': [0.41, 0.82, 0.64]}
"""

# Get a batch from Tables using the main Table
array_data = db.get_lines(table_name='Arrays',
                          lines_range=[1, 3],
                          batched=True)
"""
>> array_data = {'id': [1, 2, 3],
                 'array': [array(...), array(...), array(...)]
                 'stats': {'id': [1, 2, 3],
                           'mean': [0.23, 0.56, 0.47],
                           'max': [0.41, 0.82, 0.64]}}
"""