DocTable Examples: Insert and Delete

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

In [1]:
import random
import pandas as pd
import numpy as np
import sys
sys.path.append('..')
import doctable as dt
In [2]:
schema = (
    ('integer','id',dict(primary_key=True, autoincrement=True)),
    ('string','name', dict(nullable=False)),
    ('integer','age'),
    ('boolean', 'is_old'),
)
In [3]:
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).

In [4]:
db = dt.DocTable(target=':memory:', schema=schema, verbose=True)
for row in make_rows():
    db.insert(row)
db.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_
Out[4]:
id name age is_old
0 1 user_0 0.870957 True
1 2 user_1 0.343512 False
2 3 user_2 0.372013 False
In [5]:
newrows = list(make_rows())
db.insert(newrows)
db.select_df(verbose=False)
Out[5]:
id name age is_old
0 1 user_0 0.870957 True
1 2 user_1 0.343512 False
2 3 user_2 0.372013 False
3 4 user_0 0.350080 False
4 5 user_1 0.498410 False
5 6 user_2 0.626763 True

Deletes

In [6]:
# delete all entries where is_old is false
db.delete(where=~db['is_old'])
db.select_df(verbose=False)
DocTable: DELETE FROM _documents_ WHERE _documents_.is_old = 0
Out[6]:
id name age is_old
0 1 user_0 0.870957 True
1 6 user_2 0.626763 True
In [7]:
# use vacuum to free unused space now
db.delete(where=~db['is_old'], vacuum=True)
db.select_df(verbose=False)
DocTable: DELETE FROM _documents_ WHERE _documents_.is_old = 0
DocTable: VACUUM
Out[7]:
id name age is_old
0 1 user_0 0.870957 True
1 6 user_2 0.626763 True
In [9]:
# delete everything
db.delete()
db.count()
DocTable: DELETE FROM _documents_
DocTable: SELECT count() AS count_1 
FROM _documents_
 LIMIT ? OFFSET ?
Out[9]:
0