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?

Christopher · Accepted Answer · 2014-06-08 19:21:50Z

3

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()

answered Jun 8, 2014 at 19:21

Christopher

6432 gold badges7 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

R4PH43L Over a year ago

Sorry to say that, but it is not a flaw. Just like the [official documentation][1] on the python mysql-connector states out, pooled connections do always return to the pool after beeing closed. What you could try to make this work: g.cnx_pool.get_connection().cursor().execute(query, (request.get_data(),))) When you are using cursor = g.cnx_pool.get_connection().cursor(), your connection is returned right after cursor c'tor finishes. using my approach it should be returned after the execution.

LuciferJack · Accepted Answer · 2017-09-26 04:22:04Z

1

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)

answered Sep 26, 2017 at 4:22

LuciferJack

79912 silver badges13 bronze badges

Collectives™ on Stack Overflow

How to use mysql.connection db pool with python flask

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related