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?

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.