CODEWITHSUNDEEP
pythonpython-3.xflaskflask-sqlalchemy
brillenheini
brillenheini

Reputation: 963

Reflecting different databases in Flask factory setup

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.

Upvotes: 3

Views: 1970

Answers (2)

Researcher
Researcher

Reputation: 1046

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/

Upvotes: 3

brillenheini
brillenheini

Reputation: 963

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!

Upvotes: 3

Related Questions