Use DBUtils to connect to an ApsaraDB RDS for MySQL instance - ApsaraDB RDS

If your application uses the Python programming language and connections such as short-lived connections are frequently established between your application and an ApsaraDB RDS for MySQL instance or the maximum number of allowed connections to the RDS instance is reached, you can use DBUtils to connect to your RDS instance. DBUtils is a connection pool in the Python programming language. DBUtils helps reduce the frequency at which your application connects to the RDS instance and lower the main thread overhead of the RDS instance. This topic describes how to use DBUtils to connect to an RDS instance.

Prerequisites

Python whose version is in the range from 3.7 to 3.12 is installed on your application server.
The IP address of the server is added to an IP address whitelist of the RDS instance. For more information, see Configure an IP address whitelist.
Note
If your application is deployed on an Elastic Compute Service (ECS) instance that resides in the same region and virtual private cloud (VPC) as the RDS instance, you do not need to configure an IP address whitelist.

Preparations

Install DBUtils: Run the following command to install DBUtils on your server. In this example, DBUtils 3.1.0 is used. For more information, see DBUtils.
```
pip install DBUtils==3.1.0
```
Install PyMySQL: Run the following command to install PyMySQL. PyMySQL is required when you connect the server to the RDS instance.
```
pip install pymysql
```

Use DBUtils to connect to the RDS instance

Import related modules: Before you use DBUtils to connect to the RDS instance, run the following commands to import related modules:
```
from dbutils.pooled_db import PooledDB
import importlib
import pymysql
```

Build the DBUtilsDemo class: To facilitate method calls and management in subsequent operations, we recommend that you create the DBUtilsDemo class, configure connection pool parameters in the __init__ method, and add the _connect and _close methods to the class to obtain connections from and return connections to the connection pool.

class DBUtilsDemo:
    def __init__(self, url, user, password, database):
        db_config = {
            "host": url,
            "port": 3306,
            "user": user,
            "db": database,
            "password": password,
            "charset": "utf8",
            "connect_timeout": 3,
            "read_timeout": 10,
            "write_timeout": 10
        }
        '''
          For more information about common parameters of the DBUtils connection pool, see the "Common parameters of the connection pool" section of this topic.
            mincached: the initial number of idle connections in the connection pool. The value 0 specifies that no connections are established upon startup. 
            maxcached: the maximum number of idle connections in the pool. The value 0 or None specifies that no limits are imposed. 
            maxshared: the maximum number of shared connections. The value 0 or None specifies that all connections are dedicated. 
            maxconnections: the maximum number of connections that are allowed in most cases. The value 0 or None specifies that no limits are imposed. 
            blocking: the operation that is performed when the number of connections exceeds the upper limit. 
            maxusage: the maximum number of times that a connection can be reused. The value 0 or None specifies that no limits are imposed. 
        '''
        self.pooled = PooledDB(pymysql, maxcached=20, maxshared=0, maxconnections=100, maxusage=20, **db_config)

    # Obtain a connection from the connection pool.
    def _connect(self):
        try:
            r = self.pooled.connection()
            return r
        except Exception as e:
            print("Failed to connect:" + str(e))

    # Return a connection to the connection pool.
    def _close(self, conn, stmt):
        if stmt:
            stmt.close()
        if conn:
            conn.close()

Initialize the DBUtilsDemo class in the __main__ function and connect to an RDS instance: After the DBUtilsDemo class is constructed, you can initialize the class in the __main__ function and obtain a connection to a database on the RDS instance.

if __name__ == '__main__':
    # The endpoint of the RDS instance. You must specify the internal or public endpoint of the RDS instance based on the requirements of your server. 
    url = 'rm-bp**************.mysql.rds.aliyuncs.com'
    # Specify the username that is used to access the RDS instance. 
    user = 'dbuser'
    # Specify the password that is used to access the RDS instance. 
    password = '****'
    # Specify the name of the database to which you want to connect. 
    database = 'dbtest'

    # Obtain the connection object.
    poolUtils = DBUtilsDemo(url, user, password, database)

Common parameters of the connection pool

If you use DBUtils to connect to an RDS instance, we recommend that you configure the required parameters for the connection pool to ensure that the RDS instance runs in a more stable and efficient manner. You can configure the parameters in the PooledDB function that is called by the __init__ method.

Important

To minimize potential risks and uncertainties and ensure system stability and reliability, we recommend that you perform a complete feature and performance testing before you apply the new parameter values in your production environment.

Recommended configurations

When you use the DBUtils connection pool, we recommend that you configure parameters to reduce database runtime risks. The following table describes the parameters.

Parameter	Description	Default value	Recommended value	Remarks
maxcached	The maximum number of idle connections.	0	20	The value 0 or None specifies that no limits are imposed. Specify an appropriate value that allows you to reserve a specific number of connections to quickly respond to database request bursts.
maxusage	The maximum number of times that a connection can be reused.	None	10 to 20	The value 0 or None specifies that no limits are imposed. When the number of times that the connection is used reaches the value of this parameter, the connection is automatically closed and a new connection is established. Specify an appropriate value for this parameter to prevent long-lived idle connections from occupying resources. We recommend that you configure this parameter based on the actual requirements of the application and the processing capability of the database.
connect_timeout	The timeout period for connecting to the database. Unit: seconds.	10	3	The value range is from `1` to `31536000`. We recommend that you specify a timeout period in the range of 1s to 10s when you configure a connection pool. The timeout period varies based on the network quality and the distance between the application and server.
read_timeout	The timeout period for reading data from the database. Unit: seconds.	None	10 to 60	The default value `None` specifies that no timeout limits are imposed. We recommend that you do not set the timeout period to a small value. If an SQL statement that is used to read data is executed for a long period of time, you must check the SQL statement or the database before you modify this parameter.
write_timeout	The timeout period for writing data to the database. Unit: seconds.	None	10 to 60	The default value `None` specifies that no timeout limits are imposed. We recommend that you do not set the timeout period to a small value. If an SQL statement that is used to write data is executed for a long period of time, you must check the SQL statement or the database before you modify this parameter.

