librelist archives

Using SQLAlchemy with Flask

From:

Dan Jacob

Date:

2010-05-13 @ 12:23

The documentation for using SQLAlchemy without an ORM, i.e. just using
the SQL abstraction layer is detailed here:

http://flask.pocoo.org/docs/patterns/sqlalchemy/#sql-abstraction-layer

It mentions that you can use the engine directly, or using a connection, i.e.

conn = engine.connect()

However the SQLAlchemy docs recommend

(http://www.sqlalchemy.org/docs/dbengine.html?highlight=pooling#connectionless-execution-implicit-execution)
that closing the connection also returns the connection to the pool:

conn.close()

When using this pattern in the context of the Flask request cycle,
would it not be a good idea to explicitly create and close a
connection, and thus ensure that the connection is returned to the
pool at the end of a request ?

For example:

engine = create_engine(...)

@app.before_request
def connect_db():
    g.db = engine.connect()

@app.after_request
def close_db(response):
    g.db.close()
    return response

If this is best practice, should the documentation be updated slightly
to suggest this ?