Creating mysql singleton pattern with PooledDB

The advantage of using a single example is to reduce the database links, or mysql will crash after multiple links

Direct code:

class Mysql(object):
    __instance = None

    def __new__(cls, *args, **kwargs):
        if cls.__instance is None:
            cls.__instance = super(Mysql, cls).__new__(cls, *args, **kwargs)
        return cls.__instance

    def __init__(self):
        self.mysql = PooledDB(creator=pymysql, mincached=10, maxcached=20,
                              host=settings.MYSQL_HOST,
                              port=settings.MYSQL_PORT,
                              user=settings.MYSQL_USER,
                              passwd=settings.MYSQL_PASSWORD,
                              db=settings.MYSQL_DB,
                              charset=settings.MYSQL_CHARSET)

    def getAll(self, sql):
        _conn = self.mysql.connection()
        _cursor = _conn.cursor(cursor=pymysql.cursors.DictCursor)
        _cursor.execute(sql)
        result = _cursor.fetchall()
        _cursor.close()
        _conn.close()
        return result

    def save_all(self, sql):
        _conn = self.mysql.connection()
        _cursor = _conn.cursor(cursor=pymysql.cursors.DictCursor)
        _cursor.execute(sql)
        _conn.commit()
        _cursor.close()
        _conn.close()


mysql = Mysql()
Parameters for PooledDB:
1. mincached, the minimum number of idle connections. If the number of idle connections is less than this number, the pool will create a new connection
2. maxcached, the maximum number of idle connections. If the number of idle connections is greater than this number, the pool will close the idle connections
3. maxconnections, the maximum number of connections,
4. blocking: when the number of connections reaches the maximum number of connections, if the value is True, the program requesting the connection will wait until the current number of connections is less than the maximum number of connections. If the value is False, an error will be reported,
5. maxshared when the number of connections reaches this number, the newly requested connections will share the allocated connections
Because mysql is the only variable used in the project, the above parameters have no role in yongdali

 

In the previous link mode, the link should be closed after each sql execution

try:
    cursor.execute(sql)
finally:
    cursor.close()

Tags: PHP MySQL SQL less Database

Posted on Sun, 03 Nov 2019 10:03:04 -0800 by munchy