How to use mysql.connection db pool with python flask

Question

I'm using flask 10.1 with mysql 5.6. I'd like to have a connection pool where individual requests can grab connections and insert data in parallel. The code I think should work is this:

# !flask/bin/python
from flask import Flask, jsonify, abort, make_response, request, g
import mysql.connector

app = Flask(__name__)

db_user = "user"
db_pass = "pass"
db_url = "127.0.0.1"


@app.before_first_request
def before_first_request():
    # configure the connection pool in the global object
    g.cnx_pool = mysql.connector.pooling.MySQLConnectionPool(pool_name="name",
                                                             pool_size=10,
                                                             autocommit=True,
                                                             user=db_user,
                                                             password=db_pass,
                                                             host=db_url,
                                                             database='db')

@app.route('/log', methods=['POST'])
def log_data():
    """
    Logs data
    """
    cursor = g.cnx_pool.get_connection().cursor()
    query = """INSERT INTO db.data (time,data) values (NOW(),%s)"""
    cursor.execute(query, (request.get_data(),))
    return make_response('', 200)

if __name__ == '__main__':
    app.run(debug=True)

But, the global connection pool object that I am storing in g seems to be being cleaned up. E.g. when I invoke the rest end point I see:

Traceback (most recent call last):
  File "/home/myuser/virtualenv/py2.7-myapp-server-logger/lib/python2.7/site-packages/flask/app.py", line 1836, in __call__
    return self.wsgi_app(environ, start_response)
  File "/home/myuser/virtualenv/py2.7-myapp-server-logger/lib/python2.7/site-packages/flask/app.py", line 1820, in wsgi_app
    response = self.make_response(self.handle_exception(e))
  File "/home/myuser/virtualenv/py2.7-myapp-server-logger/lib/python2.7/site-packages/flask/app.py", line 1403, in handle_exception
reraise(exc_type, exc_value, tb)
  File "/home/myuser/virtualenv/py2.7-myapp-server-logger/lib/python2.7/site-packages/flask/app.py", line 1817, in wsgi_app
    response = self.full_dispatch_request()
  File "/home/myuser/virtualenv/py2.7-myapp-server-logger/lib/python2.7/site-packages/flask/app.py", line 1477, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/home/myuser/virtualenv/py2.7-myapp-server-logger/lib/python2.7/site-packages/flask/app.py", line 1381, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/home/myuser/virtualenv/py2.7-myapp-server-logger/lib/python2.7/site-packages/flask/app.py", line 1475, in full_dispatch_request    
rv = self.dispatch_request()
  File "/home/myuser/virtualenv/py2.7-myapp-server-logger/lib/python2.7/site-packages/flask/app.py", line 1461, in dispatch_request
return self.view_functions[rule.endpoint](**req.view_args)
  File "/home/myuser/sandboxes/myapp-server-logger/app.py", line 45, in log_user_data
    cursor.execute(query, (username, request.get_data()))
  File "/home/myuser/virtualenv/py2.7-myapp-server-logger/lib/python2.7/site-packages/mysql/connector/cursor.py", line 463, in execute
    if self._have_unread_result():
  File "/home/myuser/virtualenv/py2.7-myapp-server-logger/lib/python2.7/site-packages/mysql/connector/cursor.py", line 288, in _have_unread_result
    return self._connection.unread_result
ReferenceError: weakly-referenced object no longer exists

I'm new to flask, is this an incorrect way of storing a connection pool object?

Answer 1

It turns out the issue is that the db connection is being cleaned up and returned to the pool before the cursor is used. One must keep the connection in scope while using the cursor. This seems like a design flaw in the mysql.connector python, and that the cursor should keep a strong reference to the connection object.

Change this line:

cursor = g.cnx_pool.get_connection().cursor()

To this

conn = g.cnx_pool.get_connection()
cursor = conn.cursor()

Answer 2

You can try this way,github

code example is :

app.config.update(
    DEBUG=False,
    MYSQL_DATABASE_HOST='10.95.130.***',
    MYSQL_DATABASE_PORT=8899,
    MYSQL_DATABASE_USER='root',
    MYSQL_DATABASE_PASSWORD='******',
    MYSQL_DATABASE_DB='flask',
    MYSQL_USE_POOL=
    {
        #use = 0 no pool else use pool
        "use":0,
        # size is >=0,  0 is dynamic pool
        "size":10,
        #pool name
        "name":"local",
    },
)
mysql = MySQL()
mysql.init_app(app)