Share sqlalchemy models between flask and other apps

Question

I have a running Flask application that is set up according to a combination of best practices we found online and in Miguel Grinberg's "Flask Web Development" book.

We now need a second Python application, that is NOT a web app, and that needs access to the same models as the Flask application. We wanted to re-use the same models ofcourse, so both apps can benefit from the shared code.

We have removed dependencies on the flask-sqlalchemy extention (which we used before, when we had just the Flask application). And replaced it with the SQLalchemy Declarative extension described here, which is a bit simpler (Flask-SQLalchemy adds a few specific things to standard SQLAlchemy)

In line with the example we have created a database.py file in the root. In our case there are two things different from the Declarative extension example: I put the engine and session in a class, because all of our models use db.session, instead of db_session, and I pass a dictionary with configuration values to the init(), so that I can re-use this database.py from both Flask as well as another application, using a different configuration. it looks like this:

from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy.ext.declarative import declarative_base


class Database(object):

    def __init__(self, cfg):
        self.engine = create_engine(cfg['SQLALCHEMY_DATABASE_URI'], convert_unicode=True)
        self.session = scoped_session(sessionmaker(autocommit=False, autoflush=False, bind=self.engine))

    class Model(object):
        pass

Base = declarative_base()

So now we come to the actual problem. Flask creates a dictionary-like object containing configuration options, and adds them as a property to the app-instance. It loads them from an instance folder, a config.py in the root of the site and from environment variables. I need to pass in the configuration dictionary from Flask, so I need Flask to FIRST load and assemble the configuration, and after that initialise the database, and have a (configured) db object in the root of the app file. However, we follow the Application factory pattern, so we can use different configurations for different situations (test, production, development).

This means our app/__init__.py looks something like this (simplified):

from flask import Flask
from database import Database
from flask.ext.mail import Mail
from flask_bcrypt import Bcrypt
from config import config

mail = Mail()
bcrypt = Bcrypt()


def create_app(config_name):

    app = Flask(__name__, instance_relative_config=True)

    if not config_name:
        config_name = 'default'
    app.config.from_object(config[config_name])
    app.config.from_pyfile('config.py')
    config[config_name].init_app(app)

    db = Database(app.config)

    mail.init_app(app)
    bcrypt.init_app(app)

    @app.teardown_appcontext
    def shutdown_session(exception=None):
        db.session.remove()

    from main import main as main_blueprint
    app.register_blueprint(main_blueprint)

    return app

But the db (that the models import from ..), now needs to be inside the create_app() function, because that's where Flask loads the configuration. If I would instantiate the db object outside of the create_app() function, it will be importable from the models, but it is not configured!

an example model looks like this, and as you can see, it expects a "db" in the root of the app:

from . base_models import areas
from sqlalchemy.orm import relationship, backref
from ..utils.helper_functions import newid
from .. import db


class Areas(db.Model, areas):
    """Area model class.
    """
    country = relationship("Countries", backref=backref('areas'))

    def __init__(self, *args, **kwargs):
        self.area_id = newid()
        super(Areas, self).__init__(*args, **kwargs)

    def __str__(self):
        return u"{}".format(self.area_name).encode('utf8')

    def __repr__(self):
        return u"<Area: '{}'>".format(self.area_name).encode('utf8')

So my question is, how can I have a db instance that can be configured externally (by either Flask or another app), and still use the Application Factory Pattern?

edit: The code-example was incorrect, it had an import for Flask-SQLalchemy which was replaced by from database import Database. Sorry for any confusion.

Answer 1

The Flask-SQLAlchemy extension, like most Flask extensions, should be created outside the factory, then initialized in the factory using init_app. This is so that you can use the db object before an app is created.

db = SQLAlchemy()

def create_app():
    app = Flask(__name__)
    db.init_app(app)
    return app

Your Flask app, like any properly designed Python project, should be an installable package. This is simple to do: make sure your project layout makes sense, then add a basic setup.py file.

