Hi,
Because I still love relational databases very much and SQLAlchemy is a
an awesome library, I consider adding a minimal briding flaskext that
bootstraps SQLAlchemy for you.
The idea is that a minimal Flask application would look roughly like this:
from flask import Flask
from flaskext.sqlalchemy import init_sqlalchemy
app = Flask(__name__)
app.config.from_pyfile('application.cfg')
sa = init_sqlalchemy(app)
This obviously already assumes that config support is in place. The
extension would then look for the sqlalchemy_database_uri configuration
key to initialize the engine.
Creating the database would be a matter of two lines:
>>> from yourapplication import sa
>>> sa.init_db()
To declare models one could do this:
from yourapplication import sa
class User(sa.Model):
username = sa.Column(sa.String(200))
email = sa.Column(sa.String(200))
def __init__(self, username, email):
self.username = username
self.email = email
Here how you would query and insert records:
>>> from yourapplication import sa, User
>>> me = User('john_doe', 'john@example.com')
>>> sa.session.add(me)
>>> sa.session.commit()
>>> users = User.query.all()
>>> len(users)
1
That "sa" object would be an object that gives you the following things:
1. all public members of the "sqlalchemy" package
2. all public members of the "sqlalchemy.orm" package
3. a "Model" baseclass that is a declarative_base + query attached
4. a "session" object that is a scoped session ready to use
5. a "init_db" function that initializes the database
6. a "drop_db" function that drops the database again
Obviously because that object (which probably would be a dynamically
created module) is bound to an application that would also set up
before_request and after_request handlers for the application automatically.
What do you think of that? Any suggestions or improvements? If not I
would consider this as the prime example of a flask extension with
configuration support in 0.5.
Regards,
Armin
I've created an experimental SQLAlchemy extension, flask-alchemy:
http://bitbucket.org/danjac/flask-alchemy
It uses an intermediate adapter class to handle session and engine
configuration. It works like this:
from flask import Flask
from flaskext.alchemy import init_alchemy
from myapp.models import User
app = Flask(__name__)
init_alchemy(app)
assert app.sql_adapter
@app.route("/")
def index():
users = User.query.all()
....
Then in your model code:
from sqlalchemy import Column, String
from flaskext.alchemy import db_session, Base
class User(Base):
__tablename__ = "users"
username = Column(String(30), unique=True, primary_key=True)
The db_session is a scoped_session, the adapter object providing a
create_session() method to configure the session as needed.
An after_request function is also added to clear the session at the
end of the request.
The DefaultAdapter is for simple cases (i.e. one db/engine), however
it is trivial to provide a subclass for more complex cases such
as multiple dbs/engines. It uses Flask configuration to set up, e.g.
DATABASE_URI.
This is really just experimental to get some ideas going, not
production code, so use at own risk.
On 6/1/10 1:58 PM, Dan Jacob wrote:
> I've created an experimental SQLAlchemy extension, flask-alchemy:
Hah. So did I :D Mine looks a bit different though, will publish the
code somewhere when I'm home.
Regards,
Armin
Hi, On 6/1/10 2:28 PM, Armin Ronacher wrote: > On 6/1/10 1:58 PM, Dan Jacob wrote: >> I've created an experimental SQLAlchemy extension, flask-alchemy: > Hah. So did I :D Mine looks a bit different though, will publish the > code somewhere when I'm home. Just uploaded my code to my github account: Project Home: http://github.com/mitsuhiko/flask-sqlalchemy Example usage: http://bit.ly/c38FIV Regards, Armin
On 2010-06-01 5:05 PM, Armin Ronacher wrote: > Project Home: http://github.com/mitsuhiko/flask-sqlalchemy > Example usage: http://bit.ly/c38FIV Registered on PyPI now and uploaded a dummy 0.5 release with docs. Don't use in production yet. Extension is also not yet registered in the Flask extension Registry. Docs: http://packages.python.org/Flask-SQLAlchemy/ Example: http://bit.ly/c38FIV Regards, Armin
Just thinking of a small addition: first_or_404(). This is like get_or_404 but works with a complete query: user = User.query.filter(User.email.like(email).first_or_404() On 2 June 2010 01:24, Armin Ronacher <armin.ronacher@active-4.com> wrote: > On 2010-06-01 5:05 PM, Armin Ronacher wrote: >> Project Home: http://github.com/mitsuhiko/flask-sqlalchemy >> Example usage: http://bit.ly/c38FIV > Registered on PyPI now and uploaded a dummy 0.5 release with docs. > Don't use in production yet. Extension is also not yet registered in > the Flask extension Registry. > > Docs: http://packages.python.org/Flask-SQLAlchemy/ > Example: http://bit.ly/c38FIV > > > Regards, > Armin >
Hi, Maybe we should discuss that on IRC. Less latency :) Regards, Armin
Re: IRC - might be a good idea.
Anyway just had a quick look at your code:
1. I like the idea of integrating SQLAlchemy and the debugger.
2. Also like the idea of get_object_or_404: I was also thinking a
paginate() method would be very handy, returning some sort of
Pagination object (as pagination is such a common requirement).
3. Why add SQLAlchemy functions/classes to the flaskext namespace
(Column, String etc) ? It's a bit of "magic" that might not make the
code any easier to maintain.
4. I'm not sure how you would manage imports of your model code. For
example I have my models in myapp/models.py and my app setup code in
myapp/__init__.py:
__init__.py:
def create_app(config):
app = Flask(__name__)
app.config.from_pyfile(config)
sa = SQLAlchemy(app)
return app
How do I then define my classes in models.py to use Base, session etc
if they are tied to the "sa" object ? Same goes for modules/views
dependent on this object.
On 1 June 2010 16:06, Armin Ronacher <armin.ronacher@active-4.com> wrote:
> Hi,
>
> Maybe we should discuss that on IRC. Less latency :)
>
>
> Regards,
> Armin
>
Hi, On 6/1/10 5:20 PM, Dan Jacob wrote: > 3. Why add SQLAlchemy functions/classes to the flaskext namespace > (Column, String etc) ? It's a bit of "magic" that might not make the > code any easier to maintain. Because it saves a lot of keystrokes and it worked reliable for me in the past. It also solves the problem that you suddenly have three places where to look for objects (the module that contains engine and session, the orm module and the regular sqlalchemy module) > How do I then define my classes in models.py to use Base, session etc > if they are tied to the "sa" object ? Same goes for modules/views > dependent on this object. That is an unsolved problem with SQLAlchemy in general. That problem is not solved if you declare these things toplevel. Why? Because models can only be used by one application at onces. It will already break with create_app. The only solution I can offer is creating all the models in a factory function and attaching them to app.models or db.models, depending on what you prefer. Regards, Armin
On 1 June 2010 16:36, Armin Ronacher <armin.ronacher@active-4.com> wrote: > Hi, > > On 6/1/10 5:20 PM, Dan Jacob wrote: >> 3. Why add SQLAlchemy functions/classes to the flaskext namespace >> (Column, String etc) ? It's a bit of "magic" that might not make the >> code any easier to maintain. > Because it saves a lot of keystrokes and it worked reliable for me in > the past. It also solves the problem that you suddenly have three > places where to look for objects (the module that contains engine and > session, the orm module and the regular sqlalchemy module) > Fair enough. >> How do I then define my classes in models.py to use Base, session etc >> if they are tied to the "sa" object ? Same goes for modules/views >> dependent on this object. > That is an unsolved problem with SQLAlchemy in general. That problem is > not solved if you declare these things toplevel. Why? Because models > can only be used by one application at onces. It will already break > with create_app. The only solution I can offer is creating all the > models in a factory function and attaching them to app.models or > db.models, depending on what you prefer. > There's probably no "right" answer here, because of SQLAlchemy's session design. I suppose you could use a factory function that can be passed into your create_app. However it's not very optimal to declare all your models, business code etc inside functions. Using a global db_session isn't very optimal either - it still ties your session to the application - but you can import your models normally. What about some kind of "lazy" variation of Base that can be tied to a session at initialization ? It might work a bit like this: models.py: from flaskext.sqlalchemy import db # general namespace class UserQuery(db.BaseQuery): ... custom code... class User(db.Model): __tablename__ = "users" id = db.Column(db.Integer, ...) query = UserQuery() The "query" is configured with the session when you do your setup: def create_app(config): .... sa = SQLAlchemy(app) # now you can use your models This might be doable with a metaclass you can pass into declarative_base(), which attaches the query at runtime. I'd also suggest making the session object a thread proxy, so you don't have to do "current_app.sa" everywhere (or alternatively adding a "save()" method to the Base class). > > Regards, > Armin >
Le 01/06/2010 17:53, Dan Jacob a écrit : >>> How do I then define my classes in models.py to use Base, session etc >>> if they are tied to the "sa" object ? Same goes for modules/views >>> dependent on this object. >>> >> That is an unsolved problem with SQLAlchemy in general. That problem is >> not solved if you declare these things toplevel. Why? Because models >> can only be used by one application at onces. It will already break >> with create_app. The only solution I can offer is creating all the >> models in a factory function and attaching them to app.models or >> db.models, depending on what you prefer. >> >> > There's probably no "right" answer here, because of SQLAlchemy's session design. > > I suppose you could use a factory function that can be passed into > your create_app. However it's not very optimal to declare all your > models, business code etc inside functions. > > Using a global db_session isn't very optimal either - it still ties > your session to the application - but you can import your models > normally. > "Models can only be used by one application at once" because they have a query property that is somehow tied to a SA session. If we remove it and use session.query(User) (as in SA’s doc) instead of User.query, the models can then be used with different SA sessions and thus with different applications.
On 2010-06-01 10:09 PM, Simon Sapin wrote: > "Models can only be used by one application at once" because they > have a query property that is somehow tied to a SA session. > > If we remove it and use session.query(User) (as in SA’s doc) instead > of User.query, the models can then be used with different SA sessions > and thus with different applications. I like User.query a lot and found a way to make it work. That is based on a custom descriptor now. Regards, Armin
It would be handy to access the engine and metadata as well i.e. "sa.engine" or "sa.metadata" for when you need it, e.g. for low-level stuff. On 18 May 2010 23:59, Armin Ronacher <armin.ronacher@active-4.com> wrote: > Hi, > > Because I still love relational databases very much and SQLAlchemy is a > an awesome library, I consider adding a minimal briding flaskext that > bootstraps SQLAlchemy for you. > > The idea is that a minimal Flask application would look roughly like this: > > from flask import Flask > from flaskext.sqlalchemy import init_sqlalchemy > app = Flask(__name__) > app.config.from_pyfile('application.cfg') > sa = init_sqlalchemy(app) > > This obviously already assumes that config support is in place. The > extension would then look for the sqlalchemy_database_uri configuration > key to initialize the engine. > > Creating the database would be a matter of two lines: > > >>> from yourapplication import sa > >>> sa.init_db() > > To declare models one could do this: > > from yourapplication import sa > > class User(sa.Model): > username = sa.Column(sa.String(200)) > email = sa.Column(sa.String(200)) > > def __init__(self, username, email): > self.username = username > self.email = email > > Here how you would query and insert records: > > >>> from yourapplication import sa, User > >>> me = User('john_doe', 'john@example.com') > >>> sa.session.add(me) > >>> sa.session.commit() > >>> users = User.query.all() > >>> len(users) > 1 > > That "sa" object would be an object that gives you the following things: > > 1. all public members of the "sqlalchemy" package > 2. all public members of the "sqlalchemy.orm" package > 3. a "Model" baseclass that is a declarative_base + query attached > 4. a "session" object that is a scoped session ready to use > 5. a "init_db" function that initializes the database > 6. a "drop_db" function that drops the database again > > Obviously because that object (which probably would be a dynamically > created module) is bound to an application that would also set up > before_request and after_request handlers for the application automatically. > > What do you think of that? Any suggestions or improvements? If not I > would consider this as the prime example of a flask extension with > configuration support in 0.5. > > > Regards, > Armin >
Hi, On 2010-05-19 1:06 AM, Dan Jacob wrote: > It would be handy to access the engine and metadata as well i.e. > "sa.engine" or "sa.metadata" for when you need it, e.g. for low-level > stuff. The metadata is attached to the Model anyways. About the engine it would probably be created when needed so a `get_engine()` found could be provided. Regards, Armin
The only other issue would be where to import your models - given that
you might want to initialize your app in __init__.py (this is similar
to the config import problem discussed earlier).
I'd imagine something like this:
app = Flask(__name__)
db = init_sqlalchemy(app, "db")
db.register_models("myapp.models")
db.init_db()
This would ensure that the models in myapp.models would be imported
and added to the SQLAlchemy metadata.
The problem then is if you need to access a model in __init__.py,
without importing the model directly. Given the above code you might
be able to do this:
@before_request
def authenticate():
if 'user_id' in session:
g.user = db.models.User.query(....)
The "db" argument would in init_sqlalchemy would be the name appended
to the g object, so you can then access the models elsewhere in your
code:
@something.route("/")
def index():
users = g.db.models.User.query(...)
This might be needed to avoid circular imports.
On 19 May 2010 00:31, Armin Ronacher <armin.ronacher@active-4.com> wrote:
> Hi,
>
> On 2010-05-19 1:06 AM, Dan Jacob wrote:
>> It would be handy to access the engine and metadata as well i.e.
>> "sa.engine" or "sa.metadata" for when you need it, e.g. for low-level
>> stuff.
> The metadata is attached to the Model anyways. About the engine it
> would probably be created when needed so a `get_engine()` found could be
> provided.
>
>
> Regards,
> Armin
>