python-mysqlHow do I set up a connection pool in Python using MySQL?

The following steps can be used to set up a connection pool in Python using MySQL:

1. Install the MySQL Connector Python library using the command pip install mysql-connector-python.

2. Create a connection pool using the MySQLConnectionPool() method. This method takes in several parameters, such as host, database, user, password, etc.

3. Create a connection object from the connection pool with the get_connection() method.

4. Execute queries using the connection object.

5. Close the connection object using the close() method.

6. Return the connection object to the connection pool using the release() method.

7. Finally, close the connection pool using the close() method.

Example code

import mysql.connector

# create connection pool
my_pool = mysql.connector.pooling.MySQLConnectionPool(
    host="localhost",
    database="my_database",
    user="my_user",
    password="my_password"
)

# get connection object from pool
my_connection = my_pool.get_connection()

# execute query
my_cursor = my_connection.cursor()
my_cursor.execute("SELECT * FROM my_table")

# print query result
print(my_cursor.fetchall())

# close connection
my_connection.close()

# return connection to pool
my_pool.release(my_connection)

# close connection pool
my_pool.close()

Output

[(1, 'John'), (2, 'Jane')]

List of code parts with explanation

• import mysql.connector: this imports the MySQL Connector Python library.
• my_pool = mysql.connector.pooling.MySQLConnectionPool(host="localhost", database="my_database", user="my_user", password="my_password"): this creates a connection pool with the MySQLConnectionPool() method.
• my_connection = my_pool.get_connection(): this creates a connection object from the connection pool with the get_connection() method.
• my_cursor = my_connection.cursor(): this creates a cursor object from the connection object.
• my_cursor.execute("SELECT * FROM my_table"): this executes a query using the cursor object.
• print(my_cursor.fetchall()): this prints the query result.
• my_connection.close(): this closes the connection object.
• my_pool.release(my_connection): this returns the connection object to the connection pool.
• my_pool.close(): this closes the connection pool.

Relevant links

• MySQL Connector Python
• MySQLConnectionPool()

Edit this code on GitHub