Pooling of database connections using Python, pymysql and dbutils

Pymysql acts as python client for MySQL and dbutils provides database connection pooling framework for multithreaded Python programs.Python threads in the example use database connections from the pool.

MySQL Updated 14 Apr 2018 4 min read

Overview of Database Connection Pooling

  • A database connection to a remote database server like a MySQL Server is a TCP/IP connection established by contacting the database server on a specific port.
  • For each database connection established from a client, a database server such as MySQL Server allocates a set of buffers to cater to the client needs. Based on design - the allocation, management and de-allocation of these buffers could get as complicated as it could on the server side.
  • When a database connection is closed, all of the allocated buffers are cleared in a graceful manner and the TCP sockets are closed as well.
  • The overhead involved equals to the number of database connections made to the server multiplied by the time and resources allocated per connection.
  • For database applications running in various layers of the deployment of distributed software architecture, it is often necessary to deal with several database connections.
  • However, opening these database connections every time afresh on need basis will result in poor performance and will affect the end use cases of applications.
  • To address the above issue(s), database connections are often created in a batch, cached, used, marked for reuse and reused, which is together known as pooling of database connections.

Database Connection pooling with PyMySQL and DBUtils

  • The PyMySQL is a client library for MySQL Server, which is compliant to Python Database API specification version 2.0.
  • PyMySQL is developed fully using the Python Programming Language.
  • The DBUtils is a Python library, which provides a database-connection-pooling framework for multithreaded Python environments.
  • DBUtils provides the PooledDB class, which represents a pool of database connections to a database server like MySQL.
  • The constructor of DBUtils.PooledDB takes a Python method as parameter, which returns a Python DB-API specification 2.0 compliant connection object or a python database module, compliant to the Python DB-API specification 2.0.
  • Calling connection() method on a PooledDB instance will return a connection(a PooledSharedDBConnection instance)from the connection pool.
  • Calling close() on the PooledSharedDBConnection instance will return the database connection for reuse.
  • The parameter maxconnections of the PooledDB constructor specifies the maximum number of connections that can be created for a database connection pool. If the blocking parameter is True, the requests for a connection to the pool will block till a connection is released.
  • If False, no blocking will happen upon exceeding the maximum number of connections available in the connection pool which could lead to the exception DBUtils.PooledDB.TooManyConnections.

Example

python
from threading import Thread

from datetime import datetime

import time

import pymysql

from DBUtils.PooledDB import PooledDB

# Definition of CrawlInfo

class CrawlInfo:

    def __init__(self, url_in, header_in, body_in, lastVisit_in):

        self.url             = url_in

        self.header          = header_in

        self.body            = body_in

        self.lastVisit      = lastVisit_in

# The thread function for MySQL Insert

# This thread will insert records into MySQL table using the connections

# from connection pool

def MySQLInsertThread(threadNum_in, dbConnection_in, crawlInfo_in):

    try:

        print("Thread{} started".format(threadNum_in))

        url         = crawlInfo_in.url

        header      = crawlInfo_in.header

        body        = crawlInfo_in.body

        lastVisit   = crawlInfo_in.lastVisit

        # SQL statement to insert into a MySQL database table

        sqlInsert   = "INSERT INTO crawled values ('{}','{}','{}','{}')".format(url, header, body, lastVisit)

        # Obtain a cursor object

        mySQLCursor = dbConnection_in.cursor()

        # Execute the SQL stament

        mySQLCursor.execute(sqlInsert)

        # Close the cursor and connection objects

        mySQLCursor.close()

        dbConnection_in.close()

        print("Thread{} exiting".format(threadNum_in))

    except Exception as e:

        print("Exception: %s" % e)

    return

# Information required to create a connection object

dbServerIP              = "127.0.0.1"       # IP address of the MySQL database server

dbUserName              = "root"            # User name of the MySQL database server

dbUserPassword          = ""                # Password for the MySQL database user

databaseToUse           = "TestDatabase"    # Name of the MySQL database to be used

charSet                 = "utf8mb4"         # Character set

cusrorType              = pymysql.cursors.DictCursor

Crawl_Info_Count        = 10

mySQLConnectionPool = PooledDB(creator   = pymysql,     # Python function returning a connection or a Python module, both based on DB-API 2

                               host      = dbServerIP,

                               user      = dbUserName,

                               password  = dbUserPassword,

                               database  = databaseToUse,

                               autocommit    = True,

                               charset       = charSet,

                               cursorclass   = cusrorType,

                               blocking      = False,

                               maxconnections = Crawl_Info_Count)

threadCollection    = []

crawlInfoCollection = []

# Create data to insert data into MySQL table

for i in range(Crawl_Info_Count):

    url         = "url"+str(i)

    header      = "header"+str(i)

    body        = "body"+str(i)

    lastVisit   = time.strftime('%Y-%m-%d %H:%M:%S')

    crawlInfoCollection.append(CrawlInfo(url, header, body, lastVisit))

# Create threads to insert data into MySQL table

for i in range(Crawl_Info_Count):

    mySQLConnection = mySQLConnectionPool.connection()

    t = Thread(target= MySQLInsertThread, args=(i, mySQLConnection,crawlInfoCollection[i]))

    threadCollection.append(t)

    # Start the db insert thread

    t.start()

    print("Number of database connections in use:{}".format(mySQLConnectionPool._connections))

# Proceed once the other threads are complete

for thread in threadCollection:

    thread.join()

Output

Output
Thread0 started

Number of database connections in use:1

Thread1 started

Number of database connections in use:2

Thread2 started

Number of database connections in use:3

Thread3 started

Number of database connections in use:4

Thread4 started

Number of database connections in use:5

Thread5 started

Number of database connections in use:6

Thread6 started

Number of database connections in use:7

Thread7 started

Number of database connections in use:8

Thread8 started

Number of database connections in use:9

Thread9 started

Number of database connections in use:10

Thread0 exiting

Thread1 exiting

Thread8 exiting

Thread5 exiting

Thread9 exiting

Thread4 exiting

Thread3 exiting

Thread6 exiting

Thread7 exiting

Thread2 exiting