using sqlservice with Flask

[robinandeer]

Hi,

I really like the interface to SQLAlchemy models that the module exposes!

However, I'm stuck making it work with Flask (Flask-SQLAlchemy)

Is there a solution similar to Flask-Alchy perhaps?

[dgilland]

Something like this can get you started (it's untested but the approach should work):

from flask import current_app, _app_ctx_stack
from sqlservice import SQLClient


class FlaskSQLService(object):
    """Flask extension for sqlservice.SQLClient instance."""
    def __init__(self,
                 app=None,
                 model_class=None,
                 query_class=None,
                 session_class=None,
                 session_options=None):
        self.app = app
        self.model_class = model_class
        self.query_class = query_class
        self.session_class = session_class
        self.session_options = session_options or {}

        if app:
            self.init_app(app)

    def init_app(self, app):
        options = {}

        if self.model_class:
            options['model_class'] = self.model_class

        if self.query_class:
            options['query_class'] = self.query_class

        if self.session_class:
            options['session_class'] = self.session_class

        options['session_options'] = self.session_options

        # Set default scopefunc for SQLAlchemy session as app context stack
        # identifier function. This associates each session with the
        # appropriate Flask app context.
        options['session_options'].setdefault('scopefunc',
                                              _app_ctx_stack.__ident_func__)

        # Store SQLClient instances on app.extensions so it can be accessed
        # through flask.current_app proxy.
        app.extensions['sqlservice'] = SQLClient(app.config, **options)

        # Ensure that the session is removed on app context teardown so we
        # don't leave any sessions open after the request ends.
        @app.teardown_appcontext
        def shutdown_session(response_or_exc):
            self.remove()
            return response_or_exc

    def __getattr__(self, attr):
        """Proxy attribute access to SQLClient instance."""
        return getattr(current_app.extensions['sqlservice'], attr)

Usage:

from flask import Flask

from myapp.models import Model

app = Flask(__name__)
app.config.from_object('myapp.settings')

db = FlaskSQLService(model_class=Model)
db.init_app(app)

[robinandeer]

Perfect! Thank you for the quick response ⚡️

I will test it out

[robinandeer]

I'm getting a session-related error:

  File "/Users/robinandeer/miniconda/envs/order-portal/lib/python3.5/site-packages/sqlservice/client.py", line 293, in session
    return self._Session()
  File "/Users/robinandeer/miniconda/envs/order-portal/lib/python3.5/site-packages/sqlalchemy/orm/scoping.py", line 78, in __call__
    return self.registry()
  File "/Users/robinandeer/miniconda/envs/order-portal/lib/python3.5/site-packages/sqlalchemy/util/_collections.py", line 1025, in __call__
    val = self.registry.value = self.createfunc()
  File "/Users/robinandeer/miniconda/envs/order-portal/lib/python3.5/site-packages/sqlalchemy/orm/session.py", line 2826, in __call__
    return self.class_(**local_kw)
TypeError: __init__() got an unexpected keyword argument 'scopefunc'

I updated to the latest "SQLAlchemy" and "sqlservice" modules btw. Is there an earlier version of SQLAlchemy that it will work with? Or which "session_class" should I be using?

[robinandeer]

I had to update 2 lines in sqlservice/client.py:

def create_session(...):
    ...
    scopefunc = options.pop('scopefunc', None)
    session_factory = orm.sessionmaker(bind=bind,
                                       class_=session_class,
                                       **options)

    return orm.scoped_session(session_factory, scopefunc=scopefunc)

I could submit a PR if you like - otherwise it's a very small commit 😉

[dgilland]

Thanks for the fix! I've release v0.9.1 with that patch: https://pypi.python.org/pypi/sqlservice/0.9.1

I also noticed a minor issue with my FlaskSQLService implementation where I'm modifying the passed in session_options dict which shouldn't be done. Here's an updated version:

from flask import current_app, _app_ctx_stack
from sqlservice import SQLClient


class FlaskSQLService(object):
    """Flask extension for sqlservice.SQLClient instance."""
    def __init__(self,
                 app=None,
                 model_class=None,
                 query_class=None,
                 session_class=None,
                 session_options=None):
        self.app = app
        self.model_class = model_class
        self.query_class = query_class
        self.session_class = session_class
        
        # Set default scopefunc for SQLAlchemy session as app context stack
        # identifier function. This associates each session with the
        # appropriate Flask app context.
        self.session_options = {'scopefunc': _app_ctx_stack.__ident_func__}
        self.session_options.update(session_options or {})

        if app:
            self.init_app(app)

    def init_app(self, app):
        options = {}

        if self.model_class:
            options['model_class'] = self.model_class

        if self.query_class:
            options['query_class'] = self.query_class

        if self.session_class:
            options['session_class'] = self.session_class

        options['session_options'] = self.session_options

        # Store SQLClient instances on app.extensions so it can be accessed
        # through flask.current_app proxy.
        app.extensions['sqlservice'] = SQLClient(app.config, **options)

        # Ensure that the session is removed on app context teardown so we
        # don't leave any sessions open after the request ends.
        @app.teardown_appcontext
        def shutdown_session(response_or_exc):
            self.remove()
            return response_or_exc

    def __getattr__(self, attr):
        """Proxy attribute access to SQLClient instance."""
        return getattr(current_app.extensions['sqlservice'], attr)

[robinandeer]

Thanks again!

I'll update with your suggestions 👍 🥇

I guess you can consider the issue solved/closed

[DanCardin]

just leaving a note here for anyone else who might use doctest and hit this issue (but e.g. pytest + doctest flag will attempt to inspect this file regardless of doctests here):

Flask will complain to you about if you import current_app, _app_ctx_stack like in this file unless you are are using Werkzeug >= 0.12.
After that you'll still get an error unless you add a __wrapped__ attribute to the class.