Reflecting different databases in Flask factory setup

Question

I'd like to use Flask's application factory mechanism fpr my application. I have is that the databases I use within some blueprints are located differently, so I'm using binds for pointing to them. The tables itself are in production and already in use, so I need to reflect them in order to use them within my application.

Problem is that I can't get the reflect function working because of the application context. I always get the message, that I'm working outside the application context. I fully understand that and see, that db is really outside, but don't have any idea anymore on how to involve it.

I tried different variations on passing app via current_app to my models.py, but nothing was working.

config.py:

class Config(object):

    #Secret key
    SECRET_KEY = 'my_very_secret_key'

    ITEMS_PER_PAGE = 25

    SQLALCHEMY_BINDS = {
        'mysql_bind': 'mysql+mysqlconnector://localhost:3306/tmpdb'
    }
    SQLALCHEMY_TRACK_MODIFICATIONS = False

main.py:

from webapp import create_app

app = create_app('config.Config')

if __name__ == '__main__':
    app.run(debug=true)

webapp/init.py:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

def create_app(config_object):
    app=Flask(__name__)
    app.config.from_object(config_object)

    db.init_app(app)

    from main import create_module as main_create_module
    main_create_module(app)

    return app

webapp/main/init.py:

def create_module(app):
    from .controller import blueprint
    app.register(blueprint)

webapp/main/controller.py:

from flask import Blueprint, render_template, current_app as app
from .models import db, MyTable # <-- Problem might be here ...

bluerint = Blueprint('main', __name__)

@blueprint.route('/'):
def index():
    resp = db.session.query(MyTable)\
            .db.func.count(MyTable.versions)\
            .filter(MyTable.versions =! '')\
            .group_by(MyTable.name).all()
    if resp:
        return render_template('index.html', respo=respo)
    else:
        return 'Nothing happend'

webapp/main/models.py:

from .. import db # <-- and here ...

db.reflect(bind='mysql_bind')

class MyTable(db.Model):
    __bind_key__ = 'mysql_bind'
    __table__ = db.metadata.tables['my_table']

Expected result would be to get the reflection working in different blueprints.

Can you try this for me, I think you need to register the db with the app. db = SQLAlchemy(app) instead of db = SQLAlchemy(). Note I've found it easier to just use vanilla SQLAlchemy with flask in some cases. Contexts and wiring everything together become less painful. — Researcher, Jul 04 '19 at 10:06
That would be the normal behaviour, but in a factory setup you would rather go that way I described above. In fact, using `db.init_app(app)` is the same as `db = SQLAlchemy(app)`. In the example I described it would not make a difference since the db object would not be callable in `models.py` either way, it's only available using Flask and SQLAlchemy without factory setup (you called it "vanilla SQLAlchemy"). — brillenheini, Jul 04 '19 at 10:28
Well, I tried a lot, but this thing seems not to be solvable. The only solution is to skip `create_app` in `webapp/__init__.py` and initialize db already at import time. Thus it makes less headache. Good, old, plain "vanilla Flask". :) — brillenheini, Jul 04 '19 at 20:56
Try in `create_app()` after the line `db.init_app(app)` include `db.reflect(app=app)`. — PGHE, Jul 05 '19 at 00:37
I still think this is possible, but it is not necessary to use app factory if you don't have multiple app. Might have a play around with this later. — Researcher, Jul 05 '19 at 02:17

Researcher · Accepted Answer · 2019-07-09T12:18:21.360

Got it working, full solution here: https://github.com/researcher2/stackoverflow_56885380

I have used sqllite3 for the test, run create_db.py script to setup db. Run flask with debug.sh, since recent versions you can't seem to just app.run() inside __main__ anymore.

Explanation

As I understand it a blueprint is just a way to group together several views if you need to use them multiple times in a single app or across multiple apps. You can add different route prefix as you desire.

A db object is not associated with a blueprint, it is associated with an app, which provide the configuration information. Once inside the blueprint views you will have access to the db object with the relevant app context automatically available. Regarding the db.reflect, you need to make the call inside create_app and pass it the app object(preferred) or import the app inside the model which is spaghetti.

Multiple DBs can be accessed using binding as you've shown.

So your blueprints will have access to all tables imported and flask-sqlalchemy knows which db connection to use based on the binding.

I'm normally a fan of explicitly defining tables so you have access to the ORM objects and fields in code completion. Do you have lots of tables/fields or maybe you are creating something to query table metadata for total automation on any schema? Like a schema viewer or something like that.

This might be useful for others coming to this post: https://flask-sqlalchemy.palletsprojects.com/en/2.x/contexts/

score 2 · Answer 2 · answered Jul 05 '19 at 09:19

Brilliant! Thank you very much. Got it also working. Your tip gave me a hint to find another way:

@blueprint.route('/')
def index():

    # pushing app_context() to import MyTable
    # now I can use db.reflect() also in models.py
    with app.app_context():
        from .models import MyTable

    results = db.session.query(MyTable).all()
    print(results)
    for row in results:
        print (row)
        print(row.versions)
        print(row.name)

    if results:
        return render_template('my_table.html', results=results)
    else:
        return 'Nothing happend'

Then the reflection can be done inside models.py. The link you posted is really helpful, don't know why I did not stumble over it myself ...

Anyway, I do now have a lot more possibilities than before!

Cheers, mate!

Awesome! That context sensitive import is clever, never would have thought of that. — Researcher, Jul 05 '19 at 09:49
Depends on how often you may need to access to the database. In my code you may have to import from `models.py` in every route, with your method you gain access across all blueprints. — brillenheini, Jul 05 '19 at 11:13

Reflecting different databases in Flask factory setup

2 Answers2

Linked