flask sqlalchemy - dynamically generate data model based on columns in the database

Question

Is there a way to generate db model dynamically based on the columns in the database table for Flask SQLAlchemy?

I have an application to display data in from a database table, but the column name would change at times and break my app. I would like to have a way to generate the data model dynamically based on the actual column names in the database.

I currently explicitly declare all the columns as below.

class MyDbModel(db.Model):

    __tablename__ = 'my_table'

    id = db.Column('id', db.NVARCHAR(length=300), primary_key=True)
    name= db.Column('name', db.NVARCHAR(length=300),
                          nullable=True)

I'm not very familiar with sqlalchamy, I have tried the following but is getting an error as below, and I'm not sure if this is the right way to do it.

could not assemble any primary key columns for mapped table

from sqlalchemy import Table, Column
from sqlalchemy.orm import mapper

    db = SQLAlchemy()

    class MyDbModel(db.Model):
        __tablename__ = 'my_table'

        def __init__(self):
            #helper function to get column headers of a db table
            table_cols = get_headers_or_columns(
                'my_table'
            )
            t = Table(
                'my_table', db.metadata,
                Column('id', db.NVARCHAR(length=300), primary_key=True),
                *(Column(table_col, db.NVARCHAR(length=300)) for table_col in table_cols)
            )

            mapper(self, t)

Any help is appreciated!

qshng qshng · Accepted Answer · 2019-03-12T00:50:03

I figured this out, in case anyone needed. It's really simple, use a helper function to get the column headers of your table, then just use setattr to set the attributes of the class.

from app import db

class MyDbModel(db.Model):
        pass

def map_model_attrs(model, table):
    """
    :param model: your db Model Class
    :param table your db table name
    """

    table_cols = get_headers_or_columns(table)

    for col in table_cols:
        setattr(
            model, col, db.Column(col, db.NVARCHAR(length=300),
                                  nullable=True)
        )

map_mode_attrs(MyDbModel, 'my_table')

flask sqlalchemy - dynamically generate data model based on columns in the database

1 Answers