sqlalchemy mysql connections not closing on flask api

Question

I have an API I have written in flask. It uses sqlalchemy to deal with a MySQL database. I don't use flask-sqlalchemy, because I don't like how the module forces you into a certain pattern for declaring the model.

I'm having a problem in which my database connections are not closing. The object representing the connection is going out of scope, so I assume it is being garbage collected. I also explicitly call close() on the session. Despite this, the connections stay open long after the API call has returned its response.

sqlsession.py: Here is the wrapper I am using for the session.

class SqlSession:
    def __init__(self, conn=Constants.Sql):
        self.db = SqlSession.createEngine(conn)

        Session = sessionmaker(bind=self.db)
        self.session = Session()
    @staticmethod
    def createEngine(conn):
        return create_engine(conn.URI.format(user=conn.USER, password=conn.PASS, host=conn.HOST, port=conn.PORT, database=conn.DATABASE, poolclass=NullPool))

    def close(self):
        self.session.close()

flaskroutes.py: Here is an example of the flask app instantiating and using the wrapper object. Note that it instantiates it in the beginning within the scope of the api call, then closes the session at the end, and presumably is garbage collected after the response is returned.

def commands(self, deviceId):
    sqlSession = SqlSession(self.sessionType) <---

    commandsQueued = getCommands()
    jsonCommands = []
    for command in commandsQueued:
     jsonCommand = command.returnJsonObject()
     jsonCommands.append(jsonCommand)
     sqlSession.session.delete(command)
    sqlSession.session.commit()
    resp = jsonify({'commands': jsonCommands})
    sqlSession.close() <---  
    resp.status_code = 200
    return resp

I would expect the connections to be cleared as soon as the HTTP response is made, but instead, the connections end up with the "SLEEP" state (when viewed in the MySQL command line interface 'show processlist').

score 16 · Accepted Answer · edited May 23 '17 at 11:59

16

I ended up using the advice from this SO post: How to close sqlalchemy connection in MySQL

I strongly recommend reading that post to anyone having this problem. Basically, I added a dispose() call to the close method. Doing so causes the entire connection to be destroyed, while closing simply returns connections to an available pool (but leave them open).

def close(self):
    self.session.close()
    self.db.dispose()

This whole this was a bit confusing to me, but at least now I understand more about the connection pool.

edited May 23 '17 at 11:59

Community

1
1

answered Mar 14 '15 at 07:26

melchoir55

6,842
7
60
106

1

Just commenting to let you know you helped me greatly, 3 years later – Sam Hollenbach Dec 18 '18 at 00:10
1

Same as @SamHollenbach, this helped me years later – booky99 Jul 04 '20 at 20:07
Wow, this is a huge implementation hole. Close should mean done and gone. I've been trying to figure out why my database has been running out of connections for a week now. This is particularly bad when you consider the recommended "with maker as session" syntax. – Robert Rapplean Jul 16 '21 at 23:55

sqlalchemy mysql connections not closing on flask api

1 Answers1