cannot import dynamically generated sqlalchemy classes in flask-python

Question

Just started learning flask and python and I had to create 10+ tables(MySQLAlchemy) with the same structure and constraints. so after looking aroud for a quicker solution than declaring each class separately, I did the following in models/user.py:

#models/users.py
class exchangeapis(object):

email = db.Column(db.String(50), primary_key=True)
api =db.Column(db.String(100))
secret= db.Column(db.String(100))



@declared_attr.cascading 

# Makes sure all the dbs derived from this calss get the fK and PK

def email(cls):
        #this is necessary to propagate fk and pk to the instances
        return db.Column(db.String(50), db.ForeignKey('users.email'), primary_key=True)

#general methods to get class data       
def get_api(self):
    return self.api

def get_secret(self):
    return self.secret

exchange=['A','B','C']    
for exchange in exchanges:

cls=type(exchange.title(), (exchangeapis, db.Model), { '__tablename__' : str(exchange)+"_api"})
print(cls.__name__)

the

print(cls.__name__) 

gives me 'A', 'B', 'C' which I expect to be 'A_api', 'B_api'...

when I try to import it into my account/views.py file, with the following:

from ..models import User, A_api

I get " File "/home/puru991/flask-saas/flask-base/app/account/views.py", line 8, in from ..models import User,A_api

ImportError: cannot import name 'A_api'"

but if i define the class in the following way:

class A_api(db.Model):
__tablename__="A_api"
email = db.Column(db.String(50),ForeignKey("users.email"), primary_key=True)
api =db.Column(db.String(100))
secret= db.Column(db.String(100))

#general methods to get class data       
def get_api(self):
    return self.api

def get_secret(self):
    return self.secret

There are no errors. So my questions is how do I dynamically create classes based on exchangeapi(object) and import them successfully? I went through about 8-10 answers around here and the most i could learn was how to create them dynamically. I also learnt that MySQLAlchemy creates classes with the same name as the tablename, so I thought referring by the table name(which is "A_api") would do the trick. What am I missing?

Answer 1

The reason that you cannot import them is because in your users.py file you do not have a variable called A_api.

exchange=['A','B','C']    
for exchange in exchanges:
    cls=type(exchange.title(), (exchangeapis, db.Model), { '__tablename__' : str(exchange)+"_api"})
    print(cls.__name__)

In the code above you are creating three classes and each time you assign the class to a variable called cls. Once the for loop has finished your variable cls will be the last class created and technically you could import this variable into another module (don't do this though).

You can see this if you run the following code:

exchange=['A','B','C']    
for exchange in exchanges:
    cls=type(exchange.title(), (exchangeapis, db.Model), { '__tablename__' : str(exchange)+"_api"})

# This will print C_api as it's the last assignment to cls in the for loop
print(cls.__name__)

Regarding the table names/class names; you've got it the wrong way around, Flask-SQLAlchemy derives the table name from the class name, converting a class called “CamelCase” to a table called “camel_case”, unless you override the table name using __tablename__. Allowing the table name to be defined allows you to use a more Pythonic named class name but use a more conventional DB table name.

See a simple demonstration below of working code, two files models.py and run.py:

models.py

from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.ext.declarative import declared_attr

# This list will hold the dynamically created classes
dynamic_models = []

db = SQLAlchemy()


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

    email = db.Column(db.String(254), primary_key=True)
    # Other User fields


class ExchangeApiMixin(object):

    @declared_attr.cascading
    def email(cls):
        return db.Column(db.String(254), db.ForeignKey('users.email'), primary_key=True)

    api = db.Column(db.String(100))

    secret = db.Column(db.String(100))


for exchange in ['A_api', 'B_api', 'C_api']:
    cls = type(exchange, (ExchangeApiMixin, db.Model), {})
    print(cls.__name__)
    dynamic_models.append(cls)

# This will print C_api as it's the last assignment to cls in the for loop
print(cls.__name__)


# Individual variables also reference the dynamically created classes. The could be any legal python variable name
A_API = dynamic_models[0]
B_API = dynamic_models[1]
C_API = dynamic_models[2]

# Succinct way of creating the dynamic classes
(D_API, E_API, F_API) = [type(exchange, (ExchangeApiMixin, db.Model), {}) for exchange in ['D_api', 'E_api', 'F_api']]

run.py

import random, string
from flask import Flask, render_template_string
from models import db, dynamic_models, A_API, B_API, C_API, cls, D_API

app = Flask(__name__)
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///:memory:'
db.init_app(app)


_template = '''
    {% for rows in queries %}
        <table border='1'>
            <thead>
            <tr>
            <th>Class</th>
            <th>Email</th>
            <th>API</th>
            <th>Secret</th>
            </tr>
            </thead>    
            {% for row in rows %}
                <tr>
                <td>{{row.__class__.__name__}}</td>
                <td>{{row.email}}</td>
                <td>{{row.api}}</td>
                <td>{{row.secret}}</td>
                </tr>
            {% endfor %}
        </table>
        <p></p>
    {% endfor %}        
'''


@app.route('/')
def index():
    # display all the A_API, B_API, C_API, D_API instances
    a_rows = A_API.query.all()
    b_rows = B_API.query.all()
    c_rows = C_API.query.all()
    d_rows = D_API.query.all()
    return render_template_string(_template, queries=[a_rows, b_rows, c_rows, d_rows])


def build_sample_db():
    with app.app_context():

        db.drop_all()
        db.create_all()

        def create_from_model(Model, api_name):
            for _ in range(0, 10):
                _model = Model(
                    email='{mailbox}@example.com'.format(mailbox=''.join(random.choices(string.ascii_lowercase + string.digits, k=10))),
                    api=api_name,
                    secret='pa$$w0rd'
                )
                db.session.add(_model)

            db.session.commit()

        # Create instances using A_API, could pass dynamic_models[0]
        create_from_model(Model=A_API, api_name='A API Name')

        # Create using B_API instances
        create_from_model(Model=dynamic_models[1], api_name='B API Name')

        # Create using C_API instances using the cls variable (which is the 'C' class
        create_from_model(Model=cls, api_name='C API Name')

        # Create using D_API instances
        create_from_model(Model=D_API, api_name='D API Name')


if __name__ == '__main__':
    build_sample_db()
    app.run()