How to create a mysql connection pool or any bette

Question

In my code I am opening two mysql connections and using HTTP requests to insert data into database

g.db = mysql.connector.connect(user=a ,password=password, host=localhost,database=mysq1)
g.db1 = mysql.connector.connect(user=b,password=password, host=localhost,database=mysql2)

@app.route('/user/<db>')  
def insert(db):
   #code for inserting data into mysql1 database
   #code for inserting data into mysql2 database

I am making HTTP requests to select the databases.

  curl -i 'localhost:5000/user/mysql1' #

It is working well, data is getting inserted into the selected database. But I was thinking of creating a connection pool for the two connections and then used that pool.

Questions:

1. How to implement the mysql connection pooling?

2. Is there other better way of initializing connections.Currently connection get opened at each request.

Answer 1

#!/usr/bin/python
# -*- coding: utf-8 -*-
import time
import mysql.connector.pooling


dbconfig = {
    "host":"127.0.0.1",
    "port":"3306",
    "user":"root",
    "password":"123456",
    "database":"test",
}


class MySQLPool(object):
    """
    create a pool when connect mysql, which will decrease the time spent in 
    request connection, create connection and close connection.
    """
    def __init__(self, host="172.0.0.1", port="3306", user="root",
                 password="123456", database="test", pool_name="mypool",
                 pool_size=3):
        res = {}
        self._host = host
        self._port = port
        self._user = user
        self._password = password
        self._database = database

        res["host"] = self._host
        res["port"] = self._port
        res["user"] = self._user
        res["password"] = self._password
        res["database"] = self._database
        self.dbconfig = res
        self.pool = self.create_pool(pool_name=pool_name, pool_size=pool_size)

    def create_pool(self, pool_name="mypool", pool_size=3):
        """
        Create a connection pool, after created, the request of connecting 
        MySQL could get a connection from this pool instead of request to 
        create a connection.
        :param pool_name: the name of pool, default is "mypool"
        :param pool_size: the size of pool, default is 3
        :return: connection pool
        """
        pool = mysql.connector.pooling.MySQLConnectionPool(
            pool_name=pool_name,
            pool_size=pool_size,
            pool_reset_session=True,
            **self.dbconfig)
        return pool

    def close(self, conn, cursor):
        """
        A method used to close connection of mysql.
        :param conn: 
        :param cursor: 
        :return: 
        """
        cursor.close()
        conn.close()

    def execute(self, sql, args=None, commit=False):
        """
        Execute a sql, it could be with args and with out args. The usage is 
        similar with execute() function in module pymysql.
        :param sql: sql clause
        :param args: args need by sql clause
        :param commit: whether to commit
        :return: if commit, return None, else, return result
        """
        # get connection form connection pool instead of create one.
        conn = self.pool.get_connection()
        cursor = conn.cursor()
        if args:
            cursor.execute(sql, args)
        else:
            cursor.execute(sql)
        if commit is True:
            conn.commit()
            self.close(conn, cursor)
            return None
        else:
            res = cursor.fetchall()
            self.close(conn, cursor)
            return res

    def executemany(self, sql, args, commit=False):
        """
        Execute with many args. Similar with executemany() function in pymysql.
        args should be a sequence.
        :param sql: sql clause
        :param args: args
        :param commit: commit or not.
        :return: if commit, return None, else, return result
        """
        # get connection form connection pool instead of create one.
        conn = self.pool.get_connection()
        cursor = conn.cursor()
        cursor.executemany(sql, args)
        if commit is True:
            conn.commit()
            self.close(conn, cursor)
            return None
        else:
            res = cursor.fetchall()
            self.close(conn, cursor)
            return res


if __name__ == "__main__":
    mysql_pool = MySQLPool(**dbconfig)
    sql = "select * from store WHERE create_time < '2017-06-02'"

    # test...
    while True:
        t0 = time.time()
        for i in range(10):
            mysql_pool.execute(sql)
            print i
        print "time cousumed:", time.time() - t0

You could create a connection pool at the beginning with create_pool() which finally cause MySQLConnectionPool(), and when you need to connect to MySQL, you could get a connection with get_connection() from the pool, and when you do not need the connection you could add the connection back to the pool with conn.close().

Answer 2

Use ORM frameworks for making things easier, below is a basic and a general way we create a connection pool with out any ORM frameworks.

1. The mysql.connector.pooling module implements pooling.

2. A pool opens a number of connections and handles thread safety when providing connections to requesters.

3. The size of a connection pool is configurable at pool creation time. It cannot be resized thereafter.

Create your own pool and name it, myPool in the arguments of connection pooling, you can also declare the pool size = 5 (which is the number of database connections).

Please see below for more information:

dbconfig = {
  "database": "test",
  "user":     "joe"
}

cnx = mysql.connector.connect(pool_name = "mypool",
                              pool_size = 3,
                              **dbconfig)

dbconfig, database configuration is where you give all the configuration details, everytime you change your databse. In fact you can have multiple databases, if you want to.

Please see this MySQL documentation here

We can see more about how this arguments can be declared:

MySQLConnectionPool(pool_name=None,
                    pool_size=5,
                    pool_reset_session=True,
                    **kwargs)

This constructor instantiates an object that manages a connection pool.

Arguments in detail:

1. pool_name: The pool name. If this argument is not given, Connector/Python automatically generates the name, composed from whichever of the host, port, user, and database connection arguments are given in kwargs, in that order.

It is not an error for multiple pools to have the same name. An application that must distinguish pools by their
**pool_name** property should create each pool with a distinct name.

2. pool_size: The pool size. If this argument is not given, the default is 5.

You should see this some nice documentation here

For making your connection pool multithreaded, this post on stackoverflw might really help. Please see this post

Answer 3

Another way would be using any Web Server, I suggest you to use JBoss. You can create a datasource and configure it with the number of connections that you want to be controlled and let it be controlled by JBoss.