Tornado + SQLAlchemy

[Lorenzo Bolla]

Hi all,

I'd like to use SQLAlchemy with Tornado and I'd like to have some comments from whoever used it and is experienced with it.

Basically, what I want to avoid is making stupid mistakes like leaving open connections, open transactions, etc.

I figured out that something like these might work (my app is single threaded and no async calls are used, so really trivial).

class Application(tornado.web.Application):

    def __init__(self, auth_handlers=None):

        handlers = (

                ... list handlers here...

                )

        settings = {

             ... settings here...

            }

        tornado.web.Application.__init__(self, handlers, **settings)

        engine = create_engine(dbname)

        self.db = scoped_session(sessionmaker(bind=engine))

Then, each handler looks like this:

class MyHandler(tornado.web.RequestHandler):

    def get(self):

        ... do something with self.application.db ...

        self.application.db.commit()

    def on_finish(self):

        self.application.db.rollback() # rollback whatever was not committed explicitely

What do you think?

Would this work? Is it the right way of using sqlalchemy sessions?

Thanks,

L.

[Peter Bengtsson]

How do you envision doing re-connections?

[Sergey Koval]

Hi,

I used SQLAlchemy with Tornado before (not really with Tornado, but sockjs-tornado running on top of Tornado, but same principles apply).

Requirement I had - use same code in both Flask (web app) and Tornado (sockjs-tornado, realtime app).

Hacked up small gist illustrating how I use it:  https://gist.github.com/2175276 

Before any questions - yes, I use it synchronously, as my application is not going to database very often and there's no point messing with asynchronous database calls.

Serge.

[Dmitri Cherniak]

It seems like you can provide a pool_recycle option to your create_engine statement if you are using MySQL to limit the age of your connections.

http://docs.sqlalchemy.org/en/latest/dialects/mysql.html#connection-timeouts 

Anyone know if this will do the trick with reconnects?

[Lorenzo Bolla]

Thanks Sergey, very nice gist.

I find it useful because it wraps in a simple interface all the sqlalchemy calls one has to remember to do.

One question: your ScopedQuery is used also for "read-only" queries, and it is still going to "commit" or "rollback" and, finally, "remove" the session. Is the "remove" bit strictly necessary? Reading here (http://docs.sqlalchemy.org/en/latest/orm/session.html#lifespan-of-a-contextual-session), it looks like "rollback" or "commit" or "close" is enough.

@Peter: I guess, that, given Sergey ScopedQuery obj, one can handle reconnects in its __exit__ method, right?

Thanks,

L.   

[Dmitri Cherniak]

After reading this I've started trying to port some things to sqlalchemy using a model like this https://gist.github.com/2185742

Basically I have uniform access through my handlers to the sessionmaker.

I don't understand the advantage of using scoped_session though, could someone explain? Also, why not utilize connection pooling via pool_size parameter? If there's anything I'm missing out on I'd love to learn more.

Note: I had issues with "MySql has gone away errors" so I'm trying with pool_recycle now so see if that will have an effect. If anyone with SqlAlchemy experience can let me know I'd love to hear more.