SQLAlchemy models and DDL and ERD generation from chop-dbhi/data-models style JSON endpoints.
Project description
# Data Models SQLAlchemy
[](https://circleci.com/gh/chop-dbhi/data-models-sqlalchemy/tree/master) [](https://coveralls.io/github/chop-dbhi/data-models-sqlalchemy?branch=master)
SQLAlchemy models and DDL and ERD generation for [chop-dbhi/data-models-service](https://github.com/chop-dbhi/data-models-service) style JSON endpoints.
Web service available at http://dmsa.a0b.io/
## SQLAlchemy Models
In your shell, hopefully within a virtualenv:
```sh
pip install dmsa
```
In python:
```python
from sqlalchemy import MetaData
from dmsa import make_model_from_service
metadata = MetaData()
metadata = make_model_from_service('omop', '5.0.0',
'http://data-models.origins.link/',
metadata)
for tbl in metadata.sorted_tables:
print tbl.name
```
These models are dynamically generated at runtime from JSON endpoints provided by chop-dbhi/data-models-service, which reads data stored in chop-dbhi/data-models. Any data model stored there can be converted into SQLAlchemy models. At the time of writing, the following are available.
CAVEAT: The models are currently "Classical"-style and therefore un-mapped. See more information [here](https://github.com/chop-dbhi/data-models-sqlalchemy/issues/22).
- i2b2
- 1.7.0
- i2b2 for PEDSnet
- 2.0.1
- OMOP
- 4.0.0
- 5.0.0
- PCORnet
- 1.0.0
- 2.0.0
- 3.0.0
- PEDSnet
- 1.0.0
- 2.0.0
- 2.1.0
- 2.2.0
## DDL and ERD Generation
Use of the included Dockerfile is highly recommended to avoid installing DBMS and graphing specific system requirements.
The following DBMS dialects are supported when generating DDL:
- PostgreSQL
- MySQL
- MS SQL Server
- Oracle
### With Docker:
Retrieve the image:
```sh
docker pull dbhi/data-models-sqlalchemy
```
Usage Message:
```sh
docker run --rm dbhi/data-models-sqlalchemy dmsa -h
```
Generate OMOP V5 creation DDL for Oracle:
```sh
docker run --rm dbhi/data-models-sqlalchemy dmsa ddl -tci omop 5.0.0 oracle
```
Generate OMOP V5 drop DDL for Oracle:
```sh
docker run --rm dbhi/data-models-sqlalchemy dmsa ddl -tci --drop omop 5.0.0 oracle
```
Generate OMOP V5 data deletion DML for Oracle:
```sh
docker run --rm dbhi/data-models-sqlalchemy dmsa ddl --delete-data omop 5.0.0 oracle
```
Generate i2b2 PEDSnet V2 ERD (the image will land at `./erd/i2b2_pedsnet_2.0.0_erd.png`):
```sh
docker run --rm -v $(pwd)/erd:/erd dbhi/data-models-sqlalchemy dmsa erd -o /erd/i2b2_pedsnet_2.0.0_erd.png i2b2_pedsnet 2.0.0
```
The `graphviz` graphing package supports a number of other output formats, which are interpreted from the passed extension.
### Without Docker:
Install the system requirements (see Dockerfile for details):
- Python 2.7
- `graphviz` for ERD generation
- Oracle `instantclient-basic` and `-sdk` and `libaio1` for Oracle DDL generation
- `libpq-dev` for PostgreSQL DDL generation
- `unixodbc-dev` for MS SQL Server DDL generation
Install the python requirements, hopefully within a virtualenv (see Dockerfile for details):
```sh
pip install cx-Oracle # for Oracle DDL generation
pip install psycopg2 # for PostgreSQL DDL generation
pip install PyMySQL # for MySQL DDL generation
pip install pyodbc # for MS SQL Server DDL generation
```
Install the data-models-sqlalchemy python package:
```sh
pip install dmsa
```
Usage Message:
```sh
dmsa -h
```
Generate OMOP V5 creation DDL for Oracle:
```sh
dmsa ddl -tci omop 5.0.0 oracle
```
Generate OMOP V5 drop DDL for Oracle:
```sh
dmsa ddl -tci --drop omop 5.0.0 oracle
```
Generate OMOP V5 data deletion DML for Oracle:
```sh
dmsa ddl --delete-data omop 5.0.0 oracle
```
Generate i2b2 PEDSnet V2 ERD (the image will land at `./erd/i2b2_pedsnet_2.0.0_erd.png`):
```sh
mkdir -p erd
dmsa erd -o ./erd/i2b2_pedsnet_2.0.0_erd.png i2b2_pedsnet 2.0.0
```
## Web Service
The web service uses a [Gunicorn](http://gunicorn.org/) server in the Docker container and the Flask debug server locally. It exposes the following endpoints:
- Creation DDL at `/<model>/<version>/ddl/<dialect>/`
- Creation DDL for only `table`, `constraint`, or `index` elements at `/<model>/<version>/ddl/<dialect>/<elements>`
- Drop DDL at `/<model>/<version>/drop/<dialect>/`
- Drop DDL for only `table`, `constraint`, or `index` elements at `/<model>/<version>/drop/<dialect>/<elements>`
- Data deletion DML at `/<model>/<version>/delete/<dialect>/`
- ERDs at `/<model>/<version>/erd/`
- Oracle logging scripts at `/<model>/<version>/logging/oracle/`
- Oracle logging scripts for only `table` or `index` elements at `/<model>/<version>/logging/oracle/<elements>/`
- Oracle nologging scripts at `/<model>/<version>/nologging/oracle/`
- Oracle nologging scripts for only `table` or `index` elements at `/<model>/<version>/logging/oracle/<elements>/`
### With Docker:
Usage:
```sh
docker run dbhi/data-models-sqlalchemy gunicorn -h
```
Run:
```sh
docker run dbhi/data-models-sqlalchemy # Uses Dockerfile defaults of 0.0.0.0:80
```
### Without Docker:
Install Flask:
```sh
pip install Flask
```
Usage Message:
```sh
dmsa -h
```
Run:
```sh
dmsa serve # Uses Flask defaults of 127.0.0.1:5000
```
[](https://circleci.com/gh/chop-dbhi/data-models-sqlalchemy/tree/master) [](https://coveralls.io/github/chop-dbhi/data-models-sqlalchemy?branch=master)
SQLAlchemy models and DDL and ERD generation for [chop-dbhi/data-models-service](https://github.com/chop-dbhi/data-models-service) style JSON endpoints.
Web service available at http://dmsa.a0b.io/
## SQLAlchemy Models
In your shell, hopefully within a virtualenv:
```sh
pip install dmsa
```
In python:
```python
from sqlalchemy import MetaData
from dmsa import make_model_from_service
metadata = MetaData()
metadata = make_model_from_service('omop', '5.0.0',
'http://data-models.origins.link/',
metadata)
for tbl in metadata.sorted_tables:
print tbl.name
```
These models are dynamically generated at runtime from JSON endpoints provided by chop-dbhi/data-models-service, which reads data stored in chop-dbhi/data-models. Any data model stored there can be converted into SQLAlchemy models. At the time of writing, the following are available.
CAVEAT: The models are currently "Classical"-style and therefore un-mapped. See more information [here](https://github.com/chop-dbhi/data-models-sqlalchemy/issues/22).
- i2b2
- 1.7.0
- i2b2 for PEDSnet
- 2.0.1
- OMOP
- 4.0.0
- 5.0.0
- PCORnet
- 1.0.0
- 2.0.0
- 3.0.0
- PEDSnet
- 1.0.0
- 2.0.0
- 2.1.0
- 2.2.0
## DDL and ERD Generation
Use of the included Dockerfile is highly recommended to avoid installing DBMS and graphing specific system requirements.
The following DBMS dialects are supported when generating DDL:
- PostgreSQL
- MySQL
- MS SQL Server
- Oracle
### With Docker:
Retrieve the image:
```sh
docker pull dbhi/data-models-sqlalchemy
```
Usage Message:
```sh
docker run --rm dbhi/data-models-sqlalchemy dmsa -h
```
Generate OMOP V5 creation DDL for Oracle:
```sh
docker run --rm dbhi/data-models-sqlalchemy dmsa ddl -tci omop 5.0.0 oracle
```
Generate OMOP V5 drop DDL for Oracle:
```sh
docker run --rm dbhi/data-models-sqlalchemy dmsa ddl -tci --drop omop 5.0.0 oracle
```
Generate OMOP V5 data deletion DML for Oracle:
```sh
docker run --rm dbhi/data-models-sqlalchemy dmsa ddl --delete-data omop 5.0.0 oracle
```
Generate i2b2 PEDSnet V2 ERD (the image will land at `./erd/i2b2_pedsnet_2.0.0_erd.png`):
```sh
docker run --rm -v $(pwd)/erd:/erd dbhi/data-models-sqlalchemy dmsa erd -o /erd/i2b2_pedsnet_2.0.0_erd.png i2b2_pedsnet 2.0.0
```
The `graphviz` graphing package supports a number of other output formats, which are interpreted from the passed extension.
### Without Docker:
Install the system requirements (see Dockerfile for details):
- Python 2.7
- `graphviz` for ERD generation
- Oracle `instantclient-basic` and `-sdk` and `libaio1` for Oracle DDL generation
- `libpq-dev` for PostgreSQL DDL generation
- `unixodbc-dev` for MS SQL Server DDL generation
Install the python requirements, hopefully within a virtualenv (see Dockerfile for details):
```sh
pip install cx-Oracle # for Oracle DDL generation
pip install psycopg2 # for PostgreSQL DDL generation
pip install PyMySQL # for MySQL DDL generation
pip install pyodbc # for MS SQL Server DDL generation
```
Install the data-models-sqlalchemy python package:
```sh
pip install dmsa
```
Usage Message:
```sh
dmsa -h
```
Generate OMOP V5 creation DDL for Oracle:
```sh
dmsa ddl -tci omop 5.0.0 oracle
```
Generate OMOP V5 drop DDL for Oracle:
```sh
dmsa ddl -tci --drop omop 5.0.0 oracle
```
Generate OMOP V5 data deletion DML for Oracle:
```sh
dmsa ddl --delete-data omop 5.0.0 oracle
```
Generate i2b2 PEDSnet V2 ERD (the image will land at `./erd/i2b2_pedsnet_2.0.0_erd.png`):
```sh
mkdir -p erd
dmsa erd -o ./erd/i2b2_pedsnet_2.0.0_erd.png i2b2_pedsnet 2.0.0
```
## Web Service
The web service uses a [Gunicorn](http://gunicorn.org/) server in the Docker container and the Flask debug server locally. It exposes the following endpoints:
- Creation DDL at `/<model>/<version>/ddl/<dialect>/`
- Creation DDL for only `table`, `constraint`, or `index` elements at `/<model>/<version>/ddl/<dialect>/<elements>`
- Drop DDL at `/<model>/<version>/drop/<dialect>/`
- Drop DDL for only `table`, `constraint`, or `index` elements at `/<model>/<version>/drop/<dialect>/<elements>`
- Data deletion DML at `/<model>/<version>/delete/<dialect>/`
- ERDs at `/<model>/<version>/erd/`
- Oracle logging scripts at `/<model>/<version>/logging/oracle/`
- Oracle logging scripts for only `table` or `index` elements at `/<model>/<version>/logging/oracle/<elements>/`
- Oracle nologging scripts at `/<model>/<version>/nologging/oracle/`
- Oracle nologging scripts for only `table` or `index` elements at `/<model>/<version>/logging/oracle/<elements>/`
### With Docker:
Usage:
```sh
docker run dbhi/data-models-sqlalchemy gunicorn -h
```
Run:
```sh
docker run dbhi/data-models-sqlalchemy # Uses Dockerfile defaults of 0.0.0.0:80
```
### Without Docker:
Install Flask:
```sh
pip install Flask
```
Usage Message:
```sh
dmsa -h
```
Run:
```sh
dmsa serve # Uses Flask defaults of 127.0.0.1:5000
```
Project details
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.
Source Distribution
dmsa-0.5.8.tar.gz
(16.1 kB
view details)
Built Distributions
dmsa-0.5.8-py2.7.egg
(46.5 kB
view details)
dmsa-0.5.8-py2-none-any.whl
(24.8 kB
view details)
File details
Details for the file dmsa-0.5.8.tar.gz
.
File metadata
- Download URL: dmsa-0.5.8.tar.gz
- Upload date:
- Size: 16.1 kB
- Tags: Source
- Uploaded using Trusted Publishing? No
File hashes
Algorithm | Hash digest | |
---|---|---|
SHA256 |
f66ac8a7b378d0e5729ce3a3edffff7ec2d606f7e765a5418d2cb2bab408b351
|
|
MD5 |
6ee46394a1aaa9d9e7f428f11a7149c0
|
|
BLAKE2b-256 |
01713a5bf012d831033113a5e3b3f42ba441839bc1f5df120fadcb4e175b8abf
|
File details
Details for the file dmsa-0.5.8-py2.7.egg
.
File metadata
- Download URL: dmsa-0.5.8-py2.7.egg
- Upload date:
- Size: 46.5 kB
- Tags: Egg
- Uploaded using Trusted Publishing? No
File hashes
Algorithm | Hash digest | |
---|---|---|
SHA256 |
940be1d3de4d511b95029ce21d7f47ae6bf10668642ecf7accc8ca1c0e89bde8
|
|
MD5 |
7cc9c158f85515e3c0358dae97d4411c
|
|
BLAKE2b-256 |
5804b8cac86b7f120f2e89e5d88494a8f67823f5900ed693ed76e383778b28af
|
File details
Details for the file dmsa-0.5.8-py2-none-any.whl
.
File metadata
- Download URL: dmsa-0.5.8-py2-none-any.whl
- Upload date:
- Size: 24.8 kB
- Tags: Python 2
- Uploaded using Trusted Publishing? No
File hashes
Algorithm | Hash digest | |
---|---|---|
SHA256 |
ce0d02fd91e1b8158df39c2ada2951064a96ee22f0d3e6fbc92785f0b4f2ca9a
|
|
MD5 |
5ff285ae0b9b729dbe6d4c1fe762de93
|
|
BLAKE2b-256 |
79a9ee5ca1ebacc78a958e303b23060f4e371179ea60b0de51941b08ed87b95f
|