Search code examples
pythonflaskmysql-pythonmysql-connector

How to create a mysql connection pool or any better way to initialize the multiple databases?

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.

Solution

  • 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