Shulou(Shulou.com)06/02 Report--

This article mainly explains "what is the realization method of python self-made simple mysql connection pool". The content of the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what is the implementation method of python self-made simple mysql connection pool".

What is the connection pool?

Connection pooling is a technique for creating and managing a buffer pool of connections that are ready to be used by any thread that needs them. When the concurrency is sufficient, the performance of connection pool is generally better than that of direct connection, which not only improves the performance but also manages valuable resources.

Why do I need connection pooling?

When discussing this problem, we need to understand the cause of server stutter caused by high concurrency.

Normally, whenever a user uses various terminals to connect to the server, the server needs to open up a piece of memory to serve it, and every time a request comes from the front end, a connection needs to be created between the mysql. However, too many connections will lead to excessive stutter memory consumption of the server, so it is necessary for the connection pool to manage all connection states and allocate and recycle resources reasonably.

To put it simply, the use of connection pooling technology is available to reduce server pressure.

What is the principle of connection pooling?

Connection pool mainly requires two parameters, the default number of connections and the maximum number of connections

When the service starts, first create the default number of free connections and put them in the pool.

When a user needs a connection, first check to see if there is a free connection in the pool.

If less than: create a new connection and deliver it to the user.

If equal to: thread blocking, wait for a free connection before handing it over to the user.

If any: a free connection from the pool is allocated by the connection pool and handed over to the user for use.

If not: check to see if the total number of existing connections is greater than the maximum connection.

When the user has finished using the connection, check to see if the current number of viable 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: this connection will be released and destroyed and will not be put into the pool.

Using Python language to make a simple mysql connection pool

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

# recommended directory format. ThemisPool.py & db.cnf only need to be in the same level directory [your python project] |-- util | |-- db.cnf | |-- ThemisPool.py

ThemisPool.py

# Import depends on # mysql connection basic library import pymysql# to read configuration files required libraries for import configparserimport os# thread management import threading# creation 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 # encapsulates the parameters required for the connection in the object # in turn: database password, library name to be connected, host address [default localhost], port number [default 3306], number of initial 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, which defaults to 'db.cnf', configuration tag' mysql' 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 thread lock self._lock = threading.Lock () # initialize connection pool for i in range (self.initsize): # initial creation Put the number of connections into the pool self.pool.put (self.createConn ()) # launch 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 For details, please refer to the official 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 connection def getConn (self): self._lock.acquire () try: # if the connection in the pool is enough to directly obtain 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 greater than the initial value, it will be superfluously closed Otherwise, put if self.pool.qsize () < self.initsize: self.pool.put (conn) self.idleSize + = 1 else: try: # remove excess connections and close surplus = self.pool.get () Surplus.close () del surplus self.idleSize-= 1 except pymysql.ProgrammingError ase: 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

Db.cnf profile

[mysql] host = localhostuser = rootpassword = 12345678database = practiceinitsize = 3maxsize = 6

All configuration properties

Parameter description type default value host host address strlocalhost port number int3306usermysql login user name strrootpasswordmysql login password str-database access library name str-initsize initialization number int3maxsize maximum number of connections int6 start to use

From util.ThemisPool import ThemisPool# initializes ThemisPool connection pool (Initialize the ThemisPool connection pool) db = ThemisPool () # query to pull data, and the function will directly return data (Query pull data.It returns data directly) selectSql = "select * from user;" data = db.fetchone (selectSql) # add, delete, and modify statements. If there is a value function that uses mysql self-growing insertion, it will return 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 is named myDB.cnf, and the configuration label is [mysqlConfig]

# myDB.cnf [mysqlConfig] host = localhostuser = rootpassword = 12345678database = practiceinitsize = 3maxsize = when in use... db = ThemisPool (fileName='myDB.cnf', configName='mysqlConfig').. Naming idea

Themis (Themis) is named after the goddess of order in ancient Greek mythology, just like the role of a connection pool, managing the connections of all users and reducing unnecessary wear and tear.

GitHub address

ThemisPool connection pool

Thank you for reading, the above is the content of "what is the realization method of python self-made simple mysql connection pool". After the study of this article, I believe you have a deeper understanding of what is the implementation method of python self-made simple mysql connection pool, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.