Python makes a simple mysql connection pool

Today we are going to do something a little different, using Python to hand lift mysql connection pool.

What is a connection pool?

Connection pooling is the technique of creating and managing a buffer pool of connections, ready to be used by any thread that needs them. Connection pooling generally performs better than direct connections when there is sufficient concurrency, not only improving performance but also managing valuable resources.

Why do YOU need connection pooling?

When discussing this issue, we need to understand why high concurrency causes the server to stall.

Normally, whenever a user connects to the server using a terminal of any kind, the server needs to allocate a piece of memory for it, and whenever a request comes in from the front end, a connection needs to be created between mysql. However, too many connections will lead to too high memory usage of the server. At this time, connection pool is required to manage all connection status and allocate and reclaim resources reasonably.

Simply put, using connection pooling technology can reduce server stress.

How does connection pooling work?

The connection pool requires two parameters: the default number of connections and the maximum number of connections

1. When a service is started, it is created firstDefault connection numberthefreeThe connection is put into the pool.
2. When a user needs to connect, first check to see if there is one in the poolfreeThe connection.
   • If you have: Retrieves one from the pool allocated by the poolfreeThe connection is delivered to the user.
   • If there is no: Checks whether the total number of existing connections is greater thanThe biggestThe connection.
     • If less than: Creates a new connection and delivers it to the user.
     • If a is equal to the: The thread is blocked, waiting forfreeThe connection is then handed to the user.
3. When the user runs out of connections, check whether the number of active connections is greater than the default value.
   • If it's less than or equal to: Put the connection back infreePool, waiting for the next use.
   • If more than: Release the connection and destroy it without putting it into the pool.

Use python to create a simple mysql connection pool

Here, we need themispool.py connection pool itself, db.cnf configuration file, whose directory path is as follows:

Themispool.py & db.cnf only needs to be in the same directory
[your python project]
    |
    |
    |-- util
         |
         |-- db.cnf
         |
         |-- ThemisPool.py

Copy the code

ThemisPool.py

# import dependencies
Mysql connects to the base library
import pymysql

Libraries needed to read configuration files
import configparser
import os

Library required for thread management
import threading


The user reads the configuration file
class Config(object) :
    def __init__(self, configFileName='db.cnf') :
        file = os.path.join(os.path.dirname(__file__), configFileName)
        self.config = configparser.ConfigParser()
        self.config.read(file)

    def getSections(self) :
        return self.config.sections()

    def getOptions(self, section) :
        return self.config.options(section)

    def getContent(self, section) :
        result = {}
        for option in self.getOptions(section):
            value = self.config.get(section, option)
            result[option] = int(value) if value.isdigit() else value
        return result
 
Encapsulate the parameters needed for the connection in an object
[default localhost] port number [default 3306] Initial number of connections [default 3] Maximum number of connections [default 6]
class parameter(object) :
    def __init__(self, password, database, host="localhost",port="3306" user="root", initsize=3, maxsize=6) :
        self.host = str(host)
        self.port = int(port)
        self.user = str(user)
        self.password = str(password)
        self.database = str(database)
        self.maxsize = int(maxsize)
        self.initsize = int(initsize)

# connection pool
class ThemisPool(parameter) :
    def __init__(self, fileName='db.cnf', configName='mysql') :
        Mysql > select db.cnf, db.cnf, db.cnf, db.cnf, db.cnf
        self.config = Config(fileName).getContent(configName)
        super(ThemisPool, self).__init__(**self.config)
        Create queue as pool
        self.pool = queue.Queue(maxsize=self.maxsize)
        self.idleSize = self.initsize
        Create a thread lock
        self._lock = threading.Lock()
        Initialize the connection pool
        for i in range(self.initsize):
            Create an initial number of connections into the pool
            self.pool.put(self.createConn())
        # startup log
        print('\033[1;32m ThemisPool connect database {database}, login is {user} \033[0m'.format(database=self.database,
                                                                                 user=self.user))
                                                                          
    # Production connection
    def createConn(self) :
        Use the mysql base class
        # pymysql. Connect parameters here without explanation, specific please refer to the website https://pypi.org/project/PyMySQL/
        return pymysql.connect(host=self.host,
                               port=self.port,
                               user=self.user,
                               password=self.password,
                               database=self.database,
                               charset='utf8')
    
    Get the connection
    def getConn(self) :
        self._lock.acquire()
        try:
            If the pool is connected enough to fetch directly
            if not self.pool.empty():
                self.idleSize -= 1
            else:
                Otherwise add a new connection
                if self.idleSize < self.maxsize:
                    self.idleSize += 1
                    self.pool.put(self.createConn())
        finally:
            self._lock.release()
            return self.pool.get()
     
    # release connection
    def releaseCon(self, conn=None) :
        try:
            self._lock.acquire()
            If the pool is larger than the initial value, close the excess, otherwise put it back into the pool
            if self.pool.qsize() < self.initsize:
                self.pool.put(conn)
                self.idleSize += 1
            else:
                try:
                    Remove excess connections and close them
                    surplus = self.pool.get()
                    surplus.close()
                    del surplus
                    self.idleSize -= 1
                except pymysql.ProgrammingError as e:
                    raise e
        finally:
            self._lock.release()
      
      
    # pull data (query)
    # Available statement type (SELECT)
    def fetchone(self, sql) :
        themis = None
        cursor = None
        try:
            themis = self.getConn()
            cursor = themis.cursor()
            cursor.execute(sql)
            return cursor.fetchall()
        except pymysql.ProgrammingError as e:
            raise e
        except pymysql.OperationalError as e:
            raise e
        except pymysql.Error as e:
            raise e
        finally:
            cursor.close()
            self.releaseCon(themis)
     
    # update
    # Available statement types (INSERT, update, delete)
    def update(self, sql) :
        themis = None
        cursor = None
        try:
            themis = self.getConn()
            cursor = themis.cursor()
            cursor.execute(sql)
            return cursor.lastrowid
        except pymysql.ProgrammingError as e:
            raise e
        except pymysql.OperationalError as e:
            raise e
        except pymysql.Error as e:
            raise e
        finally:
            themis.commit()
            cursor.close()
            self.releaseCon(themis)
            
     Release the connection pool itself
     def __del__(self) :
        try:
            while True:
                conn = self.pool.get_nowait()
            if conn:
                conn.close()
        except queue.Empty:
            pass

Copy the code

Db.cnf configuration file

[mysql]
host = localhost
user = root
password = 12345678
database = practice
initsize = 3
maxsize = 6
Copy the code

All configuration properties

Begin to use

from util.ThemisPool import ThemisPool

Initialize the ThemisPool connection pool
db = ThemisPool()

Query pull data.It returns data directly.
selectSql = "select * from user;"
data = db.fetchone(selectSql)

# add, delete, change statements Insert,upate delete and alter. If there is a value function inserted using mysql, insert,upate delete and alter self-growth, it will return self-growth data)
insertSql = "insert into user values(null,'user001','123456')"
id = db.update(selectSql)
Copy the code

Custom configuration file name & configuration label

The default configuration file name is db.cnf and the default configuration tag is [mysql].

For example, the name of the custom configuration file is mydb.cnf and the configuration label is [mysqlConfig].

# myDB.cnf

[mysqlConfig]
host = localhost
user = root
password = 12345678
database = practice
initsize = 3
maxsize = 6
Copy the code

# when using. db = ThemisPool(fileName='myDB.cnf', configName='mysqlConfig')...Copy the code

After thinking

Themis, named after the Ancient Greek goddess of order, acts like a connection pool, managing all users’ connections and reducing unnecessary waste.

Making the address

ThemisPool connection pool

That’s all for this time. It will be solved in the next versionpythonTo be unable todatetimeType of data carried outJson formattingAnd integrate it

If you find something wrong with this article or have a better suggestion, feel free to leave it in the comments section and thank you for visiting 😘