SQLAlchemy Connection Pool and Sessions

Question

I've recently started using SQLAlchemy and am trying to understand how the connection pool and session work in a web-application

I am building an API using flask.

__init__.py

engine = create_engine('mysql://username:password@localhost/my_database') 
DBSession = sessionmaker(bind=engine)

views.py

@app.route('/books', methods = ['GET'])
def getBooks():
    session = DBSession()
    returnedBooks = session.query(BOOK).all()
    session.close()

    return something...

1) Firstly, if I don't explicitly close my session object, will it automatically close after the request has been processed?

2) When my app receives multiple requests, are the multiple session objects being created all linked to the single engine object that was created in my __init__.py file?

3) Are the session objects being created in view.py the connections that the connection pool holds? If so, and these weren't closed, then would new connections have to be made for subsequent requests?

4) Should I be using dispose() at some point?

score 28 · Accepted Answer · edited Mar 01 '16 at 19:17

1) Firstly, if I don't explicitly close my session object, will it automatically close after the request has been processed?

The garbage collector will eventually* call __del__ on the session. if the session has not been sorted out by you in some other way, this will probably cause a rollback. You should usually be doing something like:

import contextlib
def myRequestHandler(args):
    with contextlib.closing(DBSession()) as session:
        result = do_stuff(session)
        if neccesary:
            session.commit() 
        else:
            session.rollback()
        return result

2) When my app receives multiple requests, are the multiple session objects being created all linked to the single engine object that was created in my __init__.py file?

yes, sessionmaker() holds on to all Session configuration. Using the pattern you've got, you will get a new session for every request, and that's a good thing.

3) Are the session objects being created in view.py the connections that the connection pool holds?

sessions and connections are not the same thing; although each session is ever using one connection at a time, and they return their connection to the pool when they're done with them.

If so, and these weren't closed, then would new connections have to be made for subsequent requests?

different pool implementations have different rules, but for most database engines, the default is QueuePool; and it has a default maximum connections of 15. subsequence requests for additional connections will block or timeout. Connections are reused when possible, though.

4) Should I be using dispose() at some point?

usually not, if you are also using pools and engines and sessions, then any additional resource management outside of committing/rolling back sessions is probably not needed

_{*eventually actually means sometime between immediately and never; don't count on it to do useful work; Note that your connections will be closed forceably by the OS when your process dies, so using GC for connection management doesn't buy you anything.}

SQLAlchemy Connection Pool and Sessions

1 Answers1