DocTable Example: Schemas

In this example, we show column specifications for each available type, as well as the sqlalchemy equivalents on which they were based. Note that .

Each column in the schema passed to doctable is a 2+ tuple containing, in order, the column type, name, and arguments, and optionally the sqlalchemy type arguemnts.

from datetime import datetime
from pprint import pprint
import pandas as pd
import typing

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

@doctable.schema
class MyClass:
    __slots__ = []
    # builtin column types
    idx: int = doctable.IDCol()

    # unique name
    name: str = doctable.Col(unique=True) # want to be the first ordered argument


    # special columns for added and updated
    updated: datetime = doctable.UpdatedCol()
    added: datetime = doctable.AddedCol()

    # custom column types 
    lon: float = doctable.Col()
    lat: float = doctable.Col()

    # use Col to use factory to construct emtpy list
    # will be stored as binary/pickle type, since no other available
    elements: doctable.JSONType = doctable.Col(field_kwargs=dict(default_factory=list))

class MyTable(doctable.DocTable):
    _schema_ = MyClass
    # indices and constraints
    _indices = (
        doctable.Index('lonlat_index', 'lon', 'lat', unique=True),
        doctable.Index('name_index', 'name'),
    )
    _constraints_ = (
        doctable.Constraint('check', 'lon > 0', name='check_lon'),
        doctable.Constraint('check', 'lat > 0'),
    )


md = MyTable(target=':memory:', verbose=True)
#pprint(md.schemainfo)
md.schema_table()

	name	type	nullable	default	autoincrement	primary_key
0	idx	INTEGER	False	None	auto	1
1	name	VARCHAR	True	None	auto	0
2	updated	DATETIME	True	None	auto	0
3	added	DATETIME	True	None	auto	0
4	lon	FLOAT	True	None	auto	0
5	lat	FLOAT	True	None	auto	0
6	elements	VARCHAR	True	None	auto	0