AutoMapDB

Automagically provides CRUD API to an arbitrary PostgreSQL database

Getting Started

Prerequisites

Dependencies

• Python3 & pip
• Postgres (10+) Client & Server Dev
• GCC
• libffi

apt install -y python3-pip gcc postgresql postgresql-server-dev-10
# or
pacman -S python3 python-pip gcc postgresql postgresql-libs libffi

Install

pip install automapdb

Usage

From Shell

Table Mappings

Table Mappings are representations of Database Tables. They live in your tables.json and can be used to customize API and CLI access to the database tables.

To start, create a mapping for a table you'd like to CRUD on:

# Pass connection string as argument
automapdb --db_url=postgresql://i2b2:demouser@localhost:5433/i2b2 mapper add i2b2pm.pm_user_data

# Or conveniently set the environment variable DB_URL
export DB_URL=postgresql://i2b2:demouser@localhost:5433/i2b2
automapdb mapper add i2b2pm.pm_user_data

This creates an entry in your tables.json:

{
"i2b2pm.pm_user_data":  # Customizable name for your table (sql path by default)
  {
    "path": "i2b2pm.pm_user_data",  # SQL path to table (schema.table)
    "get_args": ["user_id"],        # Required fields to select an entry (PRIMARY KEY fields)
    "set_args": ["user_id"]         # Required fields to create/update an entry (NOT NULLABLE fields)
}

To map all the tables in your schema:

automapdb mapper add_all --schema=i2b2pm

To create a mapper to your own gusto, specify the database path and the field you'd like to change:

automapdb mapper update i2b2pm.pm_user_data --name=user --get_args=[user_id] --set_args=[user_id,full_name]

Hint: You can omit the flags if you give the arguments in proper order

Now have a look at what happened:

automapdb mapper show i2b2pm.pm_user_data

To customize the columns needed to create or select a table entry can be done by setting get_args and set_args:. By default the get_args are the PRIMARY KEY fields in the database, the set_args are NOT NULLABLE fields.

To inspect the columns and see the columns, use show_fields TABLE:

automapdb mapper show_fields i2b2pm.pm_project_data

If you are done mapping get a list of your mappings:

automapdb mapper list

Table Operations

Usage: automapdb table TABLENAME [add|get|update|delete|list]

Basic CRUD operations:

automapdb table list user
automapdb table add user testuser full_name "Test User" project_path "/test"
automapdb table get user testuser
automapdb table update user testuser email user@example.org
automapdb table delete user testuser

Simple queries

Using list and a series of arguments one can create a filtered query:

automapdb table list user --status_cd=A --full_name="i2b2 Admin"

Additionally, one can construct an ILIKE filter by passing a dictionary to the ilike argument:

automapdb table list of --patient_num=1000000001 --ilike='{tval_char:"%tobacco%"}'

Using the argument fields will return only selected columns:

automapdb table list user --fields=user_id,password

Show info on table columns from database:

automapdb table list_fields user

Formatting

The output of many methods can be formatted by passing the --fmt=FORMAT argument.
Currently supported are json, jsonl, yaml, str and pretty (python pprint).

From Python

from automapdb import AutoMapDB, TableManager, TableMapper

# Create AutoMapDB
connection_string = "postgresql://i2b2:demouser@localhost:5432/i2b2"
db = AutoMapDB(connection_string)

# Create mapper for Tables
mapper = TableMapper(db)

# Create mappings for all tables in schema 'i2b2pm'
mapper.add_all("i2b2pm")

# Change table_mapping mapping_name
mapper.update("i2b2pm.pm_project_data", name="project")

# Create TableManager object
table = TableManager(db, mapper=mapper)

# List rows in Table project (=i2b2pm.pm_project_data)
projects = table.list("project", fields=["project_id", "project_path", "status_cd"])

# Iterate over rows
for project in projects:
    # Get row data
    if project["status_cd"] == "D":
        # Update data in row
        table.update("project", project["project_id"], project["project_path"], "status_cd", "A")

Help

The cli is built around Fire, therefore you can ask for help at any point simply by firing off your command:

$ automapdb table

> COMMANDS
>     COMMAND is one of the following:
>      add
>        Add row to table, with args mapped to the tables not_nullable fields
>      delete
>        Delete row from table
>      get
>        Query database for one/multiple rows. Use args to provide the primary keys
>      list
>        Open query to table with kwargs as WHERE filters
>      list_fields
>        Shows the name, datatype, primary_key and nullable flags for the columns of given Table
>      query
>        Construct raw SQL query for a table.
>      update
>        Update row with args mapped to the tables not_nullable fields

$ automapdb table add i2b2pm.pm_user_data

> ERROR: ERROR: Missing fields: 'user_id'
> Usage: automapdb add i2b2pm.pm_user_data 'user_id' ['full_name'|'password'|'email'|'project_path']

Development

pip install -r requirements.dev.txt
pre-commit install
pre-commit install --hook-type commit-msg

Test

Start the i2b2 postgres backend:

docker run -p 5433:5432 i2b2/i2b2-pg:p1

Run tests:

python -m pytest --cov=automapdb

Citation

If you use this work in scientific work, please cite our work according to https://zenodo.org/record/5566911.

LICENSE

© 2020 - present Fabian Thomczyk and Raphael Scheible, Faculty of Medicine, University of Freiburg
Released under the MIT License.