Associate "external' class model with flask sqlalchemy

Question

We use a central class model for a wide variety of python modules. This model is defined using SQLAlchemy. The classes all inherit from declarative_base.

For example, our model definitions look something like this:

Base = declarative_base()

class Post(Base):
    __tablename__ = 'Posts'
    id = Column(INT, primary_key=True, autoincrement=True)
    body = Column(TEXT)
    timestamp = Column(TIMESTAMP)
    user_id = Column(INT, ForeignKey('Users.uid'))

We have been building a flask web-application in which we employ this same model. We have discovered a tricky problem in that flask-sqlalchemy appears to be designed in such a way that it expects all classes used in its model to have been defined by passing in an active instance of the session. Here is an example of a "proper" flask-sqalchemy class model definition:

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:////tmp/test.db'
db = SQLAlchemy(app)


class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True)
    email = db.Column(db.String(120), unique=True)

Note that the above example for flask-sqlalchemy requires an already-instantiated sql session. This has horrified us, because we are completely at a loss as to how to integrate our SqlAlchemy model into flask. We really want to use the flask-security suite in particular.

This problem has been brought up before on SO. Here, for example: How to use flask-sqlalchemy with existing sqlalchemy model?

Our requirements are different from those of whoever accepted the response there. The response points out that one loses the ability to use User.query, but this is precisely one of the things we must retain.

It is not feasible to abandon our nice, elegant, central class model definition in favor of what flask-sqlalchemy appears to require. Is there any way for us to associate our model with the SQLAlchemy() object? Bonus points for getting us the .query() method on our classes which appears to be required by flask-security.

Timbale

Answer 1

Solution:

As of today, the best way to do this is as follows:

Implement or import sqlalchemy base

from sqlalchemy.ext.declarative import declarative_base

base = declarative_base()


class Base(base):

    __abstract__ = True
    uid = Column(Integer, primary_key=True, autoincrement=True)

Register the external base:

from flask_sqlalchemy import SQLAlchemy
from model.base import Base

app = Flask(__name__)
db = SQLAlchemy(app, model_class=Base)

Archived for posterity:

I spent a lot of time looking for an answer to this. This is a lot easier to do today than it was when I originally asked the question, but it still isn't exactly simple.

For anyone who decides to do security themselves, I recommend the following excellent exposition of common design patterns which use flask, but which avoid employing unnecessary dependencies like flask-security: https://exploreflask.com/users.html

UPDATE: For anyone interested, a patch has been in the works for some time related to this. As of now it still isn't released, but you can check its progress here: https://github.com/mitsuhiko/flask-sqlalchemy/pull/250#issuecomment-77504337

UPDATE: I have taken the code from the above mentioned patch and created a local override for the SQLAlchemy object which allows one to register an external base. I think this is the best option available until such time as FSA gets around to adding this officially. Here is the code from that class for anyone interested. Tested working with Flask-SqlAlchemy 2.2

Patching in register_external_base:

import flask_sqlalchemy
'''Created by Isaac Martin 2017. Licensed insofar as it can be according to the standard terms of the MIT license: https://en.wikipedia.org/wiki/MIT_License. The author accepts no liability for consequences resulting from the use of this software. '''
class SQLAlchemy(flask_sqlalchemy.SQLAlchemy):
    def __init__(self, app=None, use_native_unicode=True, session_options=None,
                 metadata=None, query_class=flask_sqlalchemy.BaseQuery, model_class=flask_sqlalchemy.Model):

        self.use_native_unicode = use_native_unicode
        self.Query = query_class
        self.session = self.create_scoped_session(session_options)
        self.Model = self.make_declarative_base(model_class, metadata)
        self._engine_lock = flask_sqlalchemy.Lock()
        self.app = app
        flask_sqlalchemy._include_sqlalchemy(self, query_class)
        self.external_bases = []

        if app is not None:
            self.init_app(app)

    def get_tables_for_bind(self, bind=None):
        """Returns a list of all tables relevant for a bind."""
        result = []
        for Base in self.bases:
            for table in flask_sqlalchemy.itervalues(Base.metadata.tables):
                if table.info.get('bind_key') == bind:
                    result.append(table)

        return result

    def get_binds(self, app=None):
        """Returns a dictionary with a table->engine mapping.
        This is suitable for use of sessionmaker(binds=db.get_binds(app)).
        """
        app = self.get_app(app)
        binds = [None] + list(app.config.get('SQLALCHEMY_BINDS') or ())
        retval = {}
        for bind in binds:
            engine = self.get_engine(app, bind)
            tables = self.get_tables_for_bind(bind)
            retval.update(dict((table, engine) for table in tables))
        return retval

    @property
    def bases(self):
        return [self.Model] + self.external_bases

    def register_base(self, Base):
        """Register an external raw SQLAlchemy declarative base.
        Allows usage of the base with our session management and
        adds convenience query property using self.Query by default."""

        self.external_bases.append(Base)
        for c in Base._decl_class_registry.values():
            if isinstance(c, type):
                if not hasattr(c, 'query') and not hasattr(c, 'query_class'):
                    c.query_class = self.Query
                if not hasattr(c, 'query'):
                    c.query = flask_sqlalchemy._QueryProperty(self)

                    # for name in dir(c):
                    #     attr = getattr(c, name)
                    #     if type(attr) == orm.attributes.InstrumentedAttribute:
                    #         if hasattr(attr.prop, 'query_class'):
                    #             attr.prop.query_class = self.Query

                    # if hasattr(c , 'rel_dynamic'):
                    #     c.rel_dynamic.prop.query_class = self.Query

To be used like so:

app = Flask(__name__)
db = SQLAlchemy(app)
db.register_base(base)

Timbale

This is fantastic! Can you please put a license header on the code so that we can use it. – Patinated

You can use code provided on SO without an explicit license. However, I added on in a docstring for you if it helps you out. – Timbale

Crazy, after searching for quite some time on this it appears there still hasn't been a PR to address this shortcoming. I thank you very very much merchoir55! One additional issue I encountered is that current versions requires a private attribute _engine_options. Once I added self._engine_options = engine_options or {} to the constructor of the above override it worked like a charm! Delighted too, I have been bouncing off this issue for years, never really committing to getting past it because it generally lives in library land problems for me ;) – Gileadite

Overrides are no longer required. Note that the overrides are in a section that is 'archived for posterity'. The best way to do this now is to pass the base into the constructor as detailed in the section at the top of my answer. – Timbale

Is there anything to consider when applying your example, e.g. do I need __abstract__ = True or that uid column? Or would it be enough to use base = declarative_base() and put that in as db = SQLAlchemy(app, model_class=base) and make sure all my models inherit from base, too? – Ataghan

Your models simply need to inherit from base. – Timbale

There's nothing wrong with posting two answers to the same question if you've found a new approach. Also, it would be nice to mirror the core of the question by demonstrating that you're actually using Base and not db.Model in your models. – Eringo