SQLAlchemy with Flask -- hybrid models?

[David McKeone]

I've been using SQLAlchemy with Flask via the Flask extension Flask-SQLAlchemy. Everything works great so far, but I foresee a potential problem once I start to use my database model outside of Flask.  In the future I'd like to be able to use my models with non-Flask SQLAlchemy (a worker daemon process or with a PySide interface).  "Well just use standard SQLAlchemy," you may say, " and fore-go the use of the extension".  That was my first thought, but sadly some useful extensions (notably Flask-DebugToolbar) seem to like using the extension version and it is nice to be able to have Flask manage the database sessions in the way that it does.  I'd like to not throw the baby out with the bath water.    

I realize that this is somewhat specific to Flask, but is there a way that I could do both?  Can I create models with standard SQLAlchemy declarative and then somehow inject them into Flask-SQLAlchemy's way of doing things?

If it helps with the solution, I don't need to use any of the Model specific methods that Flask-SQLAlchemy provides (get_or_404, paginate, etc..) and I also specify a __tablename__ for all of my models, so I don't rely on Flask-SQLAlchemy generating that for me.

I took a look at the source of Flask-SQLAlchemy (https://github.com/mitsuhiko/flask-sqlalchemy/blob/master/flask_sqlalchemy.py)  and from what I can tell it seems that it's using Flask's signalling capabilities by customizing SQLAlchemy's session and mapper, but that is where my understanding ends (I'm still new to this whole stack, Python, Flask, SQLAlchemy) and I could use some pointers for how to proceed.

To visualize what I'm talking about, here are the two types of models.  A basic Flask-SQLAlchemy model looks like (http://packages.python.org/Flask-SQLAlchemy/quickstart.html#a-minimal-application):

from flask import Flask 

from flask.ext.sqlalchemy import SQLAlchemy 

app = Flask(__name__) 

app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:////tmp/test.db' 

db = SQLAlchemy(app) 

class User(db.Model): 

    id = db.Column(db.Integer, primary_key=True) 

    username = db.Column(db.String(80), unique=True) 

    email = db.Column(db.String(120), unique=True) 

    def __init__(self, username, email): 

         self.username = username

                   self.email = email 

    def __repr__(self): 

        return '<User %r>' % self.username

Note the db.Model, db.Integer and db <dot> everything.

The plain declarative SQLAlchemy equivalent would be (http://flask.pocoo.org/docs/patterns/sqlalchemy/):

from sqlalchemy import Column Integer, String, create_engine 

from sqlalchemy.orm import scoped_session, sessionmaker 

from sqlalchemy.ext.declarative import declarative_base 

engine = create_engine('sqlite:////tmp/test.db', convert_unicode=True) 

db_session = scoped_session(sessionmaker(autocommit=False, autoflush=False, bind=engine)) 

Base = declarative_base()

Base.query = db_session.query_property()

class User(Base): 

     __tablename__ = 'users' 

    

     id = Column(Integer, primary_key=True) 

     name = Column(String(50), unique=True) 

     email = Column(String(120), unique=True) 

     def __init__(self, name=None, email=None): 

         self.name = name 

         self.email = email 

     def __repr__(self): 

         return '<User %r>' % (self.name)

Thanks for your help!

[Simon King]

From a very quick read of the Flask-SQLAlchemy docs, I would have
thought you could just use your flask-based classes in non-Flask-based
apps without any issue. The quickstart guide that you referenced above
illustrates a command-line session using them, so there's no reason
why you couldn't do the same in a worker process.

Have you already tried it and found that it doesn't work?

Simon

[David McKeone]

Perhaps I've missed the important bit, but my understanding is that there are two ways to do it:

1) Use SQLAlchemy with manual session control (http://flask.pocoo.org/docs/patterns/sqlalchemy/)

2) Use SQLAlchemy with the Flask-SQLAlchemy extension which does session control for you, but requires you to use it's own declarative base class, db.Model  (http://packages.python.org/Flask-SQLAlchemy/quickstart.html#a-minimal-application)

The problem is that other extensions give you additional features if you use solution #2, but not if you use solution #1, because solution #1 implies that you are going to do everything yourself manually.  I'm fine with the way solution #2 handles the sessions and everything else, I just want to be able to use my own declarative base so that the models are more-or-less independent of the app that they are being used in.

[David McKeone]

I did a re-read of the documentation and I'm guessing this is the part of the documentation you are referring to: http://packages.python.org/Flask-SQLAlchemy/contexts.html

That basically says to use: app.test_request_context() before doing what you do, so that it can establish a new context.  I suppose that does make sense, I guess i just viewed it as being only for testing and not for application use.  I would have thought it might be strange to include Flask in a worker daemon when it isn't actually servicing http requests.

[Simon King]

Disclaimer: I've never used Flask so everything I say here could be
rubbish. Your question really seems to be 'How can I make
Flask-SQLAlchemy work with "standard" SQLAlchemy classes?', which is
probably better asked in a Flask-SQLAlchemy forum.

It looks like the test_request_context method is only needed if you
don't have a Flask app object available when you define your model
classes.

An (admittedly ugly) alternative would be to do something like this:

#------------------------

from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy

class Bunch(object):
def __init__(self, **kwargs):
self.__dict__.update(kwargs)

def get_model(app=None):
if app is None:
app = Flask(__name__)

db = SQLAlchemy(app)

class User(db.Model):
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(80), unique=True)
email = db.Column(db.String(120), unique=True)

