[Python/Mysql] Python MySQL Connection Pool (flask)

https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlconnectionpool.html

MySQL :: MySQL Connector/Python Developer Guide :: 10.3 pooling.MySQLConnectionPool Class

간단한 소스

import mysql.connector
import mysql.connector.pooling

dbconfig = {
  "database": "test",
  "user":     "joe",
}

cnxpool = mysql.connector.pooling.MySQLConnectionPool(pool_name = "mypool", pool_size = 3, **dbconfig)
def selectUsers():
  dbconn = cnxpool.get_connection()
  cursor = dbconn.cursor(dictionary=True)
  sql = '''
    SELECT
      users.user_id,
      users.user_name
    FROM users
  '''
  cursor.execute(sql)
  result = cursor.fetchall()
  return result

 

 

 

Error

 

module 'mysql.connector' has no attribute 'pooling'**

Traceback (most recent call last):
  File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/IPython/core/interactiveshell.py", line 2869, in run_code
    exec(code_obj, self.user_global_ns, self.user_ns)
  File "<ipython-input-4-b07112a83246>", line 1, in <module>
    cnxpool = mysql.connector.pooling.MySQLConnectionPool(pool_name = "mypool",pool_size = 3,\**dbconfig)
AttributeError: module 'mysql.connector' has no attribute 'pooling'**

 

Solution

import mysql.connector.pooling

mysql.connector.errors.NotSupportedError: MySQL version 5.7.2 and earlier does not support COM_RESET_CONNECTION.

 

Traceback (most recent call last):
  File "C:\Users\AppData\Local\Programs\Python\Python36-32\lib\site-packages\mysql\connector\connection.py", line 929, in reset_session
    self.cmd_reset_connection()
  File "C:\Users\AppData\Local\Programs\Python\Python36-32\lib\site-packages\mysql\connector\connection.py", line 1275, in cmd_reset_connection
    raise errors.NotSupportedError("MySQL version 5.7.2 and "
mysql.connector.errors.NotSupportedError: MySQL version 5.7.2 and earlier does not support COM_RESET_CONNECTION.

mysql.connector.errors.PoolError: Failed getting connection; pool exhausted

 

Traceback (most recent call last):
  File "C:\Users\AppData\Local\Programs\Python\Python36-32\lib\site-packages\mysql\connector\pooling.py", line 321, in get_connection
    cnx = self._cnx_queue.get(block=False)
  File "C:\Users\AppData\Local\Programs\Python\Python36-32\lib\queue.py", line 161, in get
    raise Empty
queue.Empty

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "C:\Users\AppData\Local\Programs\Python\Python36-32\lib\site-packages\flask\app.py", line 2464, in __call__
    return self.wsgi_app(environ, start_response)
  File "C:\Users\AppData\Local\Programs\Python\Python36-32\lib\site-packages\flask\app.py", line 2450, in wsgi_app
    response = self.handle_exception(e)
  File "C:\Users\AppData\Local\Programs\Python\Python36-32\lib\site-packages\flask\app.py", line 1867, in handle_exception
    reraise(exc_type, exc_value, tb)
  File "C:\Users\AppData\Local\Programs\Python\Python36-32\lib\site-packages\flask\_compat.py", line 39, in reraise
    raise value
  File "C:\Users\AppData\Local\Programs\Python\Python36-32\lib\site-packages\flask\app.py", line 2447, in wsgi_app
    response = self.full_dispatch_request()
  File "C:\Users\AppData\Local\Programs\Python\Python36-32\lib\site-packages\flask\app.py", line 1952, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "C:\Users\AppData\Local\Programs\Python\Python36-32\lib\site-packages\flask\app.py", line 1821, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "C:\Users\AppData\Local\Programs\Python\Python36-32\lib\site-packages\flask\_compat.py", line 39, in reraise
    raise value
  File "C:\Users\AppData\Local\Programs\Python\Python36-32\lib\site-packages\flask\app.py", line 1950, in full_dispatch_request
    rv = self.dispatch_request()
  File "C:\Users\AppData\Local\Programs\Python\Python36-32\lib\site-packages\flask\app.py", line 1936, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "c:\works\database.py", line 362, in selectNotificationList
    dbconn = cnxpool.get_connection()  # db와 연결
  File "C:\Users\AppData\Local\Programs\Python\Python36-32\lib\site-packages\mysql\connector\pooling.py", line 324, in get_connection
    "Failed getting connection; pool exhausted")
mysql.connector.errors.PoolError: Failed getting connection; pool exhausted

 

Solution

# pool_size를 늘린다.
cnxpool = mysql.connector.pooling.MySQLConnectionPool(pool_name = "mypool", pool_size = 10, **dbconfig)

SHOW VARIABLES LIKE '%max_connection%';
SHOW PROCESSLIST;