project/
    my_flask_package/
        __init__.py  # at the most basic, this contains create_app and db
    setup.py

from setuptools import setup, find_packages

setup(
    name='my_flask_package',
    version='1.0',
    packages=find_packages(),
    install_requires=['flask', 'flask-sqlalchemy'],
)

$ python setup.py sdist

Now you can install your Flask app, along with it's database, for use in other projects. Install and import it in your second project's virtualenv, then create and push an app to initialize it.

$ pip install my_flask_package-1.0.tar.gz

from my_flask_package import db, create_app
create_app().app_context().push()
db.session.query(...)

If you're concerned about overhead involved in creating your application, you could add arguments to the create_app function to control what gets initialized. For most cases this shouldn't be an issue though.

Answer 2

I ran into the same problem.

If you turn on "SQLALCHEMY_ECHO" you'll likely see that a new transaction is started but the corresponding COMMIT/ ROLLBACK is missing.

For what i found out, it has something to do with two SQLAlchemy instances which you also create, once in your model file and once in your web.py. Most likely it's because you interact with your web.py's session and if you query your models there is some context switched which will receive the COMMIT.

I fixed the issue by importing "db" from models and then init it by calling db.init_app(app). According to the logs, committing now works fine.

The @app.teardown_appcontext shouldn't be necessary as it is set up in Flask-SQLAlchemy's SQLAlchemy class (https://github.com/mitsuhiko/flask-sqlalchemy/blob/master/flask_sqlalchemy/init.py)

Answer 3

You can easily share. I will show how. Considering this Flask app:

.
├── config.py
├── db
│   └── test.db
├── do_somenthing2.py ============> Here is run some script 2
├── do_something.py   ============> Here is run some script
├── machinelearning
│   ├── models
│   │   ├── restore.py
│   │   ├── train.py
│   │   └── utils.py
│   └── save
│       └── test.ckpt
├── runserver.py ============> Here is run your app
├── test.py
└── web
    ├── __init__.py
    ├── api
    │   ├── __init__.py
    │   ├── app.py  ============> Here is app = Flask(__name__)
    │   ├── client.py
    │   ├── models.py ==========> Here is db = SQLAlchemy(app)
    │   ├── sample.json
    │   └── utils.py
    └── frontend
        ├── __init__.py
        └── routes.py

runserver.py

import os

from config import DEBUG

from web.api.app import app
from web.api.client import *

if __name__ == "__main__":
    app.run(debug=DEBUG)

OK. Now you want do use the same models to do another thing. For example: train a machine, serve and save in database (ORM) using the same models.

You can import the app and use the app.test_request_context(). Like that:

do_something.py

from web.api.app import app from web.api.models import db, user

def do_something():
    q = db.session.query(User)\
        .filter(User.Name.ilike('Andre'))
    for i in q.all():
        print (i.Name)    

with app.test_request_context():
    do_something()

do_something2.py (real example)

from web.api.app import app
from web.api.models import *

def save(df):

    passengers = []

    records = df.to_dict('records')

    for row in records:
        p = Passenger(row)
        passengers.append(p)

    for p in passengers:
        db.session.add(p)

    db.session.commit()

from ml.models import train, restore

with app.test_request_context():
    print ('Trainning model. It will take a while... (~ 5 minutos)')
    train.run()
    print ('Saving model...')
    save(restore.run())
    print ('Saved!')

Many answers recommend use (Importing files from different folder):

import sys
sys.path.append('../')

But I disagree when you have a Flask app and other scripts, because you will get crazy solving the relative references.

The approach to install your Flask app, along with it's database, for use in other projects, is another option.

Here you can find a documentation about the packages and modules.

Packages are a way of structuring Python’s module namespace by using “dotted module names”. For example, the module name A.B designates a submodule named B in a package named A. Just like the use of modules saves the authors of different modules from having to worry about each other’s global variable names, the use of dotted module names saves the authors of multi-module packages like NumPy or Pillow from having to worry about each other’s module names.