New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Document how to use plain SQLAlchemy models with the Flask-SQLAlchemy session #98

Closed
dmckeone opened this Issue Aug 24, 2012 · 25 comments

Comments

Projects
None yet
@dmckeone

dmckeone commented Aug 24, 2012

The application I was writing required that I have models that were portable (not just for use with Flask), but I still wanted to be able to take advantage of the sessions that Flask-SQLAlchemy provided.

It would be nice if it were documented somewhere that, if you are willing to forego some of the convenience methods on the Model (like Model.query), then you can use standard SQLAlchemy models with the Flask-SQLAlchemy session.

Incidentally this approach also helps with avoiding circular imports when passing the SQLAlchemy() instance, db, around to each model.

Here is an example:

# models.py

from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base

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.py

from flask import Flask
from models import Base, User
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', 'bob@gmail.com'))
    db.session.add(User('Joe Quimby', 'eat@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) 
@rzetterberg

This comment has been minimized.

rzetterberg commented Jan 25, 2013

Great example. This helped me a lot! The only thing I had to change to get this working was:

from models import User, Base
@dmckeone

This comment has been minimized.

dmckeone commented Jan 25, 2013

Glad to hear! I've updated the example with your change.

@GaretJax

This comment has been minimized.

GaretJax commented Jul 30, 2013

Wouldn't it be possible to support the Model.query by executing https://github.com/mitsuhiko/flask-sqlalchemy/blob/master/flask_sqlalchemy/__init__.py#L675 for each declarative base during the setup step?

@mitsuhiko

This comment has been minimized.

Owner

mitsuhiko commented Jul 31, 2013

Can you send a pull request for the docs for this?

@dmckeone

This comment has been minimized.

dmckeone commented Aug 2, 2013

Ya definitely, I'll try and get something together in the next little while.

@dmckeone

This comment has been minimized.

dmckeone commented Aug 2, 2013

@GaretJax You probably could do that, but I know that SQLAlchemy also documents a way to add a similar .query() property with ScopedSession.query_property(): http://docs.sqlalchemy.org/en/rel_0_7/orm/session.html#contextual-session-api. Not really sure how this varies from the current solution in Flask-SQLAlchemy, but they seemly conceptually similar.

@GaretJax

This comment has been minimized.

GaretJax commented Aug 2, 2013

I'm all for reusing as much as standard SQL alchemy stuff as possible, but consistency with the current flask-sqlalchemy API is good to have as well.

All in all I'm not convinced about some of the design choices of the current version of flask-sqlalchemy (notably the dependency on a single base), but this is probably a different issue.

I'm trying out some stuff in the next days, I have still to make up my mind about all that stuff, I just began using flask.

On Thu, Aug 1, 2013 at 11:10 PM, David McKeone notifications@github.com
wrote:

@GaretJax You probably could do that, but I know that SQLAlchemy also documents a way to add a similar .query property with ScopedSession.query_property(): http://docs.sqlalchemy.org/en/rel_0_7/orm/session.html#contextual-session-api. Not really sure how this varies from the current solution in Flask-SQLAlchemy, but they seemly conceptually similar.

Reply to this email directly or view it on GitHub:
#98 (comment)

@mitsuhiko

This comment has been minimized.

Owner

mitsuhiko commented Aug 2, 2013

All in all I'm not convinced about some of the design choices of the current version of flask-sqlalchemy (notably the dependency on a single base), but this is probably a different issue.

Agreed. That's also why I'm currently reconsidering how I can fix the library without making the migration process painful for everybody.

@GaretJax

This comment has been minimized.

GaretJax commented Aug 2, 2013

I started experimenting on this, the approach I am taking is available here: wsfcomp/flask-sqlalchemy@mitsuhiko:master...master (no working code, just an idea of the API). @mitsuhiko is there an issue or a more appropriate place to discuss the changes for the next release?

@dmckeone

This comment has been minimized.

dmckeone commented Aug 2, 2013

I'll hang on for the better place to discuss as well, but I will say that in my ideal world Flask-SQLAlchemy should not dictate the base Model class at all. Sure it can provide some helpers if the user doesn't care and just wants the Flask-SQLAlchemy session, signals and db.Model paradigm, but if I already have a Declarative Base, and I just want to wire it in to Flask-SQLAlchemy, then it should be possible to just add .query() and listen for SQLAlchemy events.

I know that we can definitely add .query() to any existing SQLAlchemy model via ScopedSession.query_property(query_cls=), and since you only have to inspect the old db.Model's query_cls attribute to get the needed argument, I suspect it could be made backwards compatible as well. Additionally, signals could be easily backed in to an existing Model via the SQLAlchemy event system (which wasn't available when Flask-SQLAlchemy was first written).

So it would end being something like below if I wanted to get all the default flask stuff:

flask_app.py

app = Flask(__name__)
db = SQLAlchemy(app)

class User(db.Model):
    """ Represents a User """
    query_cls = CachingQuery

@app.route("/")
def root():
    users =  User.query.all() 

and something like below if I wanted to just inject my existing stuff into an app.

models.py

Base = declarative_base(cls=CustomBase)

class User(Base):
      """ Represents a User """

# etc...

flask_app.py

from models import User, Page, SecretFeature

app = Flask(__name__)

# User.query() would throw an AttributeException here

db = SQLAlchemy(app, query_models=[User, Page, SecretFeature])

@app.route("/")
def root():
    users =  User.query.all()   #Work with Flask-SQLAlchemy's session

Now, this is probably half-baked to some degree, but it does prevent some problems that I had with Flask-SQLAlchemy that eventually forced me to not use it any more.

Advantages:

  • Avoiding circular references when importing the db instance (although it does create a different class of monkey-patch-like import issues around when User gets imported in other files)
  • Avoids having to think about special behaviours when creating models -- SQLAlchemy documentation mostly works as-is, without too much extra thought.
  • Works in multiple Flask and Non-Flask apps in a similar way since the query attribute can be bound differently for each app
  • Could allow wrapping the models of any SQLAlchemy project, even if it wasn't written with Flask in mind.
  • Ability to decide if models should be used with .query() or a custom SQLAlchemy session.
  • Simple things easy, hard things possible.

Disadvantages:

  • Probably not as convenient as just using db.Model -- but then if you need the kind of behaviour that I'm talking about, your aren't going to care, because the only alternative is to forego Flask-SQLAlchemy altogether (like I had to). Also, this proposal wouldn't need to remove the db.Model behaviour -- for those that just want easy, or don't want to dive into the SQLAlchemy deep end.
  • Fairly major rewrite of basically all of this package would need to happen. This isn't a big module, so maybe not too bad (and maybe needed for Python 3 anyway), but obviously something any project owner wouldn't do lightly.

Anyway, food for thought. Happy to carry the conversation on somewhere else, and very happy to see that the discussion is happening. I'd love to bring my stuff back to Flask-SQLAlchemy so that I can integrate with other extensions that expect it.

@GaretJax

This comment has been minimized.

GaretJax commented Aug 2, 2013

@dmckeone I agree on almost everything! Maybe instead of registering single models, we can register the declarative base we created (and thus all models at once), as I proposed in the example linked above.

One thing I think is important is to be able to "register" models or bases outside of the constructor and offer an API similar to the one exposed for blueprints (see: http://flask.pocoo.org/docs/blueprints/#registering-blueprints).

P.S.: I would gladly take the time to implement those changes in the next couple of weeks.

@dmckeone

This comment has been minimized.

dmckeone commented Aug 2, 2013

@GaretJax Registering the declarative base could be good as well (certainly cleaner to look at, and may even avoid monkey-patch issues). If you register a .query property on the declarative base would that be used by all the children as well? Does that require using the @declared_attr behaviours? (http://docs.sqlalchemy.org/en/rel_0_8/orm/extensions/declarative.html#mixing-in-deferred-column-property-and-other-mapperproperty-classes)

@GaretJax

This comment has been minimized.

GaretJax commented Aug 2, 2013

Normally it is inherited by the children, but I didn't run the code yet, it's just an example of the API.

I don't know if the models would be usable (but I know for sure that the .metadata attribute changed and with it the different create/drop methods stop working).

@dmckeone

This comment has been minimized.

dmckeone commented Aug 2, 2013

@GaretJax Missed part of your comment. Totally agree about blueprints and registering:

from models import Base

db = SQLAlchemy(app)
db.register_base(Base)

Actually it would be really awesome if the .query attribute used the application context to decide which session was used, so that you could use separate sessions within Blueprints if you wanted to, while using the same model classes.

@lepture

This comment has been minimized.

lepture commented Aug 2, 2013

@dmckeone I like the idea of register_base.

@mitsuhiko

This comment has been minimized.

Owner

mitsuhiko commented Aug 2, 2013

I think I want to see if i can find the time tomorrow to write up all the problems that people have mentioned with the extension and then figure out a plan how to solve them. In any case I believe by now it makes sense to start from scratch and try to find a migration path. The API exposed is very minimal and should be easy to match for the most part.

There is definitely too much magic in the extension currently.

@jmagnusson

This comment has been minimized.

jmagnusson commented Oct 2, 2013

Any news on this? I'm planning on using Flask + Flask-SQLAlchemy for a new project, but reading this makes me kind of vary on using this extension.

@mvalkon

This comment has been minimized.

mvalkon commented Feb 10, 2014

@mitsuhiko any news as to whether or not the refactoring process is going to happen? Is there some way to contribute?

@gawry

This comment has been minimized.

gawry commented May 1, 2014

I couldn't use the extension yet. Are there plans on this?
I had to implement my own pagination function. :-(

@tony

This comment has been minimized.

tony commented Jul 15, 2014

@mitsuhiko, et al: Do you think something like register_base(Base) is more pythonic than the current form? Would it make db.table, db.relationship, db.Integer, etc. obsolete?

@mozillazg

This comment has been minimized.

mozillazg commented Dec 1, 2014

Is there any news about this issue?

@nickw444

This comment has been minimized.

Contributor

nickw444 commented Jan 2, 2015

@mozillazg I came across this today and dirty patched it for my own usage. Not heavily tested, but would really like this to be eventually merged back into Flask-SQLAlchemy since it's useful if you're using the models in non flask-apps at the same time (or wish to modularise them).

My patch simply adds the method db.register_base(Base).

For Example

from models import Base
app = Flask(__name__)
db = SQLAlchemy(app)
db.register_base(Base)

This appears to correctly patch and create_all() seems to work correctly. It also patches the convenience Class.query() method onto your SQLA classes too.

https://github.com/nickw444/flask-sqlalchemy

@immunda

This comment has been minimized.

Collaborator

immunda commented Jun 9, 2015

Closing, inroads being made in #250

@immunda immunda closed this Jun 9, 2015

@ghost

This comment has been minimized.

ghost commented Dec 1, 2015

Thanks!

@AlecBenzer

This comment has been minimized.

AlecBenzer commented Jan 29, 2018

#250 seems stalled -- would it be worth just documenting that something like @dmckeone's original snippet is possible? (FWIW, I initially took a look at SQLAlchemy-Flask and moved on because I wasn't aware this was possible)

tiangolo added a commit to tiangolo/flask-sqlalchemy that referenced this issue Jun 28, 2018

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment