Skip to content

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
import doctable
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)
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