Flask and SQLAlchemy and the MetaData object

Question

it's the first time i am using this environment.

The part of SQLAlchemy i am willing to use is just the one that allows me to query the database using Table objects with autoload = True. I am doing this as my tables already exist in the DB (mysql server) and were not created by defining flask models.

I have gone through all the documentation and i don't seem to find an answer. Here is some code:

app = Flask(__name__)
app.config.from_object(__name__)

metadata = None

def connect_db():
    engine = create_engine(app.config['DATABASE_URI'])
    global metadata
    metadata = MetaData(bind=engine)
    return engine.connect()


@app.before_request
def before_request():
    g.db = connect_db()


@app.teardown_request
def teardown_request(exception):
    g.db.close()

Now you could be wondering why i use that global var named metadata. Ok some more code:

@app.route('/test/<int:id>')
def test(test_result_id):

    testTable = Table('test_table', metadata , autoload=True)

As you can see i need that object to be global in order to access it from within a function.

Also I am declaring the same var testTable in each function that needs it. I have the feeling this is not the right approach. I coudn't find any best practice advice for a case like mine.

Thanks all!

Answer 1

Have you seen this snippet in the SQLAlchemy docs?

Maybe this would work:

# This is fine as a global global
metadata = MetaData()

@app.before_first_request
def autoload_tables():
    meta.reflect(bind=g.db.bind)

@app.route('/')
def index():
    users_table = meta.tables['users']

That way your tables are reflected only once per process which is probably what you want. Note that your engine should be a global too, so you needn't create a new engine in @app.before_request - app creation is a more appropriate place.

If your case is very special you might need one engine per request, in which case you should consider the ThreadLocalMetaData class.