Dynamically Creating a database model using flask-sqlalchemy

In most cases you probably know the fields of your model before you write code. I recently landed into an issue where I had many columns fetched from different files and I didn't want to hard code them. I will share how I went about it in this blog post. This sounds like a pretty hard task at first but it turns out that you can use the type built-in-function(bif) to achieve it. Lets take a closer look at how the type bif works.

With A single argument

When given a single argument, It returns the type of the given object e.g.

a = 'my string'
# type_of_a will be str
type_of_a = type(a)

class Example:
    my_var = 12

b = Example()
# type_of_b will be Example
type_of_b = type(b)

You might be wondering how this is useful to our use case. Lets see another use case of the type bif

With three arguments

When given three arguments, it returns a new type. It acts a a dynamic form of creating classes. Below is its signature

type(name->str, bases->tuple, dict->dict)

Lets see what all these arguments represents

• name: Its a string and becomes the __name__ attribute
• bases: A tuple containing the base classes and becomes the __bases__ attribute.
• dict: A dictionary containing definitions of a class body and is copied to a standard dictionary to become the __dict__ attribute

For example, Creating a class statically in python

class Point(object):
    x = 1
    y = 0

Creating the same class dynamically using type

Point = type('Point', (object, ), dict(x=1, y=0))
# dict(x=1, y=1) can as well be replaced by {'x'=1, 'y'=0}

Creating a flask-sqlalchemy model

A model can be statically created using classes as seen below

# assuming we have our db already created in a module named app like db=SQLAlchemy()
# lets import our db
from app import db

class UserModel(db.Model):
    __tablename__ = 'users'

    id = db.Column(db.Integer, primary_key=True)
    id = db.Column(db.BigInteger, primary_key=True)
    user_name = db.Column(db.String(20), nullable=False, unique=True)
    email = db.Column(db.String(100), unique=True, nullable=False)
    password = db.Column(db.String(128), nullable=False)

The same model can be created using the type bif as seen below

from app import db

attributes = {
    '__tablename__' : 'users',
    'id' : db.Column(db.Integer, primary_key=True),
    'id' : db.Column(db.BigInteger, primary_key=True),
    'user_name' : db.Column(db.String(20), nullable=False, unique=True),
    'email' : db.Column(db.String(100), unique=True, nullable=False),
    'password' : db.Column(db.String(128), nullable=False),
}

UserModel = type('UserModel', (db.Model, ), attributes)

Till Now, you might be wondering, If I could do it with classes, why should I bother using type?????? Type comes handy if you have many attributes you don't want to type by hand. For example if you are reading them from a different source and you don't want to update the database Model by hand if the types change.

Lets see a simple example to mirror this. I will keep it simple to use a list as our data source and generate the attributes dynamically. I will assume you get back a list of data from a source that applies to your use case which is a list of subjects in our case. For simplicity, I will assume that all the attributes to be generated are of the same type which is as string of length one for our case considering that a result of each subject can be an A,B,C,D,E or F.

from app import db

# will assume I want a model to store student results and all are of the same types
subjects = ['English', 'Physics', 'Maths', 'Biology', 'Chemistry', 'History', 'Geography', 'Electronics']

# generating the attributes
# using dictionary comprehension to generate the dynamic attributes
dynamic_attributes = { subject: db.Column(db.String(1)) for subject in subjects}

# make the static attributes
static_attributes = {'__tablename__':'results', 'id' : db.Column(db.Integer, primary_key=True), }

# combine the attributes into 1 dictionary
all_attributes = {**dynamic_attributes, **static_attributes}

# make the database model
ResultsModel = type('ResultsModel', (db.Model, ), all_attributes)

If you did not understand the bit of dictionary comprehension. I wrote about comprehension in python in this blog post