Using sqlalchemy declarative in the external worker

Question

I have a web app written in python using Flask and sqlalchemy. This application runs on Heroku, and as a worker I use the ironworker extension, I have all my models defined in the models module like this:

from app import db

class Player(db.Model):
    __tablename__ = 'players'
    id = db.Column(db.Integer, primary_key=True)
    ....
    type = db.Column(db.String(50))

    def __init__(self, ....):
        ....

    __mapper_args__ = {
        'polymorphic_identity':'player',
        'polymorphic_on':type
    }

class Outfielder(Player):
    __tablename__ = 'outfielders'
    id = db.Column(db.Integer, db.ForeignKey('players.id'), primary_key=True)

    __mapper_args__ = {
        'polymorphic_identity':'outfielder'
    }

class Goalkeeper(Player):
    __tablename__ = 'goalkeepers'
    id = db.Column(db.Integer, db.ForeignKey('players.id'), primary_key=True)
    __mapper_args__ = {
        'polymorphic_identity':'goalkeeper'
    }

(Note how this imports db from app) I can create the instances of these models in my application by instantiating the models, like so:

db = SQLAlchemy(app)

gk = models.Goalkeeper('John', 'Snow', 1, 1, 3456, 67, 55, 187)
of = models.Outfielder('Gary', 'Lineker', 1, 1, 3999, 77, 78, 176)

db.session.add(gk)
db.session.add(of)
db.session.commit()

Now, what I would like to do is to be able to create the instances of these models on my external worker. The only thing that prevents me from doing so is the dependency on my app module. I don't feel like this dependency is justified and would like to find a better design approach to be able to simply export my models module to the worker and init sqlalchemy instance there and create instances of my models and save them to the database exactly the same way I can do it in the application itself.

Note that I can already access my database from the worker like this:

db = create_engine(db_url)
metadata = MetaData(db)

print "connection to the database established"

players = Table('players', metadata, autoload=True)
s = players.select()

I'm just looking for a better way of reflecting my model structure on the worker. Specifically, I'm thinking of the scenario when my models change and I'll have to maintain the code on both ends separately. If I manage to reuse the models and use declarative on both ends, then there will be no maintenance on the worker side what so ever.

Probably the best way is to define your models separate from Flask-SQLAlchemy, and allow Flask-SQLAlchemy to use the models defined in a separate module rather than having Flask-SQLAlchemy make you use the built-in db.Model base. Unfortunately, the design of Flask-SQLAlchemy does not allow for this easily, an issue which will hopefully [be addressed in the next version](https://github.com/mitsuhiko/flask-sqlalchemy/issues/98). See [this question](http://stackoverflow.com/questions/19119725/how-to-use-flask-sqlalchemy-with-existing-sqlalchemy-model) as well. — Mark Hildreth, Feb 03 '14 at 21:55

score 4 · Accepted Answer · edited May 23 '17 at 12:28

The db.Model is a SQLAlchemy declarative_base(), so you can use your models like even with plain SQLAlchemy.

This example works (YourModel is a Flask-SQLAlchemy model):

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

import settings
from models import YourModel

engine = create_engine(settings.SQLALCHEMY_DATABASE_URI)
Session = sessionmaker(bind=engine)    
session = Session()

# do something with your session
session.query(YourModel).first()

You have to decouple db from app, so you can import it without triggering app creation, using db.init_app.

Of course you should be careful here. If you load your settings dynamically, maybe you'll load it from the wrong place with import settings instead of current_app.config, but looks like you already have your worker adressed, so it is not an issue for you.

Another option is create another Flask app instead of your huge web app.

From Flask-SQLAlchemy docs (http://pythonhosted.org/Flask-SQLAlchemy/api.html#flask.ext.sqlalchemy.SQLAlchemy):

You can still use sqlalchemy and sqlalchemy.orm directly, but note that Flask-SQLAlchemy customizations are available only through an instance of this SQLAlchemy class. Query classes default to BaseQuery for db.Query, db.Model.query_class, and the default query_class for db.relationship and db.backref. If you use these interfaces through sqlalchemy and sqlalchemy.orm directly, the default query class will be that of sqlalchemy.

EDIT: One additional thing to remember when mixing sqlalchemy session with flask-sqlalchemy models is that the flask-sqlalchemy session object is actually a subclass, with added _model_changes dictionary. As per this answer, you won't be able to commit the session if you don't add the _model_changes field:

def create_session(config):
    engine = create_engine(config['DATABASE_URI'])
    Session = sessionmaker(bind=engine)
    session = Session()
    session._model_changes = {}
    return session

Works like a charm, thank you. I allowed myself to edit your answer with some important info because there was one gotcha on the way. — lawicko, Feb 04 '14 at 13:34
I didn't tested the commit. Thanks for you update. Looks like a Flask-SQLAlchemy bug. — iurisilvio, Feb 04 '14 at 14:02

Using sqlalchemy declarative in the external worker

1 Answers1