How to parse a JSON schema file and create new Python classes dynamically with many column constraints?

Question

I am using the SQLAlchemy 1.4 ORM with postgres13 and Python 3.7.

EDITED FOR CLARITY AND REFINEMENT:

To stand up the project and test it out, these 3 files are working well:

base.py --> setting up SQLAlchemy Engine and database session

models.py --> a User class is defined with a number of fields

inserts.py --> Creating instances of the User class, adding and committing them to database

This all works well provided models.py has a hardcoded Class already defined (such as the User Class).

I have a schema.json file that defines database schema. The file is very large with many nested dictionaries. The goal is to parse the json file and use the given schema to create Python Classes in models.py (or whichever file) automatically.

An example of schema.json:

  "groupings": {
    "imaging": {
      "owner": { "type": "uuid", "required": true, "index": true },
      "tags": { "type": "text", "index": true }
      "filename": { "type": "text" },
    },

    "user": {
      "email": { "type": "text", "required": true, "unique": true },
      "name": { "type": "text" },
      "role": {
        "type": "text",
        "required": true,
        "values": [
          "admin",
          "customer",
        ],
        "index": true
      },
      "date_last_logged": { "type": "timestamptz" }
    }
  },

  "auths": {
    "boilerplate": {
      "owner": ["read", "update", "delete"],
      "org_account": [],
      "customer": ["create", "read", "update", "delete"]
     },

      "loggers": {
      "owner": [],
      "customer": []
    }
  }
}

The models' Classes need to be created on the fly by parsing the json because the schema might change in the future and manually hardcoding 100+ classes each time doesn't scale.

I have spent time researching this but have not been able to find a completely successful solution. Currently this is how I am handling the parsing and dynamic table creation.

I have this function create_class(table_data) which gets passed an already-parsed dictionary containing all the table names, column names, column constraints. The trouble is, I cannot figure out how to create the table with its constraints. Currently, running this code will commit the table to the database but in terms of columns, it only takes what it inherited from Base (automatically generated PK ID).

All of the column names and constraints written into the constraint_dict are ignored.

The line #db_session.add(MyTableClass) is commented out because it errors with "sqlalchemy.orm.exc.UnmappedInstanceError: Class 'sqlalchemy.orm.decl_api.DeclarativeMeta' is not mapped; was a class (main.MyTableClass) supplied where an instance was required?"

I think this must have something to do with the order of operations - I am creating an instance of a class before the class itself has been committed. I realise this further confuses things, as I'm not actually calling MyTableClass.

def create_class(table_data):

    constraint_dict = {'__tablename__': 'myTableName'}

    for k, v in table_data.items():

        if 'table' in k:
            constraint_dict['__tablename__'] = v

        else:
            constraint_dict[k] = f'Column({v})'


    MyTableClass = type('MyTableClass', (Base,), constraint_dict)
    Base.metadata.create_all(bind=engine)

    #db_session.add(MyTableClass)
    db_session.commit()
    db_session.close()

    return

I'm not quite sure what to take a look at to complete this last step of getting all columns and their constraints committed to the database. Any help is greatly appreciated!

Answer 1

This does not answer your question directly, but rather poses a different strategy. If you expect the json data to change frequently, you could just consider creating a simple model with an id and data column, essentially using postgres as a json document store.

from sqlalchemy.dialects.postgresql import JSONB

class Schema(db.Model):
    id = db.Column(db.Integer(), nullable=False, primary_key=True, )
    data= db.Column(JSONB)

sqlalchemy: posgres dialect JSONB type

The JSONB data type is preferable to the JSON data type in posgres because the binary representation is more efficient to search through, though JSONB does take slightly longer to insert than JSON. You can read more about the distinction between the JSON and JSONB data types in the posgres docs

This SO post explains how you can use SQLAlchemy to perform json operations in posgres: sqlalchemy filter by json field