psql-to-models 0.1.2

Description:

psqltomodels 0.1.2

PSQL TO MODELS
Use regex patterns to match PostgreSQL schemas and output SQLAlchemy and Pydantic Models.
Designed for FastAPI.
Install
Requires Python 3.10.
git clone [email protected]:AlbertoV5/psql-to-models.git

cd psql-to-models

Install in editable mode.
pip install -e .

Usage
python -m psql-to-models -i ./example/schema.sql -a ./example/models_alchemy.py -p ./example/models_pydantic.py

Before.
schema.sql

After.
models_alchemy.py schema.sql
models_pydantic.py

Results Example
SQL Input
DROP TABLE IF EXISTS DATETIMEEVENTS CASCADE;
CREATE TABLE DATETIMEEVENTS
(
ROW_ID INT NOT NULL,
SUBJECT_ID INT NOT NULL,
HADM_ID INT,
ICUSTAY_ID INT,
ITEMID INT NOT NULL,
CHARTTIME TIMESTAMP(0) NOT NULL,
STORETIME TIMESTAMP(0) NOT NULL,
CGID INT NOT NULL,
VALUE TIMESTAMP(0),
VALUEUOM VARCHAR(50) NOT NULL,
WARNING SMALLINT,
ERROR SMALLINT,
RESULTSTATUS VARCHAR(50),
STOPPED VARCHAR(50),
CONSTRAINT datetime_rowid_pk PRIMARY KEY (ROW_ID)
) ;

DROP TABLE IF EXISTS DIAGNOSES_ICD CASCADE;
CREATE TABLE DIAGNOSES_ICD
(
ROW_ID INT NOT NULL,
SUBJECT_ID INT NOT NULL,
HADM_ID INT NOT NULL,
SEQ_NUM INT,
ICD9_CODE VARCHAR(10),
CONSTRAINT diagnosesicd_rowid_pk PRIMARY KEY (ROW_ID)
) ;

SQLALchemy Output
class Datetimeevents(Base):

__tablename__ = "datetimeevents"

row_id = Column(Integer, nullable=False, primary_key=True)
subject_id = Column(Integer, nullable=False)
hadm_id = Column(Integer)
icustay_id = Column(Integer)
itemid = Column(Integer, nullable=False)
charttime = Column(TIMESTAMP(0), nullable=False)
storetime = Column(TIMESTAMP(0), nullable=False)
cgid = Column(Integer, nullable=False)
value = Column(TIMESTAMP(0))
valueuom = Column(String(50), nullable=False)
warning = Column(SmallInteger)
error = Column(SmallInteger)
resultstatus = Column(String(50))
stopped = Column(String(50))


class Diagnoses_icd(Base):

__tablename__ = "diagnoses_icd"

row_id = Column(Integer, nullable=False, primary_key=True)
subject_id = Column(Integer, nullable=False)
hadm_id = Column(Integer, nullable=False)
seq_num = Column(Integer)
icd9_code = Column(String(10))

Pydantic Output
class Datetimeevents(BaseModel):

row_id: int
subject_id: int
hadm_id: int | None
icustay_id: int | None
itemid: int
charttime: datetime
storetime: datetime
cgid: int
value: datetime | None
valueuom: str
warning: int | None
error: int | None
resultstatus: str | None
stopped: str | None

class Config:
orm_mode = True


class Diagnoses_icd(BaseModel):

row_id: int
subject_id: int
hadm_id: int
seq_num: int | None
icd9_code: str | None

class Config:
orm_mode = True

Supported Queries
CREATE TABLE *

NOT NULL

CONSTRAINT UNIQUE
CONSTRAINT PRIMARY KEY

Constants
Make sure to edit the header constants under __ main __.py
ALCHEMY_HEADER = '''"""
SQLAlchemy Models
"""
from sqlalchemy import Column, Integer, String, CHAR, TIMESTAMP, SmallInteger
from sqlalchemy.dialects.postgresql import DOUBLE_PRECISION
from db.setup import Base

'''

You can always extend the supported types by editing the TYPE_LOOKUP dict in the types.py file.
TYPE_LOOKUP: dict[str, tuple[str, str]] = {
"INT": ("Integer", "int"),
"SMALLINT": ("SmallInteger", "int"),
"VARCHAR": ("String", "str"),
"TIMESTAMP": ("TIMESTAMP", "datetime"),
"DOUBLE": ("DOUBLE_PRECISION", "float"),
"CHAR": ("CHAR", "str"),
"TEXT": ("String", "str"),
}
"""Values are tuples of SQLAlchemy Model Type and Pydantic/Python Type."""

Notes

This utility is meant to be modified to match every case that's why the installation is in editable mode.
The __ main __ .py file contains all the necessary logic and header configs.
The types.py file contains a lookup table for the postgresql -> models type lookup.
The header assumes a path for the SQLAlchemy Base so make sure to change it to match yours, etc.

Plans

A more robust tool can be created which uses .toml files (or whatever) for configuration instead of python files so there is no need for editable installation.
The applications are Postgresql schemas with FastAPI but the tool can be generalized even further to support different types for other RDMS and frameworks.
I'll add support for more queries as I find them in my day-to-day work but feel free to contribute!

Changelog

0.1.2 - added ForeignKey Support
0.1.1 - added REAL -> Float support
0.1.0 - initial release