def __init__(self, username, email):
self.username = username
self.email = email

def __repr__(self):
return '<User %r>' % self.username

return Bunch(**locals())

if __name__ == '__main__':
model = get_model()
model.app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:////tmp/test.db'
model.db.create_all()
user = model.User('Joe', 'j...@example.com')
model.db.session.add(user)
model.db.session.commit()
print model.db.session.query(model.User).all()

#------------------------

Non-flask apps could just call the get_model() function and be
completely unaware that flask is in use under the hood. Flask apps
would pass their "app" instance into the get_model function.

This might only be suitable for non-flask apps that use a single
thread and session, but perhaps that is sufficient.

Hope that helps,

Simon

[David McKeone]

Yes, that solution seems to be the only way to do it; use the Flask-SQLAlchemy models and include a 'fake' flask application context when doing something outside of flask.  I had thought about asking on the Flask forum, but then it seemed like it might be too specific to the way that SQLAlchemy worked with sessions and mapping.  I don't think there is an active Flask-SQLAlchemy forum?  Maybe just the github issues tracker? 

In any case, thanks for your help Simon.  You've helped me to at least talk it out.

[Michael Bayer]

On Aug 22, 2012, at 7:51 AM, David McKeone wrote:

I've been using SQLAlchemy with Flask via the Flask extension Flask-SQLAlchemy. Everything works great so far, but I foresee a potential problem once I start to use my database model outside of Flask.  In the future I'd like to be able to use my models with non-Flask SQLAlchemy (a worker daemon process or with a PySide interface).  "Well just use standard SQLAlchemy," you may say, " and fore-go the use of the extension".  That was my first thought, but sadly some useful extensions (notably Flask-DebugToolbar) seem to like using the extension version and it is nice to be able to have Flask manage the database sessions in the way that it does.  I'd like to not throw the baby out with the bath water.    

I realize that this is somewhat specific to Flask, but is there a way that I could do both?  Can I create models with standard SQLAlchemy declarative and then somehow inject them into Flask-SQLAlchemy's way of doing things?

If it helps with the solution, I don't need to use any of the Model specific methods that Flask-SQLAlchemy provides (get_or_404, paginate, etc..) and I also specify a __tablename__ for all of my models, so I don't rely on Flask-SQLAlchemy generating that for me.

