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.
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 new_db():
table = doctable.DocTable(schema=Record, target=':memory:', verbose=True)
N = 10
for i in range(N):
age = random.random() # number in [0,1]
is_old = age > 0.5
table.insert({'name':'user_'+str(i), 'age':age, 'is_old':is_old}, verbose=False)
return table
table = new_db()
print(table)
<DocTable (4 cols)::sqlite:///:memory::_documents_>
/DataDrive/code/doctable/examples/../doctable/doctable.py:324: UserWarning: Method .insert() is depricated. Please use .q.insert_single(), .q.insert_single_raw(), .q.insert_multi(), or .q.insert_multi() instead.
warnings.warn('Method .insert() is depricated. Please use .q.insert_single(), '
/DataDrive/code/doctable/examples/../doctable/doctable.py:350: UserWarning: .insert_single() is depricated: please use .q.insert_single() or .q.insert_single_raw()
warnings.warn(f'.insert_single() is depricated: please use .q.insert_single() or '
table.select_df(limit=3)
DocTable: SELECT _documents_.id, _documents_.name, _documents_.age, _documents_.is_old
FROM _documents_
LIMIT ? OFFSET ?
/DataDrive/code/doctable/examples/../doctable/doctable.py:379: UserWarning: Method .select_df() is depricated. Please use .q.select_df() instead.
warnings.warn('Method .select_df() is depricated. Please use .q.select_df() instead.')
|
id |
name |
age |
is_old |
| 0 |
1 |
user_0 |
0.998030 |
True |
| 1 |
2 |
user_1 |
0.210891 |
False |
| 2 |
3 |
user_2 |
0.431233 |
False |
Single Update
Update multiple (or single) rows with same values.
table = new_db()
table.select_df(where=table['is_old']==True, limit=3, verbose=False)
/DataDrive/code/doctable/examples/../doctable/doctable.py:324: UserWarning: Method .insert() is depricated. Please use .q.insert_single(), .q.insert_single_raw(), .q.insert_multi(), or .q.insert_multi() instead.
warnings.warn('Method .insert() is depricated. Please use .q.insert_single(), '
/DataDrive/code/doctable/examples/../doctable/doctable.py:350: UserWarning: .insert_single() is depricated: please use .q.insert_single() or .q.insert_single_raw()
warnings.warn(f'.insert_single() is depricated: please use .q.insert_single() or '
/DataDrive/code/doctable/examples/../doctable/doctable.py:379: UserWarning: Method .select_df() is depricated. Please use .q.select_df() instead.
warnings.warn('Method .select_df() is depricated. Please use .q.select_df() instead.')
|
id |
name |
age |
is_old |
| 0 |
1 |
user_0 |
0.670833 |
True |
| 1 |
2 |
user_1 |
0.895172 |
True |
| 2 |
5 |
user_4 |
0.688209 |
True |
table = new_db()
table.update({'age':1},where=table['is_old']==True)
table.update({'age':0},where=table['is_old']==False)
table.select_df(limit=3, verbose=False)
DocTable: UPDATE _documents_ SET age=? WHERE _documents_.is_old = 1
DocTable: UPDATE _documents_ SET age=? WHERE _documents_.is_old = 0
/DataDrive/code/doctable/examples/../doctable/doctable.py:324: UserWarning: Method .insert() is depricated. Please use .q.insert_single(), .q.insert_single_raw(), .q.insert_multi(), or .q.insert_multi() instead.
warnings.warn('Method .insert() is depricated. Please use .q.insert_single(), '
/DataDrive/code/doctable/examples/../doctable/doctable.py:350: UserWarning: .insert_single() is depricated: please use .q.insert_single() or .q.insert_single_raw()
warnings.warn(f'.insert_single() is depricated: please use .q.insert_single() or '
/DataDrive/code/doctable/examples/../doctable/doctable.py:440: UserWarning: Method .update() is depricated. Please use .q.update() instead.
warnings.warn('Method .update() is depricated. Please use .q.update() instead.')
/DataDrive/code/doctable/examples/../doctable/doctable.py:379: UserWarning: Method .select_df() is depricated. Please use .q.select_df() instead.
warnings.warn('Method .select_df() is depricated. Please use .q.select_df() instead.')
|
id |
name |
age |
is_old |
| 0 |
1 |
user_0 |
1 |
True |
| 1 |
2 |
user_1 |
0 |
False |
| 2 |
3 |
user_2 |
1 |
True |
Apply as Map Function
This feature allows you to update columns based on the values of old columns.
table = new_db()
values = {table['name']:table['name']+'th', table['age']:table['age']+1, table['is_old']:True}
table.update(values)
table.select_df(limit=3, verbose=False)
DocTable: UPDATE _documents_ SET name=(_documents_.name || ?), age=(_documents_.age + ?), is_old=?
/DataDrive/code/doctable/examples/../doctable/doctable.py:324: UserWarning: Method .insert() is depricated. Please use .q.insert_single(), .q.insert_single_raw(), .q.insert_multi(), or .q.insert_multi() instead.
warnings.warn('Method .insert() is depricated. Please use .q.insert_single(), '
/DataDrive/code/doctable/examples/../doctable/doctable.py:350: UserWarning: .insert_single() is depricated: please use .q.insert_single() or .q.insert_single_raw()
warnings.warn(f'.insert_single() is depricated: please use .q.insert_single() or '
/DataDrive/code/doctable/examples/../doctable/doctable.py:440: UserWarning: Method .update() is depricated. Please use .q.update() instead.
warnings.warn('Method .update() is depricated. Please use .q.update() instead.')
/DataDrive/code/doctable/examples/../doctable/doctable.py:379: UserWarning: Method .select_df() is depricated. Please use .q.select_df() instead.
warnings.warn('Method .select_df() is depricated. Please use .q.select_df() instead.')
|
id |
name |
age |
is_old |
| 0 |
1 |
user_0th |
1.566417 |
True |
| 1 |
2 |
user_1th |
1.434875 |
True |
| 2 |
3 |
user_2th |
1.422777 |
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.
table = new_db()
values = [(table['name'],table['age']-1), (table['age'],table['age']+1),]
table.update(values)
table.select_df(limit=3, verbose=False)
DocTable: UPDATE _documents_ SET name=(_documents_.age - ?), age=(_documents_.age + ?)
/DataDrive/code/doctable/examples/../doctable/doctable.py:324: UserWarning: Method .insert() is depricated. Please use .q.insert_single(), .q.insert_single_raw(), .q.insert_multi(), or .q.insert_multi() instead.
warnings.warn('Method .insert() is depricated. Please use .q.insert_single(), '
/DataDrive/code/doctable/examples/../doctable/doctable.py:350: UserWarning: .insert_single() is depricated: please use .q.insert_single() or .q.insert_single_raw()
warnings.warn(f'.insert_single() is depricated: please use .q.insert_single() or '
/DataDrive/code/doctable/examples/../doctable/doctable.py:440: UserWarning: Method .update() is depricated. Please use .q.update() instead.
warnings.warn('Method .update() is depricated. Please use .q.update() instead.')
/DataDrive/code/doctable/examples/../doctable/doctable.py:379: UserWarning: Method .select_df() is depricated. Please use .q.select_df() instead.
warnings.warn('Method .select_df() is depricated. Please use .q.select_df() instead.')
|
id |
name |
age |
is_old |
| 0 |
1 |
-0.823513491706054 |
1.176487 |
False |
| 1 |
2 |
-0.567734080088791 |
1.432266 |
False |
| 2 |
3 |
-0.838314843815808 |
1.161685 |
False |
Update Using SQL WHERE String
table = new_db()
table.update({'age':1.00}, wherestr='is_old==true')
table.select_df(limit=5, verbose=False)
DocTable: UPDATE _documents_ SET age=? WHERE is_old==true
/DataDrive/code/doctable/examples/../doctable/doctable.py:324: UserWarning: Method .insert() is depricated. Please use .q.insert_single(), .q.insert_single_raw(), .q.insert_multi(), or .q.insert_multi() instead.
warnings.warn('Method .insert() is depricated. Please use .q.insert_single(), '
/DataDrive/code/doctable/examples/../doctable/doctable.py:350: UserWarning: .insert_single() is depricated: please use .q.insert_single() or .q.insert_single_raw()
warnings.warn(f'.insert_single() is depricated: please use .q.insert_single() or '
/DataDrive/code/doctable/examples/../doctable/doctable.py:440: UserWarning: Method .update() is depricated. Please use .q.update() instead.
warnings.warn('Method .update() is depricated. Please use .q.update() instead.')
/DataDrive/code/doctable/examples/../doctable/doctable.py:379: UserWarning: Method .select_df() is depricated. Please use .q.select_df() instead.
warnings.warn('Method .select_df() is depricated. Please use .q.select_df() instead.')
|
id |
name |
age |
is_old |
| 0 |
1 |
user_0 |
0.488699 |
False |
| 1 |
2 |
user_1 |
0.391556 |
False |
| 2 |
3 |
user_2 |
1.000000 |
True |
| 3 |
4 |
user_3 |
0.472176 |
False |
| 4 |
5 |
user_4 |
0.154501 |
False |