DocTable Examples: Update

Here I show how to update data into a DocTable. In addition to providing updated values, DocTable also allows you to create map functions to transform existing data.

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'),
)
def new_db():
    db = dt.DocTable(schema, tabname='mydocuments', verbose=True)
    N = 10
    for i in range(N):
        age = random.random() # number in [0,1]
        is_old = age > 0.5
        db.insert({'name':'user_'+str(i), 'age':age, 'is_old':is_old}, verbose=False)
    return db

db = new_db()
print(db)
DocTable2 Query: SELECT count() AS count_1 
FROM mydocuments
 LIMIT :param_1
<DocTable2::mydocuments ct: 10>
In [3]:
db.select_df(limit=3)
DocTable2 Query: SELECT mydocuments.id, mydocuments.name, mydocuments.age, mydocuments.is_old 
FROM mydocuments
 LIMIT :param_1
Out[3]:
id name age is_old
0 1 user_0 0.156287 False
1 2 user_1 0.770439 True
2 3 user_2 0.889879 True

Single Update

Update multiple (or single) rows with same values.

In [4]:
db = new_db()
db.select_df(where=db['is_old']==True, limit=3, verbose=False)
Out[4]:
id name age is_old
0 3 user_2 0.563325 True
1 5 user_4 0.829197 True
2 6 user_5 0.817841 True
In [5]:
db = new_db()
db.update({'age':1},where=db['is_old']==True)
db.update({'age':0},where=db['is_old']==False)
db.select_df(limit=3, verbose=False)
DocTable2 Query: UPDATE mydocuments SET age=:age WHERE mydocuments.is_old = true
DocTable2 Query: UPDATE mydocuments SET age=:age WHERE mydocuments.is_old = false
Out[5]:
id name age is_old
0 1 user_0 1 True
1 2 user_1 0 False
2 3 user_2 0 False

Apply as Map Function

This feature allows you to update columns based on the values of old columns.

In [6]:
db = new_db()
values = {db['name']:db['name']+'th', db['age']:db['age']+1, db['is_old']:True}
db.update(values)
db.select_df(limit=3, verbose=False)
DocTable2 Query: UPDATE mydocuments SET name=(mydocuments.name || :name_1), age=(mydocuments.age + :age_1), is_old=:is_old
Out[6]:
id name age is_old
0 1 user_0th 1.926072 True
1 2 user_1th 1.440472 True
2 3 user_2th 1.875261 True

Apply as Set of Ordered Map Functions

This is useful for when the updating of one column might change the value of another, depending on the order in which it was applied.

In [7]:
db = new_db()
values = [(db['name'],db['age']-1), (db['age'],db['age']+1),]
db.update(values)
db.select_df(limit=3, verbose=False)
DocTable2 Query: UPDATE mydocuments SET name=(mydocuments.age - :age_1), age=(mydocuments.age + :age_2)
Out[7]:
id name age is_old
0 1 -0.557324771408994 1.442675 False
1 2 -0.720820415168971 1.279180 False
2 3 -0.000447943230516556 1.999552 True

Update Using SQL WHERE String

In [8]:
db = new_db()
db.update({'age':1.00},whrstr='is_old==true')
db.select_df(limit=5, verbose=False)
DocTable2 Query: UPDATE mydocuments SET age=:age WHERE is_old==true
Out[8]:
id name age is_old
0 1 user_0 1.000000 True
1 2 user_1 0.415172 False
2 3 user_2 0.131622 False
3 4 user_3 0.193966 False
4 5 user_4 1.000000 True