I took a look at the source of Flask-SQLAlchemy (https://github.com/mitsuhiko/flask-sqlalchemy/blob/master/flask_sqlalchemy.py)  and from what I can tell it seems that it's using Flask's signalling capabilities by customizing SQLAlchemy's session and mapper, but that is where my understanding ends (I'm still new to this whole stack, Python, Flask, SQLAlchemy) and I could use some pointers for how to proceed.

There's no reason I can see in the source that flask-sqlalchemy would get in the way of entirely plain SQLAlchemy mapped objects.   At the end of the day, a class that extends Flask's db.model is just a mapped class, just like a non-"flask" class.  Both kinds of classes are freely usable with any SQLAlchemy Session, including the Session that Flask-SQLA provides.    It's important to note the distinction between mapper configuration, which has to do with class structure, and session configuration, which only deals with instances of objects.  These two processes work together at a core level that various extensions only ride on top of, unless those extensions define additional dependencies above that level.   Flask-sqlalchemy appears only to define one very trivial such dependency which is some coordination to enable the before_models_committed and models_committed hooks, which themselves are just for end-user convenience (http://packages.python.org/Flask-SQLAlchemy/signals.html).   

The Flask-SQLA approach is really just assigning event listeners to sessions and mappers.   It's doing so in a way that is a bit brittle, but also this system precedes SQLAlchemy's 0.7 event model.   Armin's immediate goal with flask-sqlalchemy is to migrate the extension to use the new event model, which would actually remove the need for the styles of registration I see here as the new system allows registration of event listeners on all sessions/mappers non-intrusively.

There's also a custom Query class in use here, though it doesn't seem to be consistently integrated with the Session, but using custom Query classes like this as well as adding the "MyClass.query" hook is a widely used pattern.

So if you were to use plain SQLAlchemy models with flask-SQLA out of the box, these particular events wouldn't fire off as much, unless you also set up the flask_sqlalchemy._SignalTrackingMapperExtension with your normal mappers.   

I think if you just tried using regular models with flask models, and didn't rely on those two particular signals, you'd see everything pretty much works without any issue.

[David McKeone]

Thanks for your great response Mike.  

Forgive my ignorance, but I don't understand enough of the underpinnings to get my first steps out of this (the downside of starting with something that gives you stuff for free, I suppose).  I'm definitely going to walk through what you've said and reference it against the documentation, but while your mind is fresh on the topic, I was wondering if you could just clarify how I might convert a standard model object into a flask-sqlalchemy model object.

Using the two examples above, would I just take my User(Base) class and then assign it property out of the Flask db properties?  Something like: 

User.session = db.session()  

User.engine = db.engine()

I know those properties don't actually exist on User, but is that the kind of thing I should be looking to do?  Move certain pieces into all of the models?  or is there something higher level going on that will do this for me in some way? something else?

... but also this system precedes SQLAlchemy's 0.7 event model.   Armin's immediate goal with flask-sqlalchemy is to migrate the extension to use the new event model, which would actually remove the need for the styles of registration I see here as the new system allows registration of event listeners on all sessions/mappers non-intrusively.

 This seems like it would be a really good way to accomplish what I'm looking for and to move things forward as well.  Once I read up on the requisite knowledge I may end up making an attempt at making this over the next little while.  I had a drink with Armin last week and I'm not sure if his current stuff points him in this direction (of course you'd have to ask him for the real answer on that), but I certainly have a vested interest, so maybe I can do some of the grunt work.

[Michael Bayer]

On Aug 22, 2012, at 3:51 PM, David McKeone wrote:

 I was wondering if you could just clarify how I might convert a standard model object into a flask-sqlalchemy model object.

why do you need to do this ?    What flask-sqlalchemy-specific features would you hope for the model objects to have ?   There's pretty much two I can see - one is the MyModel.query attribute, and the other are the events.  Neither are necessary, though you might want the usage of one or the other.   Otherwise no "conversion" is needed.

Using the two examples above, would I just take my User(Base) class and then assign it property out of the Flask db properties?  Something like: 

User.session = db.session()  

User.engine = db.engine()

I know those properties don't actually exist on User, but is that the kind of thing I should be looking to do?  Move certain pieces into all of the models?  or is there something higher level going on that will do this for me in some way? something else?

I don't see why engine or session would be stuck onto the model class like that,  I don't see that usage here: http://packages.python.org/Flask-SQLAlchemy/quickstart.html#a-minimal-application.  Just the "query" attribute.  "User.query.all()".  This is equivalent to "session.query(User).all()".

The "User.session / User.engine" pattern suggests that a model class acts as a registry for how to get at a live database connection.  That pattern is one that SQLAlchemy specifically discourages.   In the Flask docs, you'll see that the "handle" to the state of a transaction is db.session.  The objects like instances of User are then contained within that.

 This seems like it would be a really good way to accomplish what I'm looking for and to move things forward as well.  Once I read up on the requisite knowledge I may end up making an attempt at making this over the next little while.  I had a drink with Armin last week and I'm not sure if his current stuff points him in this direction (of course you'd have to ask him for the real answer on that), but I certainly have a vested interest, so maybe I can do some of the grunt work.

its really quite a small amount of effort.   I think for now the strategy on your end should be to forge ahead with what's there, and if there's some very specific thing that doesn't work as you expect, I can show you how to get the behavior you're looking for.

[David McKeone]

I suppose I should be more clear.  This is really a long term question, I was just looking for some kind of answer now because I don't want to code myself into a corner in the short term.  Currently I can make requests outside of a flask request context by using the app.test_request_context() context manager, and it seems to do the right thing.   

In the long term I'm looking for 2 (maybe 3) things that I already get from Flask-SQLAlchemy:

1) Session teardown for every request (looks like that is done with a call to session.remove() in the request teardown)

2) Debug query tracking for use with Flask-DebugToolbar  (Plus compatibility with other plug-ins that may expect Flask-SQLAlchemy)

- This is really the difficult one.  In this case I think it just needs an attribute on the Flask app called sqlalchemy_queries which contains a tuple of queries.  This is where I was thinking it may be better to assist with updating the plugin to just play nice with SQLAlchemy's default behaviours.  Maybe I'm wrong?

3) The Model.query behaviour (it's nice, but I could live without it, since its really just syntactic)

I think it'll make my code simpler/better for two reasons:

1) My models will be SQLAlchemy, not a Flask specific dialect with db <dot> everywhere.  This will make the SQLAlchemy documentation more obvious to programmers that use the code.

2) The db.Model method seems to make it very easy to create circular import errors (as seen in this issue: https://github.com/mitsuhiko/flask-sqlalchemy/issues/97 and experienced by myself).  It would be really nice if I could isolate the models and model mapping behaviours from the rest of the application to avoid that kind of thing.

The "User.session / User.engine" pattern suggests that a model class acts as a registry for how to get at a live database connection.  That pattern is one that SQLAlchemy specifically discourages.   In the Flask docs, you'll see that the "handle" to the state of a transaction is db.session.  The objects like instances of User are then contained within that.

This was really just my lack of understanding of how the database connection is connected to the models, rather than stating how I wanted it to be done.  Thinking about it now, and reading your comment, I realize that sessions are connected to the models when they are passed in, like in session.query(User).first(), and Model.query is really just a class instance that holds onto the session.  Duh, sorry.  I really need to read through the docs so I can be more comprehensible.  I'm attempting to read as I go so that my questions are better, but it's not quite working out yet.  Thank you for your help (and understanding) though.

 

[David McKeone]

Didn't say this explicitly; for now I will do what you say and forge ahead with things.  I think I see the path, but I'll make sure to let you (the list) know if I run into trouble.

Thanks again.

 

[Michael Bayer]

On Aug 22, 2012, at 5:33 PM, David McKeone wrote:

> I suppose I should be more clear. This is really a long term question, I was just looking for some kind of answer now because I don't want to code myself into a corner in the short term. Currently I can make requests outside of a flask request context by using the app.test_request_context() context manager, and it seems to do the right thing.
>
> In the long term I'm looking for 2 (maybe 3) things that I already get from Flask-SQLAlchemy:
> 1) Session teardown for every request (looks like that is done with a call to session.remove() in the request teardown)

you can use the Session provided by flask-sqlalchemy, which has the nice quality that it aligns itself with the current request.

He can make that feature more open-ended though. I should be able to say flask_sqlalchemy.request_scope(some_sessionmaker) to set that up with any sessionmaker of my choosing.

> 2) Debug query tracking for use with Flask-DebugToolbar (Plus compatibility with other plug-ins that may expect Flask-SQLAlchemy)

the logic i see in flask-sqlalchemy related to debug tracking has no connection to the "db.Model" class at all. its just some connection events which are ultimately established via the "SQLAlchemy" class. Your existing non-flask SQLA models will participate in the Session/Engine used by Flask without impacting this functionaltiy.

> 3) The Model.query behaviour (it's nice, but I could live without it, since its really just syntactic)

scoped_session has a query_property available, so you can pull that from Flask's scoped_session using SQLAlchemy public APIs like this:

sa = SQLAlchemy(db)

# API: http://docs.sqlalchemy.org/en/rel_0_7/orm/session.html#sqlalchemy.orm.scoping.ScopedSession.query_property
Base.query = sa.session.query_property


or to get exactly flask's, which appears to add three methods get_or_404, first_or_404, paginate:

Base.query = flask_sqlalchemy._QueryProperty(sa)

>
>
> Didn't say this explicitly; for now I will do what you say and forge ahead with things. I think I see the path, but I'll make sure to let you (the list) know if I run into trouble.

good luck !

[David McKeone]

Slugged it out today and got this working, hooray!  Thanks again for your help Mike (and for the time you probably put in to parse the Flask-SQLAlchemy code).  If you are at PyCon this year I WILL find you and I WILL buy you beer, unless you don't drink, in which case I WILL buy you soda or coffee.

I haven't done the Base.query part, and I may never do it (more below), but everything else works great and all my tests pass after switching to the new method.

The more I use the new system the more I wish I would have started with it.  Perhaps I can get it documented as an option, because I find it makes it far more clear where the models belong in the grand scheme of things.  Now, not everyone has 93 tables, a boat-load of relationships and requirements for doing things outside of HTTP like I do, so I can understand why it's been done the way that it's been done, but having to pass the db instance into all of my model definitions (and the resulting project structure issues I had) just wasn't worth it.  I've also found that having to use the session directly makes it far more clear which session is being used, and how.  Not to mention the benefits from being able to decouple my models from Flask completely.

So, in the name of Google search completeness,  here is the solution that I ultimately ended up with, adapted for the simple User model from above, for those like me who want/need it.  (It's quite simple, and I'm amazed that it hadn't occurred to me to try it like this)

from flask import Flask

from flask_sqlalchemy import SQLAlchemy

from sqlalchemy import Column, Integer, String

from sqlalchemy.ext.declarative import declarative_base

app =  Flask(__name__)

app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:////tmp/test.db'

db = SQLAlchemy(app)

Base = declarative_base()

class User(Base):

    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)

    name = Column(String(50), unique=True)

    email = Column(String(120), unique=True)

    def __init__(self, name=None, email=None):

        self.name = name

        self.email = email

    def __repr__(self):

        return '<User %r>' % (self.name)

@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', 'b...@gmail.com'))

    db.session.add(User('Joe Quimby', 'e...@joes.com'))

    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)