Python2.7 中的 MySQL 连接池无法按预期工作。连接池的 close() 函数面临的问题

Question

我已经为 MySQL 连接池获取了 python 示例代码，并尝试使用 django 框架执行它。如果我不关闭游标和连接对象，我就可以执行查询并检索数据。当我调用 close() 函数连接对象时，它失败并出现以下错误，请帮我解决这个问题。

ProgrammingError at /settings 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

Method: GET

Request URL: http://sample.app.com/settings

Django Version: 1.11.5

Exception Type: ProgrammingError

Exception Value: 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

Exception Location: venv/local/lib/python2.7/site-packages/mysql/connector/connection.py in _auth_switch_request, line 256 Python Executable: venv/bin/python2

Python Version: 2.7.17

我正在使用的代码，粗体编码正在创建问题。如果不关闭连接，它工作正常。当我关闭它时它不起作用并引发错误。

db_pool.py

import mysql.connector.pooling

dbconfig = {
    "host":"127.0.0.1",
    "port":"3306",
    "user":"root",
    "password":"root",
    "database":"test",
}


class MySQLPool(object):
    """
    create a pool when connect mysql.
    """
    def __init__(self, host="127.0.0.1", port="3306", user="root",
                 password="root", database="test", pool_name="mypool",
                 pool_size=3):
        res = {}
        self._host = host
        self._port = port
        self._user = user
        self._password = password
        self._database = database

        res["host"] = self._host
        res["port"] = self._port
        res["user"] = self._user
        res["password"] = self._password
        res["database"] = self._database
        self.dbconfig = res
        self.pool = self.create_pool(pool_name=pool_name, pool_size=pool_size)

    def create_pool(self, pool_name="mypool", pool_size=3):
        """
        Create a connection pool
        """
        pool = mysql.connector.pooling.MySQLConnectionPool(
            pool_name=pool_name,
            pool_size=pool_size,
            pool_reset_session=True,
            **self.dbconfig)
        return pool

    > > def close(self, conn, cursor):
    > > """ Here I'm facing issue, close is causing the issue. this close must release the connection object and add it to the connection pool. """
    > >   cursor.close()
    > >   conn.close()

    def execute(self, sql, args=None, commit=False):
        """
        Execute a sql
        """
        # get connection form connection pool.
        conn = self.pool.get_connection()
        cursor = conn.cursor()
        if args:
            cursor.execute(sql, args)
        else:
            cursor.execute(sql)
        if commit is True:
            conn.commit()
            self.close(conn, cursor)
            return None
        else:
            res = cursor.fetchall()

            self.close(conn, cursor)
            return res

在另一个文件中使用上述代码 db_operation.py

from db_pool import MySQLPool

def testing(request, bid, *args, **kwargs):

    mysql_pool = MySQLPool()
    query = "select * from test.table1;"
    result = mysql_pool.execute(query)
    print 'RESULT : ', result

请帮助我解决这个问题或分享是否有正确使用连接池的最佳示例。
提前致谢。

Answer 1

我已经解决了这个问题。默认情况下，pool_reset_session 设置为 True 并将其修改为 False。现在，它运行良好。

当 pool_reset_session 为 true 时，它​​将使用主机、用户和无密码的默认配置重置 session 。根据我当前的数据库连接，没有密码是不允许的。因此， pool_reset_session 引起了问题并解决了它。

谢谢。