SQLAlchemy connection autoclose on function failure

Say I have the following function:

@app.route("/function")
def function():
    engine = sqlalchemy.getEngine() # simplified to indicate we get an engine
    connection = engine.connect()
    result = utils.doThings(connection)
    connection.close()

If utils.doThings fails and my api call would usually just crash and write a report of the crash. Will my connection remain alive indefinitely? Or will overtime, the database prune the connections that have been idle? Is there a better way to handle this behavior than having a try except block which closes the connection on except?

Solution

Connections are closed before they are deleted as they fall out of scope. Since the connection is created in the handler function, when the function raises an error and exits, your connection will become ready for the garbage collector to delete.

However, two points to add, the engine of SQLAlchemy manages a pool of connection and could/should be share by the different handlers, and the connection can be used as a context manager which will ensure the connection is deleted for you (see the tutorial).

All in all, this is what your Flask app could look like:

import sqlalchemy as sa

engine = sa.create_engine("sqlite://")

@app.route("/hello")
def hello():
    try:
        with engine.connect() as con:
            result = con.execute(sa.text("SELECT 'hello'")).scalars().one()
    except Exception as e:
        print(e)  # please log better than this
    return result.encode("utf-8"), 200