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
- When a service is started, it is created first
Default connection numberthefreeThe connection is put into the pool. - When a user needs to connect, first check to see if there is one in the pool
freeThe 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.
- 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 codeThemisPool.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 codeDb.cnf configuration file
[mysql]
host = localhost
user = root
password = 12345678
database = practice
initsize = 3
maxsize = 6
Copy the codeAll configuration properties
| parameter | instructions | type | The default value |
|---|---|---|---|
| host | The host address | str | localhost |
| port | The port number | int | 3306 |
| user | User name for logging in to mysql | str | root |
| password | Mysql login password | str | – |
| database | Access to the library | str | – |
| initsize | Initialize the number of connections | int | 3 |
| maxsize | Maximum number of connections | int | 6 |
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 codeCustom 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 codeAfter 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 😘