Optional configurations

When you use DBUtils to connect to an RDS instance, you can configure parameters to improve the database performance. The following table describes the parameters.

Parameter	Description	Default value	Recommended value	Remarks
maxconnections	The maximum number of allowed connections in the connection pool.	0	100	The value 0 or None specifies that no limits are imposed. Specify an appropriate value for this parameter based on the concurrent processing capability and workloads of the database. In most cases, we recommend that you set this parameter to the maximum number of concurrent connections to the database.

Default configurations

You can use the default configurations of the parameters or modify the parameters based on your business requirements. The following table describes the parameters.

Parameter	Description	Default value	Recommended value	Remarks
mincached	The initial number of idle connections in the connection pool.	0	/	The value 0 specifies that no connections are established upon startup. We recommend that you use the default value. This way, connections are not established when the connection pool starts. Connections are established when they are required.
maxshared	The maximum number of shared connections.	0	/	The value 0 or None specifies that all connections are dedicated. If a connection is a shared connection, the connection can still be shared when the maximum number of connections is reached.
blocking	The handling method that is used when the maximum number of connections is exceeded.	False	/	If you set this parameter to True, the request to obtain a connection is blocked until the number of connections decreases. If you set this parameter to False, tan error is reported.
setsession	The SQL statement that is executed when a connection is established.	None	/	You can use this parameter to configure session-level parameters or perform other initialization operations.
reset	Specifies whether the status of a connection must be reset when the connection is returned to the connection pool.	True	/	If you set this parameter to True, connection reset is required. If you set this parameter to False, connection reset is not required.
failures	The number of reconnections when a database connection fails.	None	/	/
ping	The heartbeat detection operation that is performed for idle connections.	1	/	You can use this parameter to check connection availability.

Subsequent database operations

If you want to perform operations on databases, you can add custom methods to the DBUtilsDemo class.

Read requests

1. Add custom methods.

You must add custom methods to the DBUtilsDemo class to process read requests on a database. The following sample code provides an example on how to query a single row and multiple rows of data:

Query a single row of data.

def select_row(self, sql):
    connection = self._connect()
    statement = None
    try:
        statement = connection.cursor()
        statement.execute(sql)
        row = statement.fetchone()
        return row
    except Exception as e:
        print(e)
    finally:
        self._close(connection, statement)

Query multiple rows of data.

def select_rows(self, sql):
    connection = self._connect()
    statement = None
    try:
        statement = connection.cursor()
        statement.execute(sql)
        rows = statement.fetchall()
        return rows
    except Exception as e:
        print(e)
    finally:
        self._close(connection, statement)

2. Call custom methods.

You must call the following method in the __main__ function to query data:

if __name__ == '__main__':
    # Configure the required connection parameters and connect to the database.
    url = 'rm-bp**************.mysql.rds.aliyuncs.com'
    user = 'dbuser'
    password = '****'
    database = 'dbtest'
    poolUtils = DBUtilsDemo(url, user, password, database)

    # Query a single row of data.
    row = poolUtils.select_row("select * from tb where id = 'i001' limit 1")
    print(row)

    # Query multiple rows of data.
    rows = poolUtils.select_rows("select * from tb")
    print(rows)

Write requests

1. Add custom methods.

You must add custom methods to the DBUtilsDemo class to process write requests on a database, such as the INSERT, UPDATE, DELETE, and CREATE TABLE requests. The following sample code provides examples of a write request with bound parameters and a write request without bound parameters:

A write request with bound parameters

def upsert_data_prams(self, sql_upsert, params):
    connection = self._connect()
    statement = None
    try:
        statement = connection.cursor()
        statement.execute(sql_upsert, params)
        connection.commit()
    except Exception as e:
        print(e)
    finally:
        self._close(connection, statement)

A write request without bound parameters

def upsert_data(self, sql_upsert):
    connection = self._connect()
    statement = None
    try:
        statement = connection.cursor()
        statement.execute(sql_upsert)
        connection.commit()
    except Exception as e:
        print(e)
    finally:
        self._close(connection, statement)

2. Call custom methods.

You must call the following method in the __main__ function to execute the SQL statements.

if __name__ == '__main__':
    # Configure the required parameters and connect to the database.
    url = 'rm-bp**************.mysql.rds.aliyuncs.com'
    user = 'dbuser'
    password = '****'
    database = 'dbtest'
    poolUtils = DBUtilsDemo(url, user, password, database)

    # Process the write request without bound parameters.
    poolUtils.upsert_data("insert into tb(id,name,address) values ('i001','n001','a001')")

    # Process the write request with bound parameters.
    params = ['i002', 'n002', 'a002']
    poolUtils.upsert_data_prams("insert into tb(id,name,address) values (%s,%s,%s)", params)

References

For more information about the Jave connection pool, see Use Druid to connect to an ApsaraDB RDS for MySQL instance.
For more information about the Go driver package, see Use Go-MySQL-Driver to connect to an ApsaraDB RDS for MySQL instance.
For more information about the connection pooling feature of a database proxy in ApsaraDB RDS for MySQL, see Configure the connection pooling feature.