How to use flask-sqlalchemy with existing sqlalchemy model?

Question

I've read flask-sqlalchemy or sqlalchemy which recommends use of flask-sqlalchemy with flask. I want to follow this approach.

However, I have an existing model written for command line scripts which is based on sqlalchemy's declarative_base, e.g.,

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()   # create sqlalchemy Base class
              :
class Runner(Base):
    etc.

I want to still be able to use the command line scripts with this model, but also want to build a web application around the model.

Is there a way to extend the existing model, to gain the benefit of using the flask-sqlalchemy extension? Or should I just roll my own, and use sqlalchemy's ScopedSession?

Tonedeaf

would it be a problem to rewrite the command line scripts using the flask-sqlalchemy model? – Toplevel

I don't know -- haven't used flask before, or flask-alchemy and don't know the side-effects. Would the scripts need to be rewritten or just the model which is imported? I should also have mentioned that I have been using alembic to track database changes, in case there are any compatibility issue there. – Tonedeaf

Update 2020: This could be a promising way to solve this: #28789563 – Purveyor

Answer 1

Currently, this is something that is not well supported, but not impossible to do. See this issue on the Flask-SQLAlchemy issue list, which admits that the current implementation of the extension makes this situation more of a headache than they think it should. Hopefully this will be better supported in the future (once a solid migration path and new API is determined).

That issue gives the following code sample:

from flask import Flask
from models import Base, User # Your non-Flask-SQLAlchemy models...
from flask_sqlalchemy import SQLAlchemy

app =  Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:////tmp/test.db'
db = SQLAlchemy(app)

@app.before_first_request
def setup():
    # Recreate database each time for demo
    Base.metadata.drop_all(bind=db.engine)
    Base.metadata.create_all(bind=db.engine)
    db.session.add(User('Bob Jones', '[email protected]'))
    db.session.add(User('Joe Quimby', '[email protected]'))
    db.session.commit()

@app.route('/')
def root():
    users = db.session.query(User).all()
    return u"<br>".join([u"{0}: {1}".format(user.name, user.email) for user in users])

if __name__ == '__main__':
    app.run('127.0.0.1', 5000) 

There are a few things to note here:

First, you lose the ability to do User.query (because User was created using its own declarative base), along with all of the other stuff that Flask-SQLAlchemy's db.Model gives you (such as the ability to auto-generate the table names and methods like first_or_404()).

Second, any time you need to do things that involve the metadata (such as drop_all or create_all), you cannot use the Flask-SQLAlchemy methods. You must use the original metadata, bound to the Flask-SQLAlchemy engine.

I haven't tried this myself, so I'm not sure if there are any other gotchas to this approach. You might want to participate in that ticket if you find any.

Phylys

Thanks for the pointer to the issue. I can do the experiment, but definitely easier if you know this answer: If I rewrite the model using flask-alchemy's db.model, rather than Base, with that break the command line scripts? – Tonedeaf

@Lou_K: Probably not "break" per se, but when you try to run them you might have to do some additional work setting up the Flask App before the meat of the script could run. – Phylys

@Mark_Hildreth: I might try that. If that becomes onerous, maybe I can make the same source file work for the model, but depend on some environment information (some kind of configuration external to python, set by the command line or by the web app) to import the right stuff, and declare Base appropriately depending on whether it is used by the command line script or the flask app. Doesn't feel very elegant, though. And this needs to be thought through carefully to make sure the two systems don't collide with each other somehow. – Tonedeaf