SQLAlchemy-Future

SQLAlchemy-Future is a SQLAlchemy extension that introduces future/promise into query.

What it improves

Assume we are building a web application that depends on SQLAlchemy. Typical web applications are structured by models, controllers and templates (views). Most of operations that depend on models are finished in controllers.

def list_posts():
    session = Session()
    posts = session.query(Post).order_by(Post.created_at.desc()).all()
    return render_template('list_posts.html', posts=posts)

Codes like this pattern cause a blank screen when the browser are loading it. Then, the result list are rendered suddenly at once.

_images/eager-loading.gif

To avoid the blank screen, we can make queries lazy:

def list_posts():
    session = Session()
    posts = session.query(Post).order_by(Post.created_at.desc())
    return render_template('list_posts.html', posts=posts)

See the difference between both second lines of the former example code and the current. The first example calls the all() method of the query object, but the second does not. SQLAlchemy’s query objects don’t execute itself and fetch its result until it has realized (for example, looped under for statement, or applied to list() or tuple()), and the only purpose of the all() method is just to realize the query. In short, query objects are implicitly executed when they are really needed.

So, execution and fetching the result set of the posts query happen while the template is being rendered. As a result, the browser renders whole result list slightly slowly but gradually. This lazy approach helps hasty users to feel that they are waiting less, but total elapsed time still doesn’t become shorter.

_images/lazy-loading.gif

What SQLAlchemy-Future does is helping programmers to parallelize queries easily. What you have to do is just to place promise() methods where all() methods may come:

def list_posts():
    session = Session()
    posts = session.query(Post).order_by(Post.created_at.desc()).promise()
    return render_template('list_posts.html', posts=posts)

When the promise() method is called, it creates an another thread for the future. And the execution of the query has started running in the created underneath thread. The posts object, the return value which is an instance of Future, is similar to the lazy result set object explained above. It is not a real result set physically, but the promised result set. If iteration over posts, promised result set, has tried, it fetches immediately the real result set of the execution which is run from the underneath thread, or if the result set isn’t prepared, it waits for the underneath thread to finish its query execution (that may be some prepared already).

As result, similarly to the lazy approach of the above, the browser renders result list gradually, but unlike to the lazy approach, relatively fast.

_images/promised-loading.gif

How to setup

In order to make future.Query the default query class, use the query_cls parameter of the sessionmaker() function:

import future
from sqlalchemy.orm.session import sessionmaker
Session = sessionmaker(query_cls=future.Query)

Or you can make future.Query the query class of a session instance optionally:

session = Session(query_cls=future.Query)

How to promise

How to promise a future query is not hard. Just call the promise() method:

posts = session.query(Post).promise()

Its return type is Future (note that it is not a subtype of Query, so you cannot use rich facilities of Query like filter()):

assert isinstance(posts, future.Future)

Then, iterate this future query (posts in the example) when you really need it:

for post in posts:
    print post.title, "by", post.author

If the posts finished the execution in the another thread, it fetches the result set immediately. If its execution hasn’t finished, the another thread joins the main thread and it has blocked until its execution has finished.

future — References

class future.Query(entities, session=None)

Bases: sqlalchemy.orm.query.Query

The subtype of sqlalchemy.orm.query.Query class, that provides the promise() method.

You can make this the default query class of your session:

from sqlalchemy.orm import sessionmaker
import future
Session = sessionmaker(query_cls=future.Query)
promise()

Makes a promise and returns a Future.

Returns:the promised future
Return type:Future
class future.Future(query)

Bases: object

Promoised future query result.

Parameters:

Note

It is not a subtype of Query, so it does not provide any method of Query like filter().

all()

Returns the results promised as a list. This blocks the underlying execution thread until the execution has finished if it is not yet.

Returns:the results promised
Return type:list

Further informations

It is written by Hong Minhee and distributed under MIT license.

The source code can be found from the GitHub project page and checked out via Git.

https://github.com/lunant/SQLAlchemy-Future

$ git clone git://github.com/lunant/SQLAlchemy-Future.git

Indices and tables