Implementation example of python self made simple mysql connection pool
- 2021-12-12 05:09:46
- OfStack
Today, let's talk about the mysql connection pool by hand using python language.
What is a connection pool?
Connection pooling is a technique for creating and managing a buffer pool of 1 connection ready to be used by any thread that needs them. Connection Pool 1 generally performs better than direct connections when the amount of concurrency is sufficient, which not only improves performance but also manages valuable resources.
Why do you need connection pooling?
When discussing this issue, we need to first understand what causes the server to jam due to high concurrency.
Under normal circumstances, Whenever a user connects to the server using various terminals, Servers need to open up a piece of memory for its services, every time a request from the front-end to create a connection between mysql. However, too many connections will lead to server jamming memory occupation is too high, at this time, the need for connection pool to manage all the connection status, reasonable allocation & Recycling resources.
Simply put, using connection pooling technology can reduce server pressure.
What is the principle of connection pooling?
Connection pool mainly needs two parameters, default connection number and maximum connection number
When the service starts, free connections with the default number of connections are first created and put into the pool. When a user needs a connection, first check whether there are free connections in the pool. If yes: Take out 1 idle connection from the pool by connection pool allocation and deliver it to the user. If not: Check whether the total number of current surviving connections is greater than the maximum connection. If less than: Create a new connection and deliver it to users. If it is equal to: the thread is blocked, waiting for an idle connection to be handed over to the user. When the user runs out of connections, check whether the current number of surviving connections is greater than the default value. If less than or equal to: Put this connection back into the free pool and wait for the next use. If greater than: Release and destroy this connection and do not put it in the pool.
Using python Language to Make Simple mysql Connection Pool
Here, we need the ThemisPool. py connection pool itself, the db. cnf configuration file with the following directory path:
# Recommended directory format , ThemisPool.py & db.cnf You only need to be in the sibling directory
[your python project]
|
|
|-- util
|
|-- db.cnf
|
|-- ThemisPool.py
ThemisPool.py
# Import dependencies
# mysql Connect the basic library
import pymysql
# Libraries required to read configuration files
import configparser
import os
# Libraries required for thread management
import threading
# Create Configuration Class User Read 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 required for the connection in an object
# In order : Database password, library name to connect to, host address [ Default localhost] , port number [ Default 3306] Initialize the 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'):
# Load configuration file , The profile name defaults to 'db.cnf', The configuration label defaults to 'mysql'
self.config = Config(fileName).getContent(configName)
super(ThemisPool, self).__init__(**self.config)
# Create queues 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 Number of initialized connections Number of connections put 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 mysql Basic class
# pymysql.connect Parameters are not explained here , Please refer to official website for details 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 a connection
def getConn(self):
self._lock.acquire()
try:
# If there are enough connections in the pool to get directly
if not self.pool.empty():
self.idleSize -= 1
else:
# Otherwise, add a new connection again
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, the surplus will be closed, otherwise it will be put back into the pool
if self.pool.qsize() < self.initsize:
self.pool.put(conn)
self.idleSize += 1
else:
try:
# Remove redundant 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 types (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
db. cnf configuration file
[mysql]
host = localhost
user = root
password = 12345678
database = practice
initsize = 3
maxsize = 6
All Configuration Properties
| 参数 | 说明 | 类型 | 默认值 |
|---|---|---|---|
| host | 主机地址 | str | localhost |
| port | 端口号 | int | 3306 |
| user | mysql登录用户名 | str | root |
| password | mysql登录密码 | str | - |
| database | 访问库名 | str | - |
| initsize | 初始化连接数 | int | 3 |
| maxsize | 最大连接数 | int | 6 |
Start using
from util.ThemisPool import ThemisPool
# Initialization ThemisPool Connection pool (Initialize the ThemisPool connection pool)
db = ThemisPool()
# Query pull data , Function returns data directly (Query pull data.It returns data directly)
selectSql = "select * from user;"
data = db.fetchone(selectSql)
# Add, delete and change sentences , If there is a use mysql The self-growing insert value function returns self-growing data (insert,upate delete and alter. If there is a value function inserted using mysql self-growth, it will return self-growth data)
insertSql = "insert into user values(null,'user001','123456')"
id = db.update(selectSql)
Custom profile name
&
Configuration label
The configuration file name defaults to db. cnf and the configuration label defaults to [mysql]
For example, the custom configuration file name is myDB. cnf, and the configuration label is [mysqlConfig]
# myDB.cnf
[mysqlConfig]
host = localhost
user = root
password = 12345678
database = practice
initsize = 3
maxsize = 6
# When in use
...
db = ThemisPool(fileName='myDB.cnf', configName='mysqlConfig')
...
Naming ideas
Themis (Themis) is named after the goddess of order in ancient Greek mythology, just like the function of connection pool 1, which manages the connections of all users and reduces unnecessary losses.
GitHub Address
ThemisPool Connection Pool
That's all this time. The next version will solve the problem that python can't format datetime data with json and integrate it into it