python - How to fix my approach to use the same models in vanilla SQLAlchemy and Flask-SQLAlchemy?

Question

I came across several approaches on how to use the vanilla SQLAlchemy models in Flask-SQLAlchemy.

It works like a charm to use models that inherit from Base with Flask-SQLAlchemy.

But I really like that convenience stuff...

Job.query.all() # Does not work
db.session.query(Job).all() # Works

So I started to work on this and put together some code, but I am stuck and need some help to get this nice and clean.

The following block is a general definition that does not inherit from either. It is imported and supposed to be used from Flask-SQLAlchemy and vanilla SQLAlchemy at some point.

class VanillaMachine():

    __tablename__ = 'machine'

    id = Column(Integer, primary_key=True)
    name = Column(String(100))
    status = Column(Integer)

And there is a factory that takes either db.Model or Base and return Machine with the correct parent:

class MachineFactory:

    def __init__(self, *args, **kwargs):
        pass

    def __new__(cls, *args, **kwargs):

        return type('Machine',(object, VanillaMachine, args[0]), VanillaMachine.__dict__.copy())

I am quite sure that there's something off with that code, but I am not sure where.

If I use it like

db = SQLAlchemy()

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()#

Machine1 = MachineFactory(db.Model)
Machine2 = MachineFactory(Base)

there is a error message

sqlalchemy.exc.ArgumentError: Column object 'id' already assigned to Table 'machine'

Can help me to get this straight in a nice, reliable way?

I know that you could just use a function, pass the parent as argument into VanillaMachine and use some if statement, but that would be too straightforward, right? :)

Edit:

Other approaches I came across are

1. using the Flask context to use Flask-SQLAlchemy models

   with app.app_context():
       pass
   
   or 
   
   app.app_context().push()
   

But this is too focused on Flask for me and does not allow to clearly separate the models, make them independent and adjust to the context.

2. supplying an alternative Base class to db = SQLAlchemy(app, model_class=Base), see here. This might work for me, but I did not evaluate this so far.

Answer 1

I found a good solution inspired by a Factory pattern and Declarative Mixins as mentioned in the SQLAlchemy docs.

For complex multi-level inheritance scenarios a different approach is needed, using @declared_attr.cascading.

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy import Column, Integer, String
from sqlalchemy import MetaData

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

SQLALCHEMY_DATABASE_URI = 'sqlite:///' + '/tmp/test_app.db'
engine = create_engine(SQLALCHEMY_DATABASE_URI, echo=True)

# for vanilla
Base = declarative_base()

# for Flask (import from app once initialized)
db = SQLAlchemy()


class MachineMixin:

    __tablename__ = 'machine'
    id = Column(Integer, primary_key=True)
    name = Column(String(100))
    status = Column(Integer)


class ModelFactory:

    @staticmethod
    def create(which_model, which_parent):

        if which_parent == 'flask_sqlalchemy':

            parent = db.Model

        elif which_parent == 'pure_sqlalchemy':

            parent = Base

        # now use type() to interit, fill __dict__ and assign a name
        obj = type(which_model.__name__ + '_' + which_parent,
                    (which_model, parent),
                    {})
        return obj


test_scenario = 'pure_sqlalchemy' # 'flask_sqlalchemy'

Machine = ModelFactory.create(MachineMixin, test_scenario)

if test_scenario == 'flask_sqlalchemy':

    db.metadata.drop_all(bind=engine)
    db.metadata.create_all(bind=engine)

elif test_scenario == 'pure_sqlalchemy':

    Base.metadata.drop_all(bind=engine)
    Base.metadata.create_all(bind=engine)


Session = sessionmaker(bind=engine)
session = Session()
session.add(Machine(name='Bob', status=1))
session.commit()