Function Compute:Best practices for accessing ApsaraDB RDS for MySQL

Step 2: Access RDS in the function

1. Log on to the Function Compute console, find the target function in the function list, click the Code tab on the function details page, and write the following sample code in the code editor.

   from flask import Flask, jsonify
   import pymysql
   import os
   from datetime import datetime
   import logging
   
   app = Flask(__name__)
   
   # Global variable for storing MySQL singleton connection
   _mysql_connection = None
   
   # Create database connection (singleton pattern)
   def getConnection():
       global _mysql_connection
       try:
           # If connection already exists and is not disconnected, return it directly
           if _mysql_connection is not None:
               try:
                   # Test if the connection is valid (simple command test)
                   with _mysql_connection.cursor() as cursor:
                       cursor.execute("SELECT 1")  # Simple query to test connection status
                       result = cursor.fetchone()
                       if result and result[0] == 1:
                           return _mysql_connection
               except pymysql.OperationalError:
                   # If connection is disconnected, reset it
                   _mysql_connection = None
   
           # If connection does not exist or is disconnected, recreate it
           _mysql_connection = pymysql.connect(
               host=os.environ['MYSQL_HOST'],
               port=int(os.environ['MYSQL_PORT']),
               user=os.environ['MYSQL_USER'],
               password=os.environ['MYSQL_PASSWORD'],
               db=os.environ['MYSQL_DBNAME']
           )
           return _mysql_connection
       except Exception as e:
           logging.error(f"Error occurred during database connection: {e}")
           raise
   
   
   @app.route('/', defaults={'path': ''})
   @app.route('/<path:path>', methods=['GET', 'POST', 'PUT', 'DELETE'])
   def hello_world(path):
       conn = getConnection()
       try:
           with conn.cursor() as cursor:
               # Query all records in the users table, modify users table according to actual table name
               sql = "SELECT * FROM users"
               cursor.execute(sql)
               result = cursor.fetchall()
               
               columns = [desc[0] for desc in cursor.description]  # Get list of field names
   
               # Convert query results to a list of dictionaries
               users = []
               for row in result:
                   user = {}
                   for idx, column_name in enumerate(columns):
                       value = row[idx]
                       if isinstance(value, datetime):  # Handle date type fields
                           user[column_name] = value.strftime('%Y-%m-%d %H:%M:%S')
                       else:
                           user[column_name] = value
                   users.append(user)
                   
               if users:
                   # Return JSON response for all users
                   return jsonify(users), 200
               else:
                   # If no users are found, return 404 error
                   return jsonify({'error': 'No users found'}), 404
   
       except Exception as e:
           logging.error(f"Error occurred during database operation: {e}")
           return jsonify({'error': 'Database error'}), 500
   
   
   if __name__ == '__main__':
       app.run(host='0.0.0.0', port=9000)
   
   

2. On the Function Details page, choose Configuration > Environment Variables, click Edit, and configure the following environment variables in the Environment Variables panel.

   Environment variable name	Environment variable value	Description
   MYSQL_HOST	rm-bp19u8e76ae****.mysql.rds.aliyuncs.com	The endpoint of the database instance. If you choose the RDS database in the same VPC scenario, set this environment variable to the internal endpoint of the database. If you choose the Access RDS database across VPCs or regions scenario, set this environment variable to the public endpoint of the database. In the RDS instance list, click the target RDS instance ID. In the left navigation pane, click Database Connection. On the Database Connection page, obtain the internal endpoint or public endpoint of the database.
   MYSQL_DBNAME	db_test	The name of the database created in the RDS instance.
   MYSQL_PASSWORD	*****	The password of the database account.
   MYSQL_PORT	3306	The private port of the database instance.
   MYSQL_USER	dms_user_****	The username of the account that is used to connect to the RDS instance.

3. On the function details page, click the Code tab, click Test Function, and view the returned result after successful execution. The table query operation has been successfully completed.