DocTable Overview

A DocTable acts as an object-oriented interface to a single database table. It will maintain a connection to the database and sqlalchemy metadata about the table schema. Schemas are provided as classes that act much like dataclasses (see the doctable schema guide for more explanation), and indices and constraints are often specified in a definition of a subclass of DocTable.

You may also want to see the vignettes for more examples, the DocTable docs for more information about the class, or the schema guide for more information about creating schemas. I also recommend looking examples for insert, delete, select, and update methods.

Here I'll just provide some examples to give a sense of how DocTable works.

Defining a Database Schema

DocTable schemas are created using the doctable.schema decorator on a class that uses doctable.Col for defaulted parameters. Check out the schema guide for more detail about schema classes. Our demonstration class will include three columns: id, name, and age, with an additional .is_old property derived from age for example.

We can instantiate a DocTable by passing a target and schema (Record in our example) parameters, and I show the resulting schema using .schema_table(). Note that the type hints were used to describe column types, and id was used as the auto-incremented primary key.

Probably a more common use case will be to subclass DocTable to provide some basic definitions.

Object Interface

The main goal of doctable was to create an object-oriented interface to working with database tables. To that end, I'll show some of the more common use cases in the following examples.

First, note that subscripting the table object allows you to access sqlalchemy Column objects, which, as I will show a bit later, can be used to create where conditionals for select and update queries.

As we'll show later, these column objects also have some operators defined such that they can be used to construct complex queries and functions.

Inserting Rows

We use the .insert() method to insert a row passed as a dictionary of column name -> value entries.

Select Statements

Now we show how to select data from the table. Use the .count() method to check the number of rows. It also accepts some column conditionals to count entries that satisfy a given criteria

Use the .select() method with no arguments to retrieve all rows of the table. You can also choose to select one or more columns to select.

The SUM() and COUNT() SQL functions have been mapped to .sum and .count attributes of columns.

Alternatively, to see the results as a pandas dataframe, we can use .select_df().

Now we can select specific elements of the db using the where argument of the .select() method.

We can update the results in a similar way, using the where argument.

And we can delete elements using the .delete() method.

Notes on DB Interface

DocTable2 allows you to access columns through direct subscripting, then relies on the power of sqlalchemy column objects to do most of the work of constructing queries. Here are a few notes on their use. For more demonstration, see the example in examples/dt2_select.ipynb