How to Dynamically Create Tables With Column Names and Constraints From Dictionary Using SQLAlchemy Postgres ORM's Declarative Base?

Question

Setup: Postgres13, Python 3.7, SQLAlchemy 1.4

My question is regarding creating classes dynamically rather than relying on the contents of models.py. I have a schema.json file with metadata for many tables. Number of columns, column names, column constraints differ from table to table and are not known in advance.

The JSON gets parsed and its results are mapped to the ORM Postgres dialect (ex: {'column_name1': 'bigint'} becomes 'column_name1 = Column(BigInt)'). This creates a dictionary that contains a table name, column names, and column constraints. Since all the tables get passed the Augmented Base they automatically receive a PK id field.

I then pass this dictionary to a create_class function that uses this data to create the tables dynamically and commit these new tables to the database.

The challenge is that when I run the code the tables do get created but only with a single column - that of the PK id which it received automatically. All the other columns are ignored.

I suspect I am creating this error in the way that I am invoking the Session or Base or in the way I am passing the column constraints. I'm not sure how to indicate to the ORM that I am passing in Column and Constraint objects.

I have tried changing things like:

• the way the classes are created - passing in a Column object instead of a Column string ex: constraint_dict[k] = f'= Column({v})' VS constraint_dict[k] = f'= {Column}({v})'

• changing the way the column constraints are collected

• calling Base and create in different ways. I try to show these variations in the commented out lines in create_class below.

I can't work out which bits of interaction are creating this error. Any help is much appreciated!

Here is the code:

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": []
    }
  }
}

base.py

from sqlalchemy import Column, create_engine, Integer, MetaData
from sqlalchemy.orm import declared_attr, declarative_base, scoped_session, sessionmaker

engine = create_engine('postgresql://user:pass@localhost:5432/dev', echo=True)

db_session = scoped_session(
    sessionmaker(
        bind=engine,
        autocommit=False,
        autoflush=False
    )
)
# Augment the base class by using the cls argument of the declarative_base() function so all classes derived
# from Base will have a table name derived from the class name and an id primary key column.
class Base:
    @declared_attr
    def __tablename__(cls):
        return cls.__name__.lower()

    id = Column(Integer, primary_key=True)

metadata_obj = MetaData(schema='collect')
Base = declarative_base(cls=Base, metadata=metadata_obj)

models.py

from base import Base
from sqlalchemy import Column, DateTime, Integer, Text
from sqlalchemy.dialects.postgresql import UUID
import uuid


class NumLimit(Base):

    org = Column(UUID(as_uuid=True), default=uuid.uuid4, unique=True)
    limits = Column(Integer)
    limits_rate = Column(Integer)
    rate_use = Column(Integer)

    def __init__(self, org, limits, allowance_rate, usage, last_usage):
        super().__init__()
        self.org = org
        self.limits = limits
        self.limits_rate = limits_rate
        self.rate_use = rate_use

create_tables.py (I know this one is messy! Just trying to show all the variations attempted ...)

def convert_snake_to_camel(name):
    return ''.join(x.capitalize() or '_' for x in name.split('_'))


def create_class(table_data):
    constraint_dict = {'__tablename__': 'TableClass'}
    table_class_name = ''
    column_dict = {}

    for k, v in table_data.items():

        # Retrieve table, alter the case, store it for later use
        if 'table' in k:
            constraint_dict['__tablename__'] = v
            table_class_name += convert_snake_to_camel(v)

        # Retrieve the rest of the values which are the column names and constraints, ex: 'org = Column(UUID(as_uuid=True), default=uuid.uuid4, unique=True)'
        else:
            constraint_dict[k] = f'= Column({v})'
            column_dict[k] = v

    # When type is called with 3 arguments it produces a new class object, so we use it here to create the table Class
    table_cls = type(table_class_name, (Base,), constraint_dict)

    # Call ORM's 'Table' on the Class
    # table_class = Table(table_cls) # Error "TypeError: Table() takes at least two positional-only arguments 'name' and 'metadata'"

    # db_session.add(table_cls) # Error "sqlalchemy.orm.exc.UnmappedInstanceError: Class 'sqlalchemy.orm.decl_api.DeclarativeMeta'
                                # is not mapped; was a class (__main__.Metadata) supplied where an instance was required?"

    # table_class = Table(
    #    table_class_name,
    #    Base.metadata,
    #    constraint_dict) # Error "sqlalchemy.orm.exc.UnmappedInstanceError: Class 'sqlalchemy.orm.decl_api.DeclarativeMeta'
                          # is not mapped; was a class (__main__.Metadata) supplied where an instance was required?"

    # table_class = Table(
    #    table_class_name,
    #    Base.metadata,
    #    column_dict)  
    #    table_class.create(bind=engine, checkfirst=True) # sqlalchemy.exc.ArgumentError: 'SchemaItem' object, such as a 'Column' or a 'Constraint' expected, got {'limits': 'Integer'}
 
    # table_class = Table(
    #    table_class_name,
    #    Base.metadata,
    #    **column_dict) # TypeError: Additional arguments should be named <dialectname>_<argument>, got 'limits'

    # Base.metadata.create_all(bind=engine, checkfirst=True)
    # table_class.create(bind=engine, checkfirst=True)
    
    new_row_vals = table_cls(**column_dict)
    db_session.add(new_row_vals)  # sqlalchemy.exc.ArgumentError: 'SchemaItem' object, such as a 'Column' or a 'Constraint' expected, got {'limits': 'Integer'}

    db_session.commit()
    db_session.close()

Answer 1

I have created a self-contained example for you. This should give you the base building blocks to build this yourself. It includes the typemap, mapping type strings to sqlalchemy types and argumentmap, mapping non-sqlalchemy arguments to their sqlalchemy counterparts (required: True is nullable: False in sqlalchemy). This approach uses metadata to define the tables and then converts these into declarative mappings, as described in Using a Hybrid Approach with __table__ with the python type() function. These generated classes are then exported into the module scope's globals().

Not everything from your provided schema.json is supported, but this should give you a nice starting point.

from sqlalchemy import Column, Integer, Table, Text
from sqlalchemy.dialects.postgresql import UUID
from sqlalchemy.orm import declarative_base


def convert_snake_to_camel(name):
    return "".join(part.capitalize() for part in name.split("_"))


data = {
    "groupings": {
        "imaging": {
            "id": {"type": "integer", "primary_key": True},
            "owner": {"type": "uuid", "required": True, "index": True},
            "tags": {"type": "text", "index": True},
            "filename": {"type": "text"},
        },
        "user": {
            "id": {"type": "integer", "primary_key": True},
            "email": {"type": "text", "required": True, "unique": True},
            "name": {"type": "text"},
            "role": {
                "type": "text",
                "required": True,
                "index": True,
            },
        },
    },
}
Base = declarative_base()

typemap = {
    "uuid": UUID,
    "text": Text,
    "integer": Integer,
}

argumentmap = {
    "required": lambda value: ("nullable", not value),
}

for tablename, columns in data["groupings"].items():
    column_definitions = []
    for colname, parameters in columns.items():
        type_ = typemap[parameters.pop("type")]
        params = {}
        for name, value in parameters.items():
            try:
                name, value = argumentmap[name](value)
            except KeyError:
                pass
            finally:
                params[name] = value
        column_definitions.append(Column(colname, type_(), **params))

    # Create table in metadata
    table = Table(tablename, Base.metadata, *column_definitions)

    classname = convert_snake_to_camel(tablename)
    # Dynamically create a python class with definition
    # class classname:
    #     __table__ = table
    class_ = type(classname, (Base,), {"__table__": table})

    # Add the class to the module namespace
    globals()[class_.__name__] = class_