Connection pooling with Python + MySQL

How to do connection pooling with MySQL in Python and what to do with it.

mysql-connector-python MySQLConnectionPool issue

First, if you simply want to create a connection with the official MySQL Python client mysql-connector-python, you can write:

import mysql.connector

cnx = mysql.connector.connect(host="...", user="...", password="...", database="...")

This mysql-connector-python provides a class called MySQLConnectionPool, which makes it easy to do connection pooling. [^ MySQLConnectionPool]

from mysql.connector.pooling import MySQLConnectionPool

cnxpool = MySQLConnectionPool(host="...", user="...", password="...", database="...", pool_size=10)

cnx = cnxpool.get_connection()

cnx.close()  #The connection is not actually disconnected and is released into the connection pool

Congratulations ... ** is not the case. ** **

Try running the following code.

from mysql.connector.pooling import MySQLConnectionPool

cnxpool = MySQLConnectionPool(host="...", user="...", password="...", database="...", pool_size=3)

cnxpool.get_connection()
cnxpool.get_connection()
cnxpool.get_connection()
cnxpool.get_connection()  #=> PoolError: Failed getting connection; pool exhausted

MySQLConnectionPool is designed to ** throw an exception when the pool connections are exhausted **.

I don't hate this simple specification, but when actually using it in a Web application, it is often nice to have the behavior of "waiting for another connection to be released". Otherwise, an error will occur even if the number of parallel requests processed exceeds the pool size even a little.

SQLAlchemy QueuePool Solution

This problem can be solved by using SQLAlchemy's QueuePool. [^ Queue Pool]

from sqlalchemy.pool import QueuePool

cnxpool = QueuePool(lambda: mysql.connector.connect(host="...", user="...", password="...", database="..."), pool_size=10)

cnx = cnxpool.connect()

It seems that the first argument of QueuePool can be any function that returns "connection object corresponding to DB API". It's versatile and amazing.

The module itself called SQLAlchemy is an ORM, but this time we are using only the mechanism called QueuePool without using the ORM part at all.