DocTable Examples: Insert and Delete

Here we show basics of inserting and deleting data into a doctable.

import random
import pandas as pd
import numpy as np

import sys
sys.path.append('..')
import doctable

import dataclasses
@doctable.schema
class Record:
    __slots__ = []
    id: int = doctable.IDCol()
    name: str = doctable.Col(nullable=False)
    age: int = None
    is_old: bool = None

def make_rows(N=3):
    rows = list()
    for i in range(N):
        age = random.random() # number in [0,1]
        is_old = age > 0.5
        yield {'name':'user_'+str(i), 'age':age, 'is_old':is_old}
    return rows

Basic Inserts

There are only two ways to insert: one at a time (pass single dict), or multiple at a time (pass sequence of dicts).

table = doctable.DocTable(target=':memory:', schema=Record, verbose=True)
for row in make_rows():
    table.insert(row)
table.select_df()

DocTable: INSERT OR FAIL INTO _documents_ (name, age, is_old) VALUES (?, ?, ?)
DocTable: INSERT OR FAIL INTO _documents_ (name, age, is_old) VALUES (?, ?, ?)
DocTable: INSERT OR FAIL INTO _documents_ (name, age, is_old) VALUES (?, ?, ?)
DocTable: SELECT _documents_.id, _documents_.name, _documents_.age, _documents_.is_old 
FROM _documents_

	id	name	age	is_old
0	1	user_0	0.485860	False
1	2	user_1	0.661900	True
2	3	user_2	0.082627	False

newrows = list(make_rows())
table.insert(newrows)
table.select_df(verbose=False)

	id	name	age	is_old
0	1	user_0	0.485860	False
1	2	user_1	0.661900	True
2	3	user_2	0.082627	False
3	4	user_0	0.936185	True
4	5	user_1	0.082005	False
5	6	user_2	0.567260	True

Deletes

# delete all entries where is_old is false
table.delete(where=~table['is_old'])
table.select_df(verbose=False)

DocTable: DELETE FROM _documents_ WHERE _documents_.is_old = 0

	id	name	age	is_old
0	2	user_1	0.661900	True
1	4	user_0	0.936185	True
2	6	user_2	0.567260	True

# use vacuum to free unused space now
table.delete(where=~table['is_old'], vacuum=True)
table.select_df(verbose=False)

DocTable: DELETE FROM _documents_ WHERE _documents_.is_old = 0
DocTable: VACUUM

	id	name	age	is_old
0	2	user_1	0.661900	True
1	4	user_0	0.936185	True
2	6	user_2	0.567260	True

# delete everything
table.delete()
table.count()

DocTable: DELETE FROM _documents_
DocTable: SELECT count() AS count_1 
FROM _documents_
 LIMIT ? OFFSET